尝试将 csv 文件导入 postgresql 时出现“最后一个预期列之后的额外数据”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26701735/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"extra data after last expected column" while trying to import a csv file into postgresql
提问by Frederic Le Feurmou
I try to copy the content of a CSV file into my postgresql db and I get this error "extra data after last expected column".
我尝试将 CSV 文件的内容复制到我的 postgresql 数据库中，但我收到此错误“最后一个预期列之后的额外数据”。
The content of my CSV is
我的 CSV 的内容是
agency_id,agency_name,agency_url,agency_timezone,agency_lang,agency_phone
100,RATP (100),http://www.ratp.fr/,CET,,
and my postgresql command is
我的 postgresql 命令是
COPY agency (agency_name, agency_url, agency_timezone) FROM 'myFile.txt' CSV HEADER DELIMITER ',';
Here is my table
这是我的桌子
CREATE TABLE agency (
agency_id character varying,
agency_name character varying NOT NULL,
agency_url character varying NOT NULL,
agency_timezone character varying NOT NULL,
agency_lang character varying,
agency_phone character varying,
agency_fare_url character varying
);
Column | Type | Modifiers
-----------------+-------------------+-----------
agency_id | character varying |
agency_name | character varying | not null
agency_url | character varying | not null
agency_timezone | character varying | not null
agency_lang | character varying |
agency_phone | character varying |
agency_fare_url | character varying |
采纳答案by Mladen Uzelac
Now you have 7 fields.
现在您有 7 个字段。
You need to map those 6 fields from the CSV into 6 fields into the table.
您需要将 CSV 中的这 6 个字段映射到表中的 6 个字段。
You cannot map only 3 fields from csv when you have it 6 like you do in:
当您拥有 6 个 csv 字段时，您不能仅从 csv 映射 3 个字段：
\COPY agency (agency_name, agency_url, agency_timezone) FROM 'myFile.txt' CSV HEADER DELIMITER ',';
All fields from the csv file need to to be mapped in the copy from command.
csv 文件中的所有字段都需要映射到 copy from 命令中。
And since you defined csv ,delimiter is default, you don't need to put it.
并且由于您定义的 csv,分隔符是默认值，因此您不需要放置它。
回答by shacker
Not sure this counts as an answer, but I just hit this with a bunch of CSV files, and found that simply opening them in Excel and re-saving them with no changes made the error go away. IOTW there is possibly some incorrect formatting in the source file that Excel is able to clean up automatically.
不确定这算不算一个答案，但我只是用一堆 CSV 文件点击了这个，发现只需在 Excel 中打开它们并重新保存它们而不做任何更改，错误就会消失。IOTW 源文件中可能存在一些格式不正确的格式，Excel 能够自动清理。
回答by Mladen Uzelac
I tried your example and it works fine but ....
我试过你的例子，它工作正常，但是......
your command from the psql command line is missing \
缺少来自 psql 命令行的命令 \
database=# \COPY agency FROM 'myFile.txt' CSV HEADER DELIMITER ',';
And next time please include DDL
下次请包括 DDL
I created DDLfrom the csv headers
我DDL从 csv 标题创建
