How to Convert Excel Files to CSV Format

CSV (Comma Separated Values) is a common format for storing tabular data (numeric and text) in plain text. This file format is popular and enduring due to the fact that a huge number of programs and applications understand CSV, at least as an alternative file format for import / export. Moreover, the CSV format allows the user to look into the file and immediately find a problem with the data, if any, change the CSV delimiter, quoting rules, and so on. This is possible because CSV is a simple text, and even a not very experienced user can easily understand it without special training.

In this article, we’ll learn quick and efficient ways to export data from Excel to CSV and learn how to convert an Excel file to CSV without distorting all special and foreign characters. The techniques described in the article work in all versions of Excel 2013, 2010 and 2007.

• Convert data from Excel to CSV format
• Export from Excel to CSV UTF-8 or UTF-16 format
• Other ways to convert from Excel to CSV (preserving special characters)

How to Convert Excel File to CSV

If you want to export the Excel file to some other application, such as an Outlook address book or an Access database, first convert the Excel sheet to a CSV file, and then import the file . Csv to another application. The following is a step by step guide on how to export an Excel workbook to CSV format using the Excel tool – “Save as».

1. In an Excel workbook, open the tab File (File) and click Save as (Save as). In addition, the dialog box Saving a document (Save as) can be opened by pressing the key F12.
2. In the File Type (Save as type) select CSV (separated by commas) (CSV (Comma delimited)).In addition to CSV (comma delimited), several other CSV format options are available:
   • CSV (separated by commas) (CSV (Comma delimited)). This format stores Excel data as a comma delimited text file and can be used in another Windows application and in a different version of the Windows operating system.
   • CSV (Macintosh). This format saves an Excel workbook as a comma delimited file for use on the Mac operating system.
   • CSV (MS DOS). Saves an Excel workbook as a comma-delimited file for use in the MS-DOS operating system.
   • Unicode text (Unicode Text (*txt)). This standard is supported by almost all existing operating systems, including Windows, Macintosh, Linux, and Solaris Unix. It supports characters from almost all modern and even some ancient languages. Therefore, if an Excel workbook contains data in foreign languages, I recommend that you first save it in the format Unicode text (Unicode Text (*txt)), and then convert to CSV as described later in Export from Excel to UTF-8 or UTF-16 CSV format.

Note: All mentioned formats only save the active Excel sheet.

3. Select a folder to save the CSV file and click Save (Save).After pressing Save (Save) two dialog boxes will appear. Don’t worry, these messages don’t indicate an error, that’s how it should be.
4. The first dialog box reminds you that Only the current sheet can be saved in the file of the selected type (The selected file type does not support workbooks that contain multiple sheets). To save only the current sheet, just press OK.If you want to save all sheets of the book, then click cancellation (Cancel) and save all sheets of the book individually with the appropriate file names, or you can choose to save another file type that supports multiple pages.
5. After clicking OK in the first dialog box, a second one will appear, warning that some features will become unavailable because they are not supported by the CSV format. This is how it should be, so just click Yes (Yes).

This is how an Excel worksheet can be saved as a CSV file. Quick and easy, and hardly any difficulties can arise here.

Export from Excel to CSV with UTF-8 or UTF-16 encoding

If the Excel sheet contains any special or foreign characters (tilde, accent, and the like) or hieroglyphs, then converting the Excel sheet to CSV in the manner described above will not work.

The point is that the team Save as > CSV (Save as > CSV) will mangle all characters except ASCII (American Standard Code for Information Interchange). And if there are double quotes or long dashes on the Excel sheet (transferred to Excel, for example, from a Word document when copying / pasting text) – such characters will also be shredded.

Easy Solution – Save Excel Sheet as Text File Unicode(.txt), and then convert it to CSV. This way, all non-ASCII characters will remain intact.

Before moving on, let me briefly explain the main differences between UTF-8 and UTF-16 encodings, so that in each individual case you can choose the appropriate format:

• UTF-8 is a more compact encoding that uses 1 to 4 bytes for each character. It is most often recommended to use this format when ASCII characters predominate in the file, as most of these characters require 1 byte of memory. Another advantage is that the encoding of a UTF-8 file containing only ASCII characters will not differ in any way from the same ASCII file.
• UTF-16 uses 2 to 4 bytes to store each character. Please note that not in all cases a UTF-16 file requires more memory space than a UTF-8 file. For example, Japanese characters take up 3 to 4 bytes in UTF-8 and 2 to 4 bytes in UTF-16. Thus, it makes sense to use UTF-16 if the data contains Asian characters, including Japanese, Chinese, and Korean. The main disadvantage of this encoding is that it is not fully compatible with ASCII files and requires special programs to display such files. Keep this in mind if you plan to import the resulting files from Excel somewhere else.

How to Convert Excel File to CSV UTF-8

Suppose we have an excel sheet with foreign characters, in our example they are Japanese names.

To export this Excel sheet to a CSV file, while keeping all the hieroglyphs, we will do the following:

1. In Excel, open the tab File (File) and click Save as (Save as).
2. Enter the file name in the field File Type (Save as type) select Unicode text (Unicode Text (*.txt)) and click Save (Save).
3. Open the created file in any standard text editor, such as Notepad.

Note: Not all simple text editors fully support Unicode characters, so some may appear as rectangles. In most cases, this will not affect the final file in any way, and you can simply ignore it or choose a more advanced editor, such as Notepad++.

4. Since our Unicode text file uses the tab character as delimiters, and we want to convert it to CSV (comma delimited), we need to replace the tab characters with commas.

Note: If there is no strict need to get a file with comma delimiters, but you need any CSV file that Excel can understand, then this step can be skipped, since Microsoft Excel perfectly understands files with a delimiter – tabulation.

4. If you still need a CSV file (separated by commas), then do the following in Notepad:
   • Select the tab character, right-click on it, and in the context menu, click Copy (Copy), or just click Ctrl + Cas shown in the figure below.
   • Press Ctrl + Hto open the dialog box Substitute (Replace) and paste the copied tab character into the field That (Find what). In this case, the cursor will move to the right – this means that a tab character is inserted. In field Than (Replace with) enter a comma and press Replace all (Replace all).

   In Notepad, the result will be something like this:

   

5. Click File > Save as (File > Save as), enter a name for the file and in the drop-down list Encoding (Encoding) select UTF-8… Then press the button Save (Save).
6. Launch Windows Explorer and change the file extension from .txt on . Csv.Change extension differently .txt on . Csv You can do it directly in Notepad. To do this, in the dialog box Save as (Save as) in the field File Type (Save as type) select an option All files (All files), and add “.csv” to the file name in the corresponding field, as shown in the figure below.
7. Open the CSV file in Excel, for this, on the tab File (Filet) knead Open > Text files (Open > Text files) and check if the data is ok.

Note: If your file is intended to be used outside of Excel and UTF-8 format is a requirement, then do not make any changes to the sheet and do not save it again in Excel, as this may lead to problems with reading the encoding. If some part of the data is not displayed in Excel, open the same file in Notepad and correct the data in it. Don’t forget to save the file in UTF-8 format again.

How to Convert Excel File to CSV UTF-16

Exporting to a UTF-16 CSV file is much faster and easier than exporting to UTF-8. The fact is that Excel automatically applies the UTF-16 format when you save the file as Unicode text (Unicode Text).

To do this, save the file using the tool Save as (Save as) in Excel and then in Windows Explorer, change the extension of the created file to . Csv. Done!

If you need a CSV file with a semicolon or semicolon as delimiter, replace all tab characters with commas or semicolons respectively in Notepad or any other text editor of your choice (see earlier in this article for detailed instructions on how to do this).

Other Ways to Convert Excel Files to CSV

The methods described above for exporting data from Excel to CSV (UTF-8 and UTF-16) are universal, i.e. suitable for working with any special characters and in any version of Excel from 2003 to 2013.

There are many other ways to convert data from Excel to CSV format. Unlike the solutions shown above, these methods will not result in a pure UTF-8 file (this does not apply to OpenOffice, which can export Excel files in several UTF encoding options). But in most cases, the resulting file will contain the correct character set, which can then be painlessly converted to UTF-8 format using any text editor.

Convert Excel File to CSV Using Google Sheets

As it turns out, it’s very easy to convert an Excel file to CSV using Google Sheets. Provided that Google Drive is already installed on your computer, follow these 5 simple steps:

1. In Google Drive click the button Create (Create) and select Table (Spreadsheet).
2. On the menu File (Filet) knead Import (Import).
3. Click Download (Upload) and select the Excel file to upload from your computer.
4. In the dialog box Impfile ort (Import file) select Replace table (Replace spreadsheet) and click Import (Import).

Tip: If the Excel file is relatively small, then to save time, you can transfer data from it to a Google spreadsheet using copy / paste.

5. On the menu File (Filet) knead Download as (Download as), select file type CSV – the file will be saved on the computer.

Finally, open the generated CSV file in any text editor to make sure all characters are saved correctly. Unfortunately, CSV files created this way do not always display correctly in Excel.

Save .xlsx file as .xls and then convert to CSV file

This method does not require any additional comments, since everything is already clear from the name.

I found this solution on one of the forums dedicated to Excel, I don’t remember which one. To be honest, I have never used this method, but according to many users, some special characters are lost when saving directly from . Xlsx в . Csv, but remain if first . Xlsx save as .xls, and then like . Csv, as we did at the beginning of this article.

Anyway, try this method of creating CSV files from Excel for yourself, and if it works, it will be a good time saver.

Saving an Excel file as CSV using OpenOffice

OpenOffice is an open source suite of applications that includes a spreadsheet application that does a great job of exporting data from Excel to CSV format. In fact, this application gives you access to more options when converting spreadsheets to CSV files (encoding, delimiters, and so on) than Excel and Google Sheets put together.

Just open the Excel file in OpenOffice Calc, click File > Save as (File > Save as) and select the file type CSV text (Text CSV).

The next step is to select the parameter values Encoding (Character sets) и Field separator (Field delimiter). Of course, if we want to create a UTF-8 CSV file with commas as delimiters, then select UTF-8 and enter a comma (,) in the appropriate fields. Parameter Text separator (Text delimiter) is usually left unchanged – quotation marks (“). Next click OK.

In the same way, for a quick and painless conversion from Excel to CSV, you can use another application – LibreOffice. Agree, it would be great if Microsoft Excel provided the ability to also adjust the settings when creating CSV files.

In this article, I talked about the methods that I know about converting Excel files to CSV. If you know more efficient methods for exporting from Excel to CSV, please tell us about it in the comments. Thank you for attention!