[FIXED] How to deal with a comma in description in a CSV file

Issue

Here's an interesting problem: I have a generic price file with ID#, Description and Price to import that comes in as a comma delimited file (CSV or TSV) from a variety of vendors. One of the vendors uses a comma in their Description field. The problem is in the import thinks that every comma defines a new column and throws off the record. (It would be easy to deal with if the import file were fixed length, but alas it is not.)

Question: Can anyone think of how to deal with a comma in Description? I'd like to replace the comma with a period or hyphen, which would be acceptable.

Here's what the file looks like.

ID,Description,Price
1234,Good Part,1.23
2345,This is.ok,2.34
3456,Bad Part,with a comma,4.56

In the first and second record, there are 3 columns as it should be. In the third example, this results in 4 columns and throws off the import since it's looking for a currency in the 3rd column, but finds a string instead. I'm using Perl and Java script for the most part.

Solution

The most common solution is quoting fields that can contain "bad characters".

In this case:

3456,"Bad Part,with a comma",4.56

And in turn, if you happen to have " character inside you escape it with \ (and so you do with plain ).

Answered By - user80168

Answer Checked By - Mary Flores (PHPFixing Volunteer)