This is a guide on how to manually convert a Excel XP, Excel 2003, Excel 2007, Excel 2010 and Excel 2013 Spreadsheet to a CSV file. You can convert XLS to CSV automatically and schedule conversion of XLS to CSV using our software tool FileSculptor.
This tutorial covers Excel 2013. If you use a different version of Excel click on the version below:
|
Excel 2013
|
Why Use a CSV File
CSV is a popular format to transfer data between services and applications, because of its simplicity: only text values and value separators. Converting a spreadsheet into CSV format makes sense since most applications support CSV format rather than XLS or any other format. Click here to read more about the CSV Format.
Open the Excel File
Open the Excel file that you want to save for use in another program. Activate the worksheet you want to export or save as a CSV file.
Excel exports to a CSV file only the text and values as they are displayed in cells of the active worksheet. All rows and all displayed values in each cell are saved. Columns of data are separated by commas, and each row of data ends in a carriage return. If a cell contains a comma, the cell contents are enclosed in double quotation marks.
Sample of file in Excel 2013
Click here to download the sample XLSX file displayed here where you can see the difference between cell contents and displayed values. The Order ID column is a number formatted to display in five positions including zeroes at left. The Order Date column is a date formatted to display in the format YYYYMMDD. The Order Amount column is configured to not show the thousand separator.
Save As CSV Format
To convert the Excel spreadsheet to CSV, we will use the Save As command with the CSV Format option for file type. Excel will make a copy of the original file to a new file in the selected format. Click the File option, and then click Save As. The Save As dialog box will appear. In the Save as type box, choose the format CSV (Comma delimited). Browse to the location where you want to save the new text file, and then click Save.
Save As file in Excel 2013
Save file as CSV type in Excel 2013
Confirm Conversion
A dialog box will appear, asking you to save only the active sheet as a CSV file, click OK.
Confirm conversion to CSV
A second dialog box will appear, asking you to leave out any incompatible features with a CSV file, click Yes.
Confirm conversion to CSV again
Note: the Save As command shows three file type options for saving a CSV file. They differ on how special characters in the CSV file that control line breaks, tab and other features are recognized by different operating systems. Use the Macintosh or MS-DOS formats only if you intend to use the CSV file on those operating systems.
CSV (comma delimited) format: Saves a worksheet as a comma-delimited text file for use on another Windows operating system.
CSV (Macintosh) format: Saves a worksheet as a comma-delimited text file for use on the Macintosh operating system.
CSV (MS-DOS) format: Saves a worksheet as a comma-delimited text file for use on the MS-DOS operating system.
View CSV File Contents
After the spreadsheet is converted to the CSV format, close Excel without saving and use a text editor like Notepad or Word to view its contents. Do not open it using Excel. To open the CSV File in Excel correctly use our guide Convert CSV Files to Excel.
View Converted CSV file in Notepad
CSV Comma or Semicolon Separator
When Excel saves a spreadsheet to the CSV Format, it may use a comma (,) or a semicolon (;) to separate the contents. To change the CSV separator to comma or semicolon, close Excel and access the Control Panel in Windows.
In Windows Vista/7/8/2008, click the Start button, and then click Control Panel. Open Clock, Language and Regional Options and click Change the date, time or number format. In the Formats tab click Aditional Settings button. In the Numbers tab type a new separator in the List separator box and click OK button to close both configuration dialog windows.
In Windows XP/2000/2003, click the Start button, and then click Control Panel. Click on Date, Time, Language, and Regional Options and Regional and Language Options icon. In the Regional Options tab click Customize. In the Numbers tab type a new separator in the List separator box and click OK button to close both configuration dialog windows.
After you change the list separator character for your computer, all programs use the new character as a list separator. You can change the character back to the default character by following the same procedure.
Converting Date Values to CSV
CSV files only store text values. If you have a cell with a date value content, when you convert it to CSV, it might not be recognized by the destination. For example “01-12-14” might not be recognized by a program that expects dates in a different format. A program that reads the value “01-12-14” might either interpret it as Jan-12-1914, Jan-12-2014, Dec-01-1914 or Dec-01-2014 depending upon system configurations.
To avoid such problem, check what the destination program supports for date values and format the cells accordingly. If you are creating a generic CSV, try to use a standard date values, like for example “YYYYMMDD”. In this case our sample date value would become “20140112”. To do this, format the cells with Custom format, and enter “yyyymmdd” in the format field.
Converting Currency Values to CSV
CSV files only store text values. If you have a cell with a currency value content, when you convert it to CSV, it might not be recognized by the destination. For example “1,100.10” might not be recognized by a program that expects amounts in a different format. The program might not understand the double quotes created because the value contains a comma as thousands separator. Or might expect a different decimal separator depending upon system configurations.
To avoid such problems, check what the destination program supports for currency values and format the cells accordingly. If you are creating a generic CSV, try to use a standard for currency values, like for example values without the thousands separator or that contains only numbers (multiply the values by 100) . In this case our sample currency value does not carry the thousands separator. To do this, format the cells with Custom format, and choose the “0.00” option.