Excess commas in CSV

[BenH]

Have a script which imports a CSV .txt file into a table via a TransferText method and an import spec. The file it always in the same format, same number of fields.

However, sometimes a row will contain an excess comma in the name field. This causes the rows to go out of synch when the table is created. I don't want this.

Is there a way to parse the txt file before import, count the number of commas on each row, and if it's not the expected results, to stop and warn the user?

Cheers.

 

[gemma-the-husky]

if commas are embedded in text, you need to get the csv formatted with quote marks around the offending text. - there are generally options to do this in whatever is generating the csv. alternatively generate the csv with a different separator instead of comma - otherwise, can you goto fixed width instead of csv - then you will not have any commas/sepa

so that

1,2,3,12 A Street, Townsville, UK, Smith, John

becomes
1,2,3,"12 A Street, Townsville, UK", Smith, John

 

[BenH]

Unfortunately this isn't an option becuase the CSV is also imported into an Ingres database via a vendor supplied shell script. If we asked the people who provide the data to give it in the format you've asked (with "" around the fields), then it wouldn't import into the Ingres database without amendment of that interface. If we amended that shell script, it wouldn't be supported, etc. etc...

What I really need is some kind of tool within VBA to analyse the text file before import within some kind of data structure or otherwise.

Any ideas much appreciated.