Print this page

Converting Your Spreadsheet Into Comma Delimited CSV

Knowledge Article Number 000221341
Description When you upload a CSV file to import the data into Salesforce, you can often see that all columns are mixed into a one single string, so there is no possibility to map them to respective Salesforce fields.

 
User-added imageHow it looks in Data Import Wizard

 
User-added image
How it looks in Apex Data Loader

The problem is caused by a delimiter used during conversion of your spreadsheet into CSV file. In many European countries due to regional settings a semicolon is used as a delimiter character, however Salesforce supports only comma delimited CSV files.
Below you can find step-by-step guides how to convert your spreadsheet into a comma delimited CSV file.
Resolution

If you are using Ms Excel 2007 / 2010 / 2013 for Windows.
  • Click on Start > Control Panel > Region and Language. Change the Format to English (United States). After converting your spreadsheet to a CSV file, change this format back to original one, in order to keep your formatting in existing Excel files. 
  • Open your Excel sheet go to File > Save As.
  • Choose Unicode Text (*.txt) from the drop-down list next to Save as type, enter the file name and then click Save.
  • Open the unicode .txt file in the Notepad (right click on the file, choose Open with... > Notepad).
  • This unicode text file is tab-delimited, so you need to replace all tabs with commas. Select a tab character, right click it and choose Copy from the context menu, or simply press CTRL+C.
User-added image
  • In the Notepad menu bar click on Edit > Replace.... Right click on the Find what field and choose Paste from the context menu, or press CTRL+V. In the Replace with field enter a comma and click on Replace All and close that box.
  • Then click on File > Save As, choose All files (*.*) next to Save as type, enter a file name followed by .csv, then choose UTF-8 next to Encoding field and click Save.
User-added image


If you are using OpenOffice Calc for Windows, Linux or Mac OS.
  • In the main menu choose File > Save As….
  • Next to Save as type select Text CSV (.csv) from the drop-down list, enter the file name and click on Save.
User-added image
  • If you get a warning that your document may contain formatting or content that cannot be saved in the Text CSV file format, click on Keep Current Format button.
  • In the Export Text File box choose Unicode (UTF-8) in Character set field and choose comma as Field delimiter.
  • By default both options Quote all text cells* and Fixed column width are unchecked, the option Save cell content as shown** is checked. It's recommended to leave them unchanged.
User-added image
  • click OK.   
*  Exports all text cells with leading and trailing quote characters as set in the Text delimiter box. If not checked, only those text cells get quoted that contain the Field delimiter character.
** Data will be saved as displayed, including applied number formats. If this checkbox is not marked, raw data content will be saved.


 
If you are using Ms Excel 2008 / 2011 for Mac.
  • In the main menu choose File > Save As….
  • From the Format drop-down list choose UTF-16 Unicode Text, enter the file name and click Save.
  • Open this document in TextEdit.
  • This unicode text file is tab-delimited, so you need to replace all tabs with commas. Select a tab character, click on Edit > Copy from the main menu, or simply press Command+C.
  • In the main menu choose Edit > Find > Find and Replace…
  • Click on Find field and choose Edit > Paste from the main menu, or press Command+V. In the Replace with field enter a comma and click on Replace All button, then close this box.
User-added image
  • Now you need to change the file extension from txt to csv. Select your file and press Command+I. In the Name & Extension section replace .txt with .csv and hit Enter.
  • You will see a warning saying, whether you want to change the file extension. Click "Use .csv" button. 

 




promote demote