@ Luke Maciak:

Thanks Luke, I will try that.

@ 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.

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

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

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. :(

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

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. :)

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?

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.

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!