#DataTipOfTheDay 2011-02-14

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.