Reading CSV file

[Guest_imported]

I am trying to update a database with a CSV file. I am using the VBScript Split function to put the values into an array. At first everything seemed to work, but then I noticed one of the fields (an address field) contains commas on a few of the records.

When I open up the CSV file in Excel (i.e. by clicking on it in Windows Explorer), then all the columns come out correctly. In other words it is not confused by the additional commas. However when I process the file into a database (using VBScript Split function to put each line into an array defining a comma as the delimiter), the result is wrong because of the commas in the field values. Has anyone got any ideas as to why Excel can differentiate between the commas, but my code can't? I fear I'm doing something stupid.

TIA
Mel

 

[culshaja]

It might be that Excel is using text delimeters such as " or '. If a comma appears between these delimiters then it knows that it is part of the text and not a delimiter.
I have seen this problem many times.
Also the CSV might have some hidden characters that it uses to delimit the columns - I'm just using supposition on that one and would like it confirmed or denied.

James

James Culshaw
jamesculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

 

[Guest_imported]

It turns out that whenever a strong data type (non-numeric) contains a comma it has double quotes around it. Has anyone got code to work around this (looks like the VBScript Split function won't be enough).
TIA
Mel

 

[culshaja]

You could try and use a different delimter for the columns if you have a character that you can guarantee will not appear in the data.

James
James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk