Extra commas in CSV file

[E11esar]

Hi there. This could be a curious one.

Has anybody come across a solution to remove stray commas that appear within strings in a CSV file please?

In effect I have many address fields that are punctuated with commas and I am looking for a way to remove these while parsing the csv file.

Any ideas will be most appreciated please.

Thank you.

Mark :)

[nukefusion]

I had a very similar issue myself with an application I created. It needed to produce a CSV file that could be imported into another vendors piece of software, but some of the fields contained address data, which could potentially contain commas.

I simply used the String.Replace( ) method to remove the commas and replace them with spaces, or another neutral character. You may well be able to apply a similar approach to your problem.

[E11esar]

Csv

No that won't work for me as is as I am splitting the line as a string, using the delimited fields to populate a string array, hence in some cases the individual parts of the address string are being split into array cells, which is not serving its puprose well for me.

Ultimately the individual fields need to populate a database table so I need to keep the address lines in one field and so on.

Thank you.

M :)

[nukefusion]

Really, if it's within your control, the best thing to do would be to stop the stray commas getting into the CSV file in the first place, or if the address field must have commas then put the field data between quotation marks. Then you could process it in your application as a literal string.

If you haven't got control over creation of the CSV file and the address fields aren't encapsulated within quotation marks, then you're dealing with a poorly formed CSV file and the only option would be to fudge it using some sort of heuristic analysis.

[Ramk]

Really, if it's within your control, the best thing to do would be to stop the stray commas getting into the CSV file in the first place, or if the address field must have commas then put the field data between quotation marks. Then you could process it in your application as a literal string.

If you haven't got control over creation of the CSV file and the address fields aren't encapsulated within quotation marks, then you're dealing with a poorly formed CSV file and the only option would be to fudge it using some sort of heuristic analysis.

Why don't you try to use JET OLEDB. In which, you can parse the csv file(or any text file etc) by using OLEDB. You can define a custom schema in a file called schema.ini file.
Plz go through the following link for more info on schema.ini file.
Schema.ini File (Text File Driver)