excel - 如何成功将 Excel CSV 导入 postgreSQL？

Question

我能够使用此方法导入两个表，但它不适用于第三个表。当我运行查询时，它不显示错误，只显示空白。

Postgresql COPY CSV ERROR: extra data after last expected column

我从上面得到了格式。

CREATE TABLE GrossNewHomeSales
        (ID varchar not null primary key, nhs_transaction_id varchar not null unique,   
         nhs_week_ending varchar,   nhs_sale_date varchar,  nhs_owner_name varchar,
         nhs_owner_age varchar, nhs_address varchar,    nhs_sf varchar, nhs_realtor_sale varchar,   
         nhs_realtor_name varchar,  nhs_realtor_office varchar, nhs_prev_zip varchar, nhs_employment varchar
);

COPY GrossNewHomeSales (ID, nhs_transaction_id, nhs_week_ending,    nhs_sale_date,
                    nhs_owner_name, nhs_owner_age,  nhs_address,    nhs_sf,
                    nhs_realtor_sale,   nhs_realtor_name,   nhs_realtor_office, nhs_prev_zip,
                    nhs_employment
)                    
FROM
'C:\Program Files\PostgreSQL\9.6\data\GrossNewHomeSales.csv'
WITH CSV HEADER DELIMITER ',';

Answer 1

PostgreSQL 可能期望该文件采用 UTF8 编码，但由于您是在 Windows 上创建该文件，所以它不是。您有 2 个选择:

1. 以UTF8编码保存文件。

   Should be as simple as opening the CSV in notepad (or equivalent) and saving with the proper (UTF8) encoding.

2. 在COPY 命令中指定正确的编码。

   Example: ENCODING 'WIN1257'

以下是 PostgreSQL 支持的列表: https://www.postgresql.org/docs/current/static/multibyte.html