Import large Excel data to MS Access

Question

I am using MS Access 2010 to import Excel data.

The spreadsheet has > 250,000 records, and 130 columns. I tried both linking to the data source, or importing the data to database.  For both there is an error indicating something like "Unexpected database driver error".

I believe it is due to too many records.  I tried splitting the file, and the import can be done when it has only ~50,000 records.  I can end up loading all records into MS access, by importing the splitted smaller files one after another.  But this will require quite some manual work and is not desirable.  

Would there be any way to get around?  MS Access database can support the data size.  The question is more about the internal mechanism to import massive records.  I think if MS access can handle the massive records by smaller trunks, it should be able to handle the import.

Answer 1

You may have some bad data in the middle of the Excel file.

Add a number column using 'autofill' and try using a query with 'From' & 'To' criteria to pull the chunks.

When you hit a snag then narrow the chunk to find the problem.

Answer 2

Please don't piggyback your question on another thread. Please use the Participate link above and start your own thread, fully explaining the problem.

Answer 3

I am having same problem with importing large Excel 2013 file (27 columns but a lot of rows) into Access 2013. Get same unexpected error from external database driver(1). I've put both Excel and Access files on laptop instead of network but no success. Tried saving as .csv file, but got issues due to some of text in cells. Tried saving as .xls but many rows didn't save. Tried .xlsb binary format but it wouldn't import at all and crashed Access. Tried removing borders and shading and bolding but no success. Tried linking instead of importing but still no success. 

Appears there is is some problem with the .xsls format, given .xls and .csv do import, but rows get truncated. Any additional ideas welcome. Running on Windows 7.

Answer 4

The excel data is on a local drive.

Yes it is more like a flat file format.  This is an extraction from data warehouse, for further analysis (like building pivot table).  We are exploring feasibility to load into MS access, and see if possible to trim down the size by some query.

Answer 5

Is the Excel data on a network share or local. If its not local I would try importing from a local file. 

Also, Excel is a flat file. You might want to consider normalizing the data as you import.