Best format for import into Access

[Justinidea]

I have multiple spreadsheets with massive amounts of data in them - approximately 20 million rows in total. I will be building several Access databases from that data.

What is the most efficient format for transferring data from Excel to Access?

In other words, would it be more efficient to convert the .XLSX to something else (i.e. XML, DIF, SLK etc) or is xlsx already the best way.

Thanks!

 

[Joe4]

First off, if you have 20 million rows of data, you may have issues importing it into Access. Access database have a 2 GB size limit. If the amount of data you have pushes Access over that limit, you won't be able to use Access, at least not to house the data (unless you split that up among multiple Access databases). You may want to look at importing the database into a more robust database program like MySQL, SQL, Oracle, etc. You can still use Access as the front-end database.

Even if you are able to import all that data into Access and not exceed the 2 GB size limit, I suspect your database performance will most likely be very slow, as you will be taxing Access' limits.

To answer your question, all else equal, I would probably convert my Excel file to tab-delimited text files if I were to import them to Access. Importing Excel files into Access can be flaky, as instead of invoking the Import Wizard and letting you set the format of each file, Access & Excel will try to communicate and "figure out" the field formats on their own, based on the first 10 records or so of each file. If it guesses wrong, you will get many import errors which can drive you crazy.

 

[Justinidea]

Thanks Joe.

I'm starting with raw .txt files, but when I tried to import those directly in to Access, there were multiple errors because of mixed formats in certain columns - some date fields had null values and a couple of other issues that made it seem easier to convert to Excel first. I probably did it backwards but I have the spreadsheets and filled in missing dates with a dummy date and added some other missing data.

Bottom line, I have it working in my own wierd way, but I was just wondering if .xlsx was better (except for .txt of course).

 

[alansidman]

I personally prefer to get my data normalized in Excel and then convert the files to .csv files and then import them into Access. I can then using the Advanced button in the import wizard designate which fields I want to import and which format it should be imported as.

My 2 cents.

 

[Justinidea]

I too have found that the best way. But since I've been told I don't know everything, I thought I would ask if there is a better way. Thanks for your input!