200 COL Excel file with 700 rows -how to switch COLS & ROWS in ACC

[JacquelineAB]

I have an EXCEL spreadsheet with 700 lines of data and 200 columns. It was
set up with COLUMN A as the data header with the individual columns being the
data fields. ACCESS will not import this correctly since it requires the
first ROW to the the header as opposed to the first COLUMN. Is there a way
to switch this in ACCESS? The number of rows will NOT transpose in EXCEL as
they exceed the column limits. Can this be done in ACCESS or I am in deep
trouble getting this info into a new database format? I am guessing I need to
use the Pivot Table/Chart function but i am not finding failproof
instructions on how to do this. CAN ANYONE PLEASE HELP??

 

[Pete D.]

You can do it with the link below but you will have to break the file down
to less than 255 rows of data which means convert three times and then
append data together. Access can handle 255 fields so you have to get it
down to this many rows and I recommend since you have to do it with three
files you will probally need to add a unique key to the records to assemble
it with some queries afterwards. When you run query in access it sometimes
needs more field room to complete the query so 700/3 bring it down to 234
records with your remainder in the last file. Also if you search google
with, transpose text file, you'll find other dos, excel, access and VB/VBA
solutions.

http://support.microsoft.com/kb/202176

 

[Peter Hibbs]

Jacqueline,

Maybe the Excel to Access Converter Utility program at :-
http://www.rogersaccesslibrary.com/...?TID=183&SID=e81a164e2314187z3e8587231e28cb7d
can do what you want.

Peter Hibbs.