3

I have been stuck with the following frustrating issue for some days, I hope you can help.

Using Salesforce Data loader I wish to export some objects (Accounts, Contacts, Opportunities) and import them into a sandbox. When I export the Accounts, my csv file gets a strange format for fields that have line breaks. For example querying ID, BillingStreet, BillingCity gives the following csv for one record:

"ID","BILLINGSTREET","BILLINGCITY"

"0012213140hL7LVAA0", "21 Whitetown Road

Haudack","New York"

The same happens for any field I export that have line breaks, Shipping Street, text areas etc. Have anyone encountered this issue and found a solution? It has been a while since I used the data loader, but don't remember having this issue ever.

Any help is greatly appreciated.

Improve this question

2 Answers 2

Reset to default
2

That's standard CSV format. Any program that understands CSV will correctly parse the file. When a cell starts with a quotation mark, it must end with a quotation mark. Everything in between, including commas and new line characters, are part of the cell's data. There's nothing strange about the output. Try loading the file in Excel, or reading it into a database, and you'll see it works as expected.

Also, see RFC 4180, which is considered the standard for CSV. Note that a RFC is usually modeled on formats already found in the wild at the time of writing, as many formats often converge to a de facto standard by consensus far before a formal standard is written.

Improve this answer
5
  • This actually happens when opening the exported file with excel or notepad++ - as you can see in the above example the line break happens in between 2 quotation marks.
    – MR.M.
    Commented Jul 6, 2016 at 5:56
  • @user2936057 I still see nothing wrong with what the data loader is doing... What are you expecting it to do? If the street field is on two lines, that's how CSV is supposed to behave.
    – sfdcfox ♦
    Commented Jul 6, 2016 at 6:03
  • I see what you mean, so the export is actually formatted correctly. My problem with those line breaks is that when I want to manipulate the data in excel - like add a new column for a new field - and then upload to a sandbox, my data will be formatted wrong. I.e. each row should start with the ID, but as soon as I have a line break, the document gets messed up. Any suggestion?
    – MR.M.
    Commented Jul 6, 2016 at 6:19
  • @user2936057 Sometimes Excel gets the file format wrong when you open it. Try using File / Open manually instead of opening the file via the file manager or Data Loader's Open In External Program option. Make sure that "CSV (Windows)" is the file type selected. You might even need to use the import text feature if that fails.
    – sfdcfox ♦
    Commented Jul 6, 2016 at 6:25
  • I am actually using the import text feature (Data -> From Text) and following the steps, but with the same result - whenever there is a line break it makes a new line in excel. Very frustrating issue - as a workaround I am not exporting any fields that have line breaks, but I am not happy about it. Any other suggestions? Thx!
    – MR.M.
    Commented Jul 6, 2016 at 8:18
0

Current answer is correct: this is standard CSV format. Unfortunately, not every application cares about that. In my case, a 3rd party application receiving the file would disregard quotation marks and read each line as a new entry so I needed to remove the line breaks.

A quick method is to export the report as a details only excel spreadsheet (xls) then convert that to csv. In your example, this would remove the line break in the BillingCity field

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .