What should I do if the csv export file does not show Russian characters? Excel to CSV with UTF8 encoding Xls hieroglyphs.

Often, when working with various files in Excel, the user may encounter the fact that instead of clear letters there will be an incomprehensible font that is impossible to read. This is due to incorrect encoding. In this article we will talk about how to change the encoding in Excel so that words can be read.

Method one: using Notepad++

It so happens that it is easier to change text encoding in a third-party program than in Excel itself. That is why we will now look at how to change the encoding in Excel using the Notepad++ program.

So, to perform all the steps correctly, follow the instructions:

Launch Notepad++.
Click on the "File" button.
In the menu that appears, select “Open” (this can also be done by pressing the key combination CTRL+O).
In the Explorer window that appears, navigate to the desired file, the encoding of which is incorrect, and click the "Open" button.
In the file that opens, click the "Encodings" button, which is located above the toolbar.
In the menu, select the “Convert to UTF-8” item, since this is the encoding that the Excel program accepts with a bang.
Click the "Save" button on the left side of the toolbar and close the program window.

That's all, now you know how to change the encoding in Excel using one of the following methods. Although it is the fastest, most convenient and simplest, there are others that cannot be kept silent about.

Method two: using the "Text Wizard"

Directly in the program itself Microsoft Excel There are also tools that will allow you to change the encoding. This is exactly what we will talk about now, or rather, about the “Text Master”:

Open Excel. Please note that it should not be launched by double-clicking on the file that is displayed incorrectly, but directly by the program with a blank sheet.
Go to the "Data" tab.
Click the "Get External Data" button and select "From Text" from the drop-down menu.
In the Explorer window that appears, specify the path to the file that has encoding problems and click the "Import" button.
Now the “Text Wizard” window itself will open. It is necessary to change the file format in it, which is why click on the file format of the same name and select “Unicode (UTF-8)” from there.
Click Next.
Also skip the next window by clicking the “Next” button; all settings in it should be by default.
Now you need to determine the data format of the file column and, in accordance with it, select one of the items in the list of the same name. Finally, click “Done”.

After all this, you need to specify the very first cell so that the data fits at the beginning of the table.

It’s clear how to change the encoding in Excel using the second method. Let's move on to the third.

Method three: saving

How to change encoding in Excel? To implement the third method, you need to perform the following steps:

Open the file in the program.
Click "File".
Select "Save As".
In the menu that appears, select the extension and click "Tools", from the drop-down menu - "Web Document Options".
In the window that appears, go to the "Encoding" tab and select it from the "Save document as" list.
Click OK.

Now all that remains is to specify the folder where the file will be saved. The next time you open it, the text should be displayed correctly.

Users working in browsers, text editors and processors often face the need to change text encoding. However, even when working in table processor Excel, such a need may also arise, because this program processes not only numbers, but also text. Let's figure out how to change the encoding in Excel.

Text encoding is a set of electronic digital expressions that are converted into user-friendly characters. There are many types of encoding, each of which has its own rules and language. The ability of a program to recognize a specific language and translate it into characters (letters, numbers, other symbols) that are understandable to the average person determines whether the application can work with a specific text or not. Among the popular text encodings, the following should be highlighted:

Windows-1251;
KOI-8;
ASCII;
ANSI;
UKS-2;
UTF-8 (Unicode).

The last name is the most common among encodings in the world, as it is considered a kind of universal standard.

Most often, the program itself recognizes the encoding and automatically switches to it, but in some cases the user needs to indicate its type to the application. Only then will it be able to work correctly with encoded characters.

The greatest number of problems with deciphering the encoding Excel programs occurs when trying to open CSV files or export txt files. Often, instead of ordinary letters, when opening these files through Excel, we can observe incomprehensible characters, the so-called “krakozyabry”. In these cases, the user needs to perform certain manipulations in order for the program to begin displaying data correctly. There are several ways to solve this problem.

Method 1: Changing Encoding Using Notepad++

Unfortunately, Excel does not have a full-fledged tool that would allow you to quickly change the encoding of any type of text. Therefore, for these purposes, you have to use multi-step solutions or resort to help third party applications. One of the most reliable ways is to use the Notepad++ text editor.

Despite the fact that this method is based on the use of third-party software, it is one of the most simple options for transcoding the contents of files in Excel.

Method 2: Using the Text Wizard

In addition, you can make the conversion using the program’s built-in tools, namely the Text Wizard. Oddly enough, using this tool is somewhat more complicated than using the third-party program described in the previous method.

Method 3: saving the file in a specific encoding

There is also the opposite situation, when the file does not need to be opened with the data displayed correctly, but saved in the specified encoding. You can also perform this task in Excel.

The document will be saved on your hard drive or removable storage device in the encoding that you defined yourself. But you need to take into account that now documents saved in Excel will always be saved in this encoding. In order to change this, you will have to go into the window again "Web Document Options" and change settings.

There is another way to change the encoding settings of saved text.

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 Alternative option file format for import/export. Moreover, the CSV format allows the user to look into the file and immediately find a data problem, if any, change the CSV delimiter, citation rules, and so on. This is possible because CSV is simple text, and even a less experienced user can easily understand it without special training.

In this article we will explore fast and effective ways export data from Excel to CSV and learn how to convert an Excel file to CSV, preserving all special and foreign characters without distortion. The techniques described in the article work in all versions of Excel 2013, 2010 and 2007.

How to Convert an Excel File to CSV

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

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

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

If the Excel sheet contains any special or foreign characters (tilde, accent, etc.) or hieroglyphs, then converting the Excel sheet to CSV using the method described above will not work.

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

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, because most of these symbols require 1 byte of memory. Another advantage is that the encoding of a UTF-8 file containing only ASCII characters will be no different 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. A significant drawback 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 an Excel File to CSV UTF-8

Let's say we have an Excel sheet with foreign characters, in our example these are Japanese names.

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

Comment: 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, for example, Notepad++.

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

Comment: If there is no strict need to get a comma-delimited file, but you need any CSV file that Excel can understand, then you can skip this step, since Microsoft Excel perfectly understands tab-delimited files.

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

How to Convert an Excel File to CSV UTF-16

Exporting to a UTF-16 CSV file is much faster and easier than UTF-8. The problem is that Excel automatically applies the UTF-16 format when saving 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. Ready!

If you need a CSV file with a comma or semicolon as delimiter, replace all tab characters with commas or semicolons respectively in Notepad or any other text editor of your choice (earlier in this article there are detailed instructions, how it's done).

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 produce a pure UTF-8 file (this does not apply, which can export Excel files to several UTF encoding options). But in most cases the resulting file will contain correct set characters, which can then be painlessly converted to UTF-8 format using any text editor.

Converting an Excel file to CSV using Google Sheets

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

Advice: If your Excel file is relatively small, you can save time by copying/pasting the data into a Google spreadsheet.

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

Save the .xlsx file as .xls and then convert it to a 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 feedback from many users, some special characters are lost if saved directly from .xlsx V .csv, but remain if first .xlsx save as .xls, and then how .csv, like us .

One way or another, try this method of creating CSV files from Excel yourself, and if it works, it will be a good time saver.

Saving an Excel file as CSV using OpenOffice

OpenOffice is a suite of open-source applications source code, includes a spreadsheet application that does a great job of exporting data from Excel to CSV format. In fact, this application provides access to more options when converting tables to CSV files (encoding, delimiters, etc.) than Excel and Google Sheets combined.

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 asks you to select parameter values Encoding(Character sets) and Field separator(Field delimiter). Of course, if we want to create a comma-delimited UTF-8 CSV file, we choose 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 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 configure parameters in the same way when creating CSV files.

In this article, I talked about the methods I know of to convert Excel files to CSV. If you are more familiar effective methods export from Excel to CSV, tell us about it in the comments. Thank you for attention!

Files and documents created on a computer always have their own encoding. It often happens that when exchanging files or downloading them from the Internet, the encoding in which they were created is not readable by our computer. The reasons for this may be different - either the lack of the necessary encoding in the program with which we want to open the file, or simply the absence of some program components ( additional package fonts, for example).

Below we will look at how to change the encoding of an unreadable file or document in different programs.

Changing the encoding on a browser page

For Google Chrome

Select the menu “Settings” → “Tools”.
The line “Encoding” - we point the mouse, and a list of possible encodings appears in the browser.
We select “Windows 1251” for Russian sites. If it doesn’t help, try “Automatic”.

For Opera

Click “Opera” → “Settings”
On the left menu “Websites” → field 2 “Display” → “Customize fonts”
In the “Encoding” field, select “Cyrillic (Windows 1251)”.

For Firefox

Firefox → Settings → Content.
Opposite the “Default font” line, click the “Advanced” button.
At the bottom of the window, select “Encoding” &rarr “Cyrillic (Windows 1251)”.

Changing encoding in Word

Let's look at the procedure for changing the encoding using Word 2010 as an example.

Open the document.
File tab → Options.
Select the “Advanced” line. In the “General” section, next to the line “Confirm file format conversion when opening”, check the box. Click OK.
Next, the “File Conversion” window will open. Select “Encoded Text” and click OK.
Next, in the window that opens, mark “Other” and select from the list the encoding that will display the required text. In the “Sample” window you can see how text is displayed in a particular encoding that we have chosen.

If the above procedure did not help display the document, you can try changing the font. Sometimes a document may appear as “squares” or other characters if the program does not have the appropriate font.

Changing encoding in Excel

Let's look at the procedure for changing the encoding for Excel 93-2004 and 2007:

Open an unreadable document using Notepad++.
Select the menu Encoding → Convert to UTF-8.
The characters will not change, only the encoding at the bottom of the screen will change. Next, select a character set. If it is Russian: Encoding → Character sets → Cyrillic → Windows-1251.
Click "Save". Open the file in Excel. If the text is not readable, try repeating steps 3-4.

Changing text encoding

Open the file in the standard word processor Notepad.
Click “Save As”.
In the saving window that opens, select the location where we want to save the file, the document type - text, and also set a different encoding type.
Save.
Let's try to open the document.

I have an Excel file that has some Spanish characters(Tildes, etc.) that I need to convert to a CSV file to use as an import file. However, when I save as CSV, it mangles the "special" Spanish characters, which are not ASCII characters. This also seems to happen with left and right quotes and em dashes, which appears to be coming from the original user creating the Excel file on a Mac.

since CSV is just a text file, I'm sure it can handle UTF8 encoding, so I'm assuming this is a limitation of Excel, but I'm looking for a way to get from Excel to CSV and store non-ASCII characters.

30 answers

Note: Be careful with sensitive data from Google Sheets.

after saving to CSV under Excel in command line put:

Iconv -f cp1250 -t utf-8 file-encoded-cp1250.csv > file-encoded-utf8.csv

(Remember to replace cp1250 with your encoding).

works quickly and is great for large files, such as a postal code database, that cannot be imported into GoogleDocs (400,000 cell limit).

Assuming a Windows environment, save and work with the file as usual in Excel, but then open the saved Excel file in Gnome Gnumeric (free). Save the Gnome Gnumeric table as CSV, which - for me anyway - saves it as UTF-8 CSV.

easy way to do it: download open office(), download spreadsheet and open the excel file (.xls or .xlsx). Then simply save it as a CSV text file and a window will open asking you to save the current format or save as .ODF format. Select "Keep current format" and in the new window, select the option that works best for you, according to the language in which your file is written. For Spanish, select Western Europe (Windows-1252/ WinLatin 1) and the file works just fine. If you select Unicode (UTF-8), it will not work with Spanish characters.

I also faced the same problem but there is a simple solution for this.

open the xlsx file in Excel 2016 or higher.
In "Save As" select this option: "(CSV UTF-8 (comma delimited)*.csv)"

It works great and creates a csv file that can be imported into any software. I have imported this csv file into my database SQLITE data and it works fine with all unicode characters.

I wrote a small Python script that can export sheets to UTF-8.

you just need to provide the Excel file as the first parameter and then the sheets you want to export. If no sheets are provided, the script exports all sheets present in the Excel file.

#!/usr/bin/env python # export data sheets from xlsx to csv from openpyxl import load_workbook import csv from os import sys reload(sys) sys.setdefaultencoding("utf-8") def get_all_sheets(excel_file): sheets = workbook = load_workbook(excel_file,use_iterators=True,data_only=True) all_worksheets = workbook.get_sheet_names() for worksheet_name in all_worksheets: sheets.append(worksheet_name) return sheets def csv_from_excel(excel_file, sheets): workbook = load_workbook(excel_file,use_iterators=True ,data_only=True) for worksheet_name in sheets: print("Export " + worksheet_name + " ...") try: worksheet = workbook.get_sheet_by_name(worksheet_name) except KeyError: print("Could not find " + worksheet_name) sys.exit (1) your_csv_file = open("".join(), "wb") wr = csv.writer(your_csv_file, quoting=csv.QUOTE_ALL) for row in worksheet.iter_rows(): lrow = for cell in row: lrow. append(cell.value) wr.writerow(lrow) print(" ... done") your_csv_file.close() if not 2<= len(sys.argv) <= 3: print("Call with " + sys.argv + " ") sys.exit(1) else: sheets = if len(sys.argv) == 3: sheets = list(sys.argv.split(",")) else: sheets = get_all_sheets(sys.argv) assert( sheets != None and len(sheets) > 0) csv_from_excel(sys.argv, sheets)