PostgreSQL Excel contains Chinese characters in the data import method

1.excel file Processing

Delete header row, save as comma delimiter csv file

2. file format processing Notepad + +

1). convert Files to UNIX format

2). format conversion to UTF8 no BOM

3. Uploading to the database import

Precautions:

1). Character set configuration, operating system character set Lang=en_us. UTF-8

Database Character Set UTF-8

uccc_im=# \l

List of databases

Name | Owner |   Encoding |    Collate |   Ctype | Access Privileges

-----------+----------+----------+-------------+-------------+-----------------------

Postgres | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 |

Template0 | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 | =c/postgres +

|          |             |             | | Postgres=ctc/postgres

template1 | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 | =c/postgres +

|          |             |             | | Postgres=ctc/postgres

Import_testdb | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 |

(5 rows)

Import_testdb =#

Import_testdb =# show server_encoding;

Server_encoding

-----------------

UTF8

(1 row)

Import_testdb =# show client_encoding;

Client_encoding

-----------------

UTF8

(1 row)

Import_testdb =#

2). CSV Import Database

Import_testdb =#

Import_testdb =# COPY zh_tbl from '/var/lib/pgsql/zh_data_2016.01.21.csv ' DELIMITER ', ';

3) Client Chinese display

SCRT client Character Set default modified to UTF8, solve Chinese characters garbled problem

Pgadmin client direct query without garbled display.

This article is from the "Yiyi" blog, make sure to keep this source http://heyiyi.blog.51cto.com/205455/1904281

PostgreSQL Excel contains Chinese characters in the data import method