Using Excel to import .csv file into Access table

[Spence]

Using Excel 2003, I need to get a csv file into an Access table.

The problems:

csv file is over 200k lines, so I can't open it in Excel
There are known to be some bad records in the file, so they need to be read in and checked
one-by-one (a simple field count is all that's needed).
Each line has to be parsed to check for commas inside quoted strings.

So I'm using a FIleSystemObject TextStream to read the data. Trouble is it's taking about
4 hours to import all records.

Basic process is:

Open table as ADO recordset (table is empty at this point)
Open csv file as TextStream
Read line
parse line copying each field into a text array
Check size of array to confirm field count
create new record in recordset
copy text array to new record
update recordset
Repeat from Read Line until end of TextStream.

Any suggestions for an alternative (quicker) method? And using Access isn't an option.

Thanks

 

[Nigel]

Sounds to me using Excel is not an option either!

 

[Spence]

Sounds to me using Excel is not an option either!

Unfortunately, apart from writing a Windows app to do it, it's the only
option. Thankfully, it's only once a month it has to be done.