I got an unusual request today. Someone sent me an Access file, asking to convert it to Excel. Why? I don’t know, I guess people are confused by the database stuff or something. I looked at the file, and figured this would be simple. There was only a single table in the DB.
I had one problem though – the table had over 400k records. An Excel worksheet can take only around 65,536 rows. It’s a design choice they made – and you have to deal with it. The export feature in Access however does not take this into account. If the table has more rows than the worksheet can accept it simply discards all the remaining entries and gives you an error message.
I really don’t get this. Excel and Access are both part of the same Office Suite. They were likely developed by the same team/department/group. How come no one ever caught this? What is the point of having the export feature if it doesn’t work half the time?
Fortunately you can export it to other formats, for example tab delimited text file. I like text files. Text files are easy to process. Hell, text files are absolutely trivial to process – unless of course you are a windows user.
So I exported the DB into a text file, copied it over to my Kubuntu laptop and did this:
$split -l 65536 my_file.txt
Now I had 7 tab delimited text files that could be easily imported into Excel.
That was easy! Easy as π!
wow, i got exactly the same request at work and im having exactly the same problems. i was trying to find a program online that might convert it but i havent had any luck. so what exactly do you do if you dont mind me asking? the other thing about the built in export on access is that it took like an hour for it to do it and i couldnt touch my computer during that time or it would freeze. this is the worst task ever, but unlike you, i have about 2000 tables that are just like the one that you had. please help! thanks!
Vicki, the only thing I could do was to export each Access table into a tab-separated text file (it’s one of the options in the export menu), then split that file into sizable chunks, and then import each chunk as an excel worksheet. There is just no other way to do this.
Also if the tables cross reference each-other you may need to do some joins and etc. Either way, it’s to much work.
You might want to explain to your supervisors that each Excel worksheet can only have 65,536 rows, and that each table in the database contains n rows so even in the best case scenario they would the data in a very fragmented form. They will be familiar with the excel limits – all they have to do is to open Excel and scroll down till the end.
Give them an estimate of how long would it take to do, and how many excel files it would take.
I would suggest figuring out what data they want out of that file, and design some queries that will generate appropriate reports for them – then export those into Excel. Chances are they actually need only a fraction of this data for whatever they are doing.
Good luck.
Access has some sort of SQL query feature. Why not use a perl script to export chunks of data from each enumerated table, obeying the $ffff limit?
You are right. You can set up your access file for querying with ODBC in windows. Then you can use DBD::ODBC driver for DBI to connect. :)
Hi,
I’m trying to export 22 multi linked (on to many realtionship) tables into excel. It keeps saying there are too many defined fields. Would you be able to help at all?
Thanks
Kasy
Well, it might mean that the number of colums in the tables combined is greater than the total number of columns you are allowed to have in Excel. I’m not sure how to get around that. :(
An Access table can be put into Excel, there is a decent thread on it here http://www.nettechguide.com/forums/showthread.php?t=2237723
Hi can you please guide me with your step by step how you slip the text file into seven different tabs. Once I am able to export of the data onto the text file, I tried opening the file using excel and ofcourse data beyond the max limit 65,000+ did not cross over. How do I get to the stage of writing the split formula & delimiting the tabs? I am using access & excel 2003.
Thanks,
Tia
@ tia:
Tia, I thought the steps were fairly clear. Here it goes again:
1. Open your shell terminal
2. Type in “split -l 65536 my_file.txt”
3. ???
4. Profit
Split is a native unix command that should be installed by default on most POSIX compatible systems (ie. anything that was not made by Microsoft – Linux, Unix, Apple, etc…)
If you are on windows… You can use Cygwin which is a POSIX compliant shell for Windows. But it is probably not recommended if you have never used a shell.
Hmmm… Don’t know. There are probably some file splitters that can do it, but I never looked for one.
@ Luke Maciak:
Thanks Luke, I will try that.