Convert Excel Table to Access flat file

[paras.shah]

Need some help.

Currently need to convert table from excel to a flat file in Access.

Current data looks like this (example) ->

Fruit Jan Feb Mar Apr

Apple 10 12 15 20
Pear 0 20 3 10
Banana 1 10 17 2

and I need to get it like this ->

Fruit Month Qty
Apple Jan 10
Apple Feb 12
Apple Mar 15
Apple Apr 20
Pear Jan 0

and so on.

I have tried to do this in excel by pivot table but I have a table
that once converted exceeds 65K records.

The reason is that I have multiple feeds of different formats, and
once they are in this flat file format, it is easier to work when
dealing with writing queries.

Does anyone know of a way to do this in Access 2003? I am pulling my
hair out trying to figure it out.

Thanks

 

[Joan Wild]

Import into Access as is and then run a union query to normalize it. You'll
need to write this in SQL View of a query. Start a new query, dismiss the
Add table dialog and go to View, SQL View...

SELECT [Fruit], "Jan" AS Month, [Jan] AS Qty
FROM YourTable
UNION ALL
SELECT [Fruit], "Feb", [Feb]
FROM YourTable
etc. for each month.

You can then turn this into a MakeTable query and run it to get your
normalized table.

 

[John Nurick]

One way is:

1) import or link the table as is (i.e. you'll have 13 fields of which
12 have the names of months)

2) create your normalised table with the three fields Fruit, MonthName,
Qty (it's usually best to avoid giving fields names that are also the
names of common functions or VBA objects).

3) use a series of append queries that take one month at a time, e.g.
the first might be

INSERT INTO NewTable (Fruit, MonthName, Qty)
SELECT Fruit, "Jan" AS MonthName, Jan AS QTY
FROM ExcelTable WHERE Jan IS NOT NULL;

and the second will use "Feb" and Feb instead of "Jan" and Jan, and so
on.

4) Delete the old table.

Another - if you have Perl on your computer - is to export the data from
Excel to a CSV file and then process this using the txtnrnm.pl utility
at http://www.j.nurick.dial.pipex.com/Code/Perl/index.htm . This will
produce a normalised text file that you can import straight into Access.

 

[VBmatta via AccessMonster.com]

Hi paras

I have the same kind of problem exactly the same.Did you get a way to do this
one.If so can we discuss or communicate about it.I have more than 65,000
cells if i were to convert to excel , so i am going for ACCESS...


However mine is more like this :
---------------------------------------Time (hrs)---------------------
----------------------------------
day 1:00 2:00 3:00 4:00 5:00 6:00 .........24:00

2/4/1985 2 4 3 5 ........................
...
3/4/1985 2 5 6 8
Thank you
Arenden

 

[John W. Vinson]

However mine is more like this :
---------------------------------------Time (hrs)---------------------
----------------------------------
day 1:00 2:00 3:00 4:00 5:00 6:00 .........24:00

2/4/1985 2 4 3 5 ........................
..
3/4/1985 2 5 6 8

That is - again - a good spreadsheet but a BAD table. "Fields are expensive,
records are cheap". A tall-thin table with fields for the date, the time, and
the value will be much better.

John W. Vinson [MVP]

 

[Aaron Kempf]

not sure that I always agree with this

leave it in spreadsheet form for now-- and get practice writing queries

the only problem with wide tables in Access is that there is a limit to the
number of columns.. this is the single reason I moved to Access Data
Projects

FILE, NEW, PROJECT (existing data)