Fix Excel Open UTF-8 encoded CSV file garbled problem

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