Commas are messing up my CSV

posted Feb 14, 2011, 6:04 PM by Ezra Kenigsberg   [ updated Jan 27, 2012, 12:19 PM ]

#DataTipOfTheDay 2011-02-14

Q: "I exported a CSV from System X and commas in text fields are messing everything up! What do I do?"

A: Here are the alternatives I’d try, in order. (Btw, remember that Salesforce’s CSV format is the same format Microsoft uses for its CSVs—so you can try opening potential CSVs in Excel to see if they’re handled correctly.)
  1. Specify a text qualifier (ideally a quotationmark) when exporting. Each field’s value is then wrapped with quotationmarks, formatted in a way that Salesforce can understand.
  2. Absent the ability to specify a text qualifier, I’d specify a different delimiter, like the pipe character or something exotic from Windows’ Character Map application, if System X displays it properly. Once the file has been exported using that delimiter, I would transform the file into a proper CSV in four steps:
    • perform a find-and-replace, replacing every instance of a quotationmark with two consecutive quotationmarks.
    • perform a find-and-replace, replacing every instance of the delimiter with quotationmark-comma-quotationmark.
    • add a quotationmark to the beginning of every exported record
    • add a quotationmark to the end of every exported record
  3. Absent the ability to specify a different delimiter, I’d try breaking up the export files. For example, if only Fields X, Y, and Z are causing problems, I would break the export up into four files:
    • ID and all fields except Fields X, Y, and Z
    • ID and Field X
    • ID and Field Y
    • ID and Field Z
      Once those four files are exported, I would either (i) import the files into Salesforce as an insert followed by three updates (the updates are best done using the upsert command with external IDs) or (ii) stitch the files into a single file and import that. In either case, the files will require some reformatting, but it should be a lot easier than trying to handle all the fields in one file.
Not the most romantic help topic. . . but Happy Valentine's Day anyway!