trying to unravel a spreadsheet

[KWhamill]

I have a spreadsheet that was once converted to Pivot Table, copied and
pasted and then never changed back. The original source of this data is gone.
and i need to convert this into an access table. Thus I have something that
looks like this
Date1 date2 date3 date4
date5 date6
1 amt amt amt amt
amt amt
2 amt amt amt amt
amt amt
3 amt amt amt amt
amt amt

So I want to make each of those into an individual entry on a table
primary key 1 date1 amt
primary key 1 date2 amt
and so on
the amount of data is enormous I can't see just entering these in, but i
can't think of a way to unravel the spreadsheet.

 

[Jerry Whittle]

First you'll need to either import or link the spreadsheet into Access.

Create a bunch of queries joined together as a UNION query. In each of the
individual queries you'll want the PK field, the Date, and the Amt under that
date. For the date you probably have to manually insert it with something
like:

TheDate: #6/26/2008# if that would be the date for the Date1 field.

 

[pietlinden]

This is how I did it...



Public Sub TwistCrosstab()
Dim strSQL As String
Dim intCounter As Integer

'note to self: the date is the value of the column name besides
column(0).

'reference to non-normalized table (crosstab table)
Dim tdf As DAO.TableDef

Set tdf = DBEngine(0)(0).TableDefs("xlsCrosstab")

'old table structure is:
'IDNo (account), Date(n) where the value is the value to be
transposed.
'new table structure is:
'IDNo, TransactionDate, Amount

For intCounter = 1 To tdf.Fields.Count - 1
CurrentDb.Execute "INSERT INTO FixedXTB ( Account,
TransactionDate, Amount ) SELECT [" & tdf.Fields(0).Name & "], #" &
tdf.Fields(intCounter).Name & "# As Transaction_Date, [" &
tdf.Fields(intCounter).Name & "] As Amount FROM [" & tdf.Name & "];"
Next intCounter

Set tdf = Nothing
End Sub

"FixedXTB" is the destination table... it has an autonumber field as
primary key just for the heck of it...

 

[a a r o n . k e m p f]

SQL Server has the UNPIVOT command

Access is for babies.
Don't be a loser.

UPSIZE, UPSIZE, UPSIZE

-Aaron

 

[Tom Wickerath]

Check out my Reverse Crosstab examples, available as a download in a .zip file:

http://www.accessmvp.com/TWickerath/downloads/revXtab.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Peter Hibbs]

Try this utility -
http://www.rogersaccesslibrary.com/...me='Excel to Access Converter Utility program'

HTH

Peter Hibbs.

 

[So Sorry For Poor Aaron]

Hark, lurkers in the newsgroup: aaron the unhelpful returneth. Beware him,
he beith no more than a troll. He leadeth the unwary into a costly morass of
unneeded upgrades. Beware, avoid, and evade aaron the unhelpful.


:

 

[KWhamill]

First I turned it into a text file, when you have a date for a column header
and currency for a field this tends to cofuse things and i need acces to see
things for what they are. Then the rest you suggested, of course works fine,
I had already thought of this. I don't think that i conveyed a proper sence
of the size of the problem. When I say enormous i mean more than 100 columns
and 500 rows multipied across 5 spreadsheets. I think what i'm looking for is
a solution that will be able to read what it needs off of the data file and
know when nit has reached the last bit of data. I don't care if i have to set
it up for each spreadsheet.

 

[a a r o n _ k e m p f]

you need an ETL tool.

Access doesn't support ETL.
Go and take a class on SSIS or something similiar

-Aaron