Large csv files in XL

  • Thread starter Thread starter John E.
  • Start date Start date
J

John E.

I have a csv file which is about 50M in size. It has to filter 2 supporting
csv files of about 200M each, and create a 4th csv of matching output
depending on the criteria. I now know I can't do much with these files in XL
because of the 65000 row limit.

I was then thinking about doing this as streaming text, but that was too
slow and complex because the indexes in each row are a long way from the
start.

Would it be easier to use ADO? or some other technology within Office 2000?
(I've also got mySQL but don't know much about using it.)

Any suggestions? TIA.
 
Hi John,

Basically your choices are

1) Use textfile tools (such as Perl or the Gnu textutils). If the CSV
files are already sorted on the relevant keys this is pretty quick even
on files this size.

2) Use Access (or mySQL): import the files to tables, create indexes on
the keys you need, and use a query to produce the result. Given the 2GB
limit on Access 2000 mdb files you will need to be a little careful when
importing or you'll have trouble. In my experience it's best when
importing big textfiles to create the table manually in Access first,
removing all the indexes Access automatically creates. Then import the
data, and finally create the indexes you need. Then compact the database
and do the next table. Also, make sure the mdb file is on a local drive,
not on a network share.
 
Back
Top