Import over 255 to Access 2010

[TJVFree]

I’ve been on the internet now for a couple hours with no success.

I’m using MS Office 2010

I have a excel spread sheet with around 18,000 rows, and 16,475 columns
The 16,475 columns always are the same. So what I’m trying to find is if there is a way I can import this spread sheet into access. Understanding that access has a 255 limitation.

I’m wondering if there is a way I can have access go out to the spread sheet and grab column” A, B , C, D, Z, AE, AZ, etc.,”<-as a example

Then go back and grab more columns until I have all the columns in different access table from columns that were in excel

I appreciate any help

 

[Andrzejek]

So in (your) perfect world you would like to have a table in Access with 16,475 fields (columns)? WOW! The question is: Why? Table like that would be totally unmanageable. Reasonably any table should have up to about 20 fields (IMHO), so you are going over 16, 450 more.

Have fun.

---- Andy

 

[TJVFree]

I apologize if I didn’t explain it better.

I would like to import the 16,450 columns into a number of different tables. "Similar to how we do pass-Through queries".

If I can make my excel spread sheet into a ODBC and then do a pass-Through query and grab the columns I need and put into different tables

 

[randy700]

16,450 columns? Really?

My version of Excel (2007) only goes to column XFD, which is fewer than 16,450 columns.


Randy

 

[TJVFree]

Correction 340

 

[StarPassing]

Excel 2010 max row= 1,048,576 and max colums= 16,384

You should write code to open Excel and copy data you need to the different tables. That is what I do with large Excel files.

Tom

 

[MazeWorX]

You could link the sheet as a data source to access it and grab the data from in access assuming this is a one time import?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[MazeWorX]

btw 255 is the max size of a field in access nothing to do with the amount of records in a table or rows etc. Access has a 2gig limit before the db needs modification

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[SkipVought]

340 separate tables, each with ONE COLUMN of data?

What is the purpose for this data?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TJVFree]

Thanks everyone for responding.
I receive a “Data Dump” that comes to me in a csv file.

Once I open and save it in excel there are about 18,000 records.

The header row has 340 columns.

This will be a weekly process, I’d like to automate
Needing to import the excel table into access.

Now what I’m hoping we can do .

Thoughts:

Does anyone have code that will reach out from access to my excel file and import just certain columns maybe with a TransferSpreadsheet?

Is there a way I can save my csv or excel file as a ODBC, then do a pass-through query to retrieve the columns I need.

You’re thoughts please.

Thanks again everyone

 

[randy700]

Why bother with Excel if you want the file to ultimately reside in Access? I'd import the text file directly.
You could import the entire file into one table and create a query that pulls only the fields you're concerned with.

Randy

 

[TJVFree]

Thanks Randy, Is there a place I can find more information about how I would do this?

 

[randy700]

Use the TransferText method.
Pretty much the same as importing a spreadsheet.


Randy

 

[kjv1611]

16,475 vs 340 Columns

[ROFL]

I'm still trying to get over that one. That's quite a difference, I'd say.

Yep, as Randy says, just import the CSV directly using TransferText. Do it manually the first time (import the text file manually), and set up an Import Specification for it, make sure to save the specification with an easy to remember (or written down) specification name. Then use the specification with the transfertext method. There are examples readily available in the Access help files, as well as all over the web.

Once you get it into a table, are you wanting to consolidate the data differently, perhaps by combining some of the columns (if possible) or anything of that sort? Or do you have a pre-determined method for splitting the data to separate tables that are more manageable in size? If so, I'd suggest all of that would be worth discussing around here to make sure you wind up with the best solution... so for that/those steps, come up with how you think you'll do it, share, and ask for feedback.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MazeWorX]

After youve done it the first time manually; save the spec file and you can automate you future Imports like this

DoCmd.TransferText acImportDelim, strFileImportSpec, "TableNameHere", strFileLoc

In my case I use variables to hold the Spec file name(strFileImportSpec) and the location of the text file(strFileLoc)

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[TJVFree]

Thank you all for your help, but I’m still getting error messages. "See below"

I also this time attached a like to a blank database and the csv file I'm trying to load. Thanks again for your help

Error message
This operation will fail because the text file you are about to import contains more than 255 columns. We recommend that you first make a backup copy of your source find, reduce the number of columns to 255 or less, and try again

http://www.4shared.com/file/n7bNbQUr/TestDB.html

http://www.4shared.com/file/7FTW6aX_/Testfile_2.html

 

[SkipVought]

Short of writing a VBA read/write routine, you might consider this Excel technique for NORMALIZING your data...

faq68-5287

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TJVFree]

Thanks Skip, but it doesn’t give me a way in import 309 columns

http://www.4shared.com/file/7FTW6aX_/Testfile_2.html

 

[MajP]

Importing that spreadsheet into a access table would be silly and a waste of time. It is so completely non-normal, it would make no sense. Just write some VBA code in the spreadsheet, to normalize the data into a couple of tables. Then you could easily import the data. It would not be that hard in the spread sheet to write the code. The naming convention already facilitates this.

What would you do with this data in access? IMO it would be completely unusable in that form. Stop wasting your time figuring out how to import it as is. Fix it first.

 

[SkipVought]

it doesn't give me a way in import 309 columns

It? Excel?

Please explain exactly HOW you failed to import your .csv into Excel.

@MajP - My proposed process would reduce the number of columns to possibly THREE.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]