The need to change the text encoding is often encountered by users working in browsers, text editors and processors. However, when working in an Excel spreadsheet processor, such a need may also arise, because this program processes not only numbers, but also text. Let's see how to change the encoding in Excel.
Lesson: Encoding in Microsoft Word
Text encoding is a set of electronic digital expressions that are converted into user-friendly characters. There are many types of encoding, each with its own rules and language. The ability of a program to recognize a specific language and translate it into characters understandable to an ordinary person (letters, numbers, other symbols) 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 latter name is the most common among the 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 specify its type to the application. Only then will it be able to work correctly with encoded characters.
The greatest number of problems with decoding the encoding in Excel programs encountered 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 "crazy". In these cases, the user needs to perform certain manipulations in order for the program to start displaying the data correctly. There are several ways to solve this problem.
Method 1: Change encoding with Notepad++
Unfortunately, Excel does not have a full-fledged tool that would allow you to quickly change the encoding in any type of text. Therefore, it is necessary to use multi-step solutions for this purpose or to resort to the help of third party applications. One of the most reliable ways is to use the Notepad++ text editor.
Though this way based on the use of third party software, it is one of the most simple options to convert the contents of files to Excel.
Method 2: Using the Text Wizard
In addition, you can make the conversion using the built-in tools of the program, namely the Text Wizard. Oddly enough, using this tool is somewhat more complicated than using third party program described in the previous method.
Method 3: Save the file in a specific encoding
There is also the opposite situation, when the file does not need to be opened with the correct display of data, but saved in the set encoding. Excel can also do this task.
The document will be saved on the hard drive or removable media in the encoding that you have 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 back to the window "Web Document Options" and change settings.
There is another way to change the encoding settings of the saved text.
I have excel file, which 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 "special" Spanish characters that are not ASCII characters. This also seems to happen with left and right quotes and em dashes, which seem to come from the original user creating the Excel file on Mac.
since CSV is just a text file, I'm pretty sure it can handle UTF8 encoding, so I'm assuming it's an Excel limitation, but I'm looking for a way to get from Excel to CSV and keep non-ASCII characters.
30 responses
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 fast and is great for large files like postcode database that can't be imported into GoogleDocs (400.000 cell limit).
assuming a Windows environment, save and work on 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 a UTF-8 CSV.
easy way to do it: download open office(), download spreadsheet and open the excel file (.xls or .xlsx). Then just save it as a text CSV file and a window will open asking you to keep 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 your file is written in. For Spanish, choose Western Europe (Windows-1252/ WinLatin 1) and the file works just fine. If you choose Unicode (UTF-8), it won't work with Spanish characters.
I also faced the same problem, but there is a simple solution for this.
- open .xlsx file in Excel 2016 or higher.
- In "Save As" select this option: "(CSV UTF-8 (comma delimited)*.csv)"
It works great and a csv file is created which can be imported to any software. I have imported this csv file into my SQLITE database 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 an excel file as the first parameter and then the sheets you want to export. If no sheets are provided, the script will export 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 + "
Why Do Doodles and Hieroglyphs Appear in Excel? This problem can have several variants, I will collect most of the solutions in this article. Well, at the same time I will write how to add and use hieroglyphic characters in the text.
Encoding problem often occurs in different programs. Who is sure faced with the problem of changing the encoding. Let's take a look at other cases.
If you see scribbles/hieroglyphs in Excel when you open the file, changing the encoding might help. Resave the file in the following way:
On the Encoding tab, select Unicode (UTF-8) or Cyrillic (Windows)
Resave the file.
Wrong encoding when getting data from external sources. Opening CSV files
If, when pasting data from other files or external sources scribbles / unknown characters are obtained, it is also recommended to clarify the encoding. You can do this in the following way:
On the Ribbon, select the Data tab, and in the Get External Data section, select the option you want.
If you are inserting regular data from a file, i.e. text or table, select From text
Specify the file from which we collect data, then select the data format, and most importantly, in the File Format section, select Unicode (UTF-8).
What to choose with a separator or a fixed width (step 1), as well as the next step (step 2) are described in detail in this .
In step 3, select the "General" data format.
Opening with the wrong program
I also recommend checking which program you open the Excel file with. It happens that books 2010 excel format open in Excel 2003 (or earlier) with the text changed to hieroglyphs. Learn more about Excel formats.
Exactly the same problem periodically occurs if you open complex Excel-books with a large data set through alternative spreadsheet editors such as LibreOffice.Calc, Apache OpenOffice and others.
How to add characters-hieroglyphs in the text?
“I’m fine with the text, I need to insert a hieroglyph / symbol in Excel,” you say. To insert a symbol, go to the Insert tab and in the Symbols section select Symbol.
Almost any symbol can be found in the symbol table
To add it to the text, click on it.
Share our article on your social networks: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 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 will study fast and effective ways exporting 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.
How to Convert Excel File to CSV
If you want to export the Excel file to some other application, such as address book Outlook or database Access data, pre-convert 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 only save 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, 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 em dashes on the Excel sheet (transferred to Excel, for example, from word document when copying / pasting text) - such characters will also be shredded.
- 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. A significant 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:
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, such as Notepad++.
- 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.
Comment: 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.
Comment: 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) to Excel and then to Windows Explorer change the extension of the created file to .csv. Ready!
If you need a CSV file with a semicolon or semicolon as delimiter, replace all tabs with commas or semicolons respectively in Notepad or any other text editor of your choice (earlier in this article is 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 result in 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 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:
Advice: If the Excel file is relatively small, then to save time, you can transfer data from it to a Google spreadsheet using copy / paste.
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 symbols lost if saved directly from .xlsx in .csv, but remain if first .xlsx save as .xls, and then like .csv, like us .
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 provides access to more options when converting tables to CSV files (encoding, delimiters, and so on) 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 is to select the parameter values Encoding(Character sets) and 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!
csv ( Comma Separated Values- values separated by commas) is a text format designed to represent tabular data. For example, it is used to export data if their volumes are large enough. However, this often causes a problem: the created csv-file may not show the characters of the Russian alphabet or distort them. However, this problem is easily solved.
The essence of the problem is as follows. If Excel version, in which the user is working, is not English, then it may expect a different encoding of the CSV file created to export data. Namely: the encodings of the language to which the used version of Excel belongs. In this case, those characters that are not included in the standard ASCII set may be distorted.
The problem is solved in the following simple way:
1. Launch Excel.
2. Open the “Data → From text” page (in localized Excel, menu items may have different names).
3. Open your CSV file and in the “Text Wizard” window, set the required separator and encoding (use “UTF-8”).
Instructions for solving the same problem for Russian-speaking Excel 2007
1. Create a new document (book) in Excel
2. From the "Data / Get External Data" menu, select the "From Text" import. In the file selection window, select your CSV file. Excel will automatically launch the Text Wizard (Import). 
3. In the "File format" setting, select "Unicode (UTF-8)":
5. Then click the “Finish” button. At right choice Encoding problems with adequate display of Cyrillic characters should not be.
And one more important thing...
| Was our site useful to you? |
