Export ranges from Excel to CSV with any delimiter and any character encoding

Widespread use of plain text CSV files is easily explained – this format is supported by the majority of applications. So saving Excel spreadsheets as comma-delimited CSV files is a familiar operation. But what if you need to export all worksheets at once, or apply a different delimiter? Besides, saving as CSV automatically closes the source Excel file – and it becomes tiresome when you have to export data repeatedly.

The Export to CSV feature simplifies the conversion of XLSX (XLS) tables to CSV format:

Export to CSV is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

Download XLTools for Excel

1. On XLTools tab, click the Export button Choose Export to CSV from the drop-down list A window will open.

2. Choose Selected range Select the range you want to export.

3. Set processing options.

   

   

4. Check the box Open file after export, if you want to see the result immediately.

   

   Note: the resulting CSV file will open either in Notepad, or in Excel. This depends which program you have as default to open *.csv files on your computer.

5. Click OK Save the CSV file to your computer Done.

See that the source Excel file remains open and unchanged. Learn more.

1. On XLTools tab, click the Export button Choose Export to CSV from the drop-down list A window will open.

2. Choose This worksheet or All worksheets in this workbook.

3. Set processing options.

   

   

4. Сheck the box Open file (folder) after export, if you want to see the result immediately.

   

   Note: the resulting CSV file will open either in Notepad, or in Excel. This depends which program you have as default to open *.csv files on your computer.

5. Click OK Save the CSV file(s) to your computer Done.

Another approach to save multiple worksheets as CSV files, is to use XLTools Workbook Organizer.

1. On XLTools tab, click the Workbook Organizer button A window will open.

2. See the list of all worksheets in all open workbooks Select one, all or just a few worksheets you want to export.

3. Click the Save icon From the drop-down list select Save sheet(s) as separate files.

   

   

4. The window will open Choose to save as comma-separated values *.csv files Set the processing options.

5. Click OK Save the CSV file(s) to your computer Done.

You can define how to process and export values to CSV format:

• Save values As formatted values

  Select this option, if you want to export values in the format that matches Excel formatting – as displayed in cells. For example, 10%, 12-Mar-2016, $40.

  

  

• Save values As actual values

  Select this option, if you want to export values in default format – as displayed in Excel formula bar. For example, 0.1, 3/12/2016, 40.00.

  

  

• Check the box With qualifiers

  Check this option, if you need to set field qualifiers. You can type " (double quote), or ' (single quote), or any other character. The add-in will add the qualifiers to every field in the resulting CSV file. This option would be helpful if you process CSV files in legacy systems.

  

  

  Additionally, you can check the box remove _ from data. This will keep the qualifier qualifier character only in the beginning and end of the field and remove it from all other instances.

  For example, if you add ' (single quotes) as field qualifiers, while some cells in your source Excel table already contain ' (single quotes or apostrophes), this will obscure the field limits in the CSV file. So, you can choose to remove any other single quote characters that are not qualifiers from the resulting CSV file. The source Excel file will remain intact.

  

  

• Check the box Unmerge cells and duplicate values

  Check this option, if your source table has merged cells. The add-in will duplicate values from merged cells into the corresponding places of the resulting CSV text string.

  

  

You can save an Excel table as a CSV file with a standard comma delimiter, or choose a different suitable separator between values in the output CSV file:

• comma
• semicolon
• tab
• vertical bar (pipe)

You can choose the necessary character encoding for the output CSV file. Unicode UTF-8 is the most common encoding and is recommended as a default option:

• Unicode (UTF-8)
• ISO
• ISCII
• Unicode, or any other

When you convert Excel files using "Save As…CSV", a message appears: "Some features in your workbook might be lost". Next, it seems that your source XLSX file is replaced by the CSV file. What actually happens is that your original Excel file is automatically closed, and the newly generated CSV file opens in Excel interface. So each time you save an Excel worksheet as CSV, you have to reopen the source Excel file again and again. It is rather inconvenient and brings the risk of losing unsaved changes to the source data.

The Export to CSV feature streamlines the XLSX to CSV routine:

1. Export to CSV a selected range, or multiple sheets at once.
2. Notice that the source Excel file remains open and unchanged. Its extension did not change from *.xlsx to *.csv.
3. Continue – uninterrupted – work with the source file, or export ranges to CSV again.

If you want to export data to other formats, such as TXT, HTML, PDF or others, check out the Export Sheets tool. It will help you save multiple Excel worksheets as separate files in the format you need.