Convert a large Access table into Excel files

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 π!

Tags: access, excel, access export feature, 65536, split, linux is better than windows