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

.

Relevant Pages

  • RE: Access Query Recordet conversion to an Array
    ... > I have a problem with converting a recordset to an array. ... My seond problem is when you bring a recordset in that has various ... > missing the point with the migration from queries to excel. ... > Dim i As Long ...
    (microsoft.public.excel.programming)
  • Re: ADO Excel to Access - bulk transfer of array?
    ... this in Excel before I make the final decision. ... Access and instead of populating an array, use the table you want the info ... I would like to find a way to transfer the entire array or recordset over ... .CommandType = adCmdText ...
    (microsoft.public.excel.programming)
  • Re: REPLACE in SQL function against text files
    ... In that case you could replace in the recordset or do rs.GetRows and replace in the array and then dump that array in Excel. ... Possibly there is a way to do this in SQL and maybe somebody else will tell you. ...
    (microsoft.public.excel.programming)
  • Re: ADO Excel to Access - bulk transfer of array?
    ... it may be better to get your array into a recordset and append it to ... this in Excel before I make the final decision. ... .CommandType = adCmdText ...
    (microsoft.public.excel.programming)
  • Re: Import text in to access...
    ... You can connect to a csv file (which can be opened in Excel and which an ... The .csv file needs to be uploaded to the server. ... So I'd suggest going through this recordset one row at a time and within ... ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool... ...
    (microsoft.public.inetserver.asp.db)
Quantcast