Using Excel to import .csv file into Access table
 

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

--
Spence

Using Excel to import .csv file into Access table
 

Sounds to me using Excel is not an option either!

--

Regards,
Nigel




"Spence" <spencelayhathotmaildotcom wrote in message
...
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

--
Spence

Using Excel to import .csv file into Access table
 

In article , Nigel wrote:

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.

--
Spence
Computing Services - because IT needn't be difficult
http://www.compservonline.co.uk/