Fix Excel Open UTF-8 encoded CSV file garbled problem

Source: Internet
Author: User

Fix Excel Open UTF-8 encoded CSV file garbled problem


Referenced from: https://en.wikipedia.org/wiki/Comma-separated_values


CSV formats is not limited to a particular character set. They work just as well with Unicode character sets (such as UTF-8 or UTF-16) as with ASCII (although particular progr AMS/Support CSV may have their own limitations). CSV files normally would even survive naive translation from one character set to another (unlike nearly all proprietary DA TA formats). CSV does not, however, provide any-to-indicate-character set is-use, so-must be communicated separately, or determined at the receiving end (if possible).


Databases that include multiple relations cannot is exported as a single CSV file.


CSV is a flat file that is encoded in a variety of ways. For example, MongoDB stores data in the UTF-8 format, and when exported to a CSV file using Mongoexport, the CSV encoding is UTF-8. (The exported CSV file can be opened with UltraEdit to verify the encoding format)


Garbled characters appear when you open UTF-8 encoded CSV files directly using Excel.


Microsoft Excel would open. csv files, but depending on the system ' s regional settings, it may expect a semicolon as a Separator instead of a comma, since in some languages the comma is used as the decimal separator. It also applies some magic, such as reformatting what looks like numbers, eliminating leading + or 0, which break s phone numbers, or a leading = makes the cell a formula, where function names must is in the opener ' s local language. Also, many regional versions of Excel is not being able to deal with Unicode in CSV. One simple solution when encountering such difficulties are to change the filename extension from. csv to. txt; Then opening the file from a already running Excel instance with the "Open" command, where the user can manually specify The delimiters, encoding, format of columns, etc.


Excel opens a CSV file that relies on the system locale. Because of the impact of the locale and the different implementations of the CSV file, Excel needs to open the CSV file in the correct way. Let's take the example of a CSV file encoded in UTF-8 format, derived from MongoDB:


1. Open the Excel application.


2. Click "Self-text" on the "Data" menu:

650) this.width=650; "title=" clip_image001 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "Alt=" Clip_ image001 "src=" http://s3.51cto.com/wyfs02/M02/87/0F/wKiom1fSXr_Sc4yxAABCTPAGhPY174.png "border=" 0 "height=" 230 "/ >


3. Select the CSV file, click "Import" and the "Text Import Wizard" dialog box appears.

650) this.width=650; "title=" clip_image003 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "Alt=" Clip_ image003 "src=" http://s3.51cto.com/wyfs02/M00/87/0F/wKiom1fSXsCx6U08AAElrKHa3iI901.jpg "border=" 0 "height=" 513 "/ >

From the default settings above, I suspect that Excel opens CSV by default with "Windows (ANSI)", encoding is not recognized.


4. File type select "Split symbol", File original format select "65001:unicode (UTF-8)", tick "data contains title", click Next.

650) this.width=650; "title=" clip_image005 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "Alt=" Clip_ image005 "src=" http://s3.51cto.com/wyfs02/M00/87/0F/wKiom1fSXsKiq3NdAAEoegiIlWE403.jpg "border=" 0 "height=" 513 "/ >


5. Remove the "tab" symbol and tick "comma" and click "Next".

650) this.width=650; "title=" clip_image007 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "Alt=" Clip_ image007 "src=" http://s3.51cto.com/wyfs02/M00/87/0D/wKioL1fSXsOCRqFRAAD7XS5ne5M725.jpg "border=" 0 "height=" 513 "/ >


6. The column data format is selected as required, here is the general, click "Done".

650) this.width=650; "title=" clip_image009 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "Alt=" Clip_ image009 "src=" http://s3.51cto.com/wyfs02/M01/87/0F/wKiom1fSXsWSLBLrAAEFVkPFHo0978.jpg "border=" 0 "height=" 513 "/ >


7. The Import Data dialog box appears, we select "Existing Worksheet" and click "OK".

650) this.width=650; "title=" clip_image010 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "alt=" clip_image010 "src=" Http://s3.51cto.com/wyfs02/M02/87/0F/wKiom1fSXsWiSfgGAAAyz9v0H8k099.png "border=" 0 "height=" 345 "/>


8. The data is displayed as normal.

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1851117

Fix Excel Open UTF-8 encoded CSV file garbled problem

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.