postgresql 将 csv 文件导入 psql db
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11363005/
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
Import csv file into psql db
提问by Sangeetha Narayana Moorthy
I want to import csv file to psql db...
我想将 csv 文件导入 psql db ...
I have searched and tried using copy, also referred https://www.commandprompt.com/ppbook/r23528and the syntax i used is:
我已经搜索并尝试使用 copy，也提到了https://www.commandprompt.com/ppbook/r23528，我使用的语法是：
copy location from 'file.csv' with delimiters '\t' NULL as 'NULL' CSV;
Note : My csv file is delimited by 'tab' .
注意：我的 csv 文件由 'tab' 分隔。
After executing the above cmd 1st time, it shows :
第一次执行上述cmd后，显示：
ERROR: syntax error at or near ""\t""
LINE 1: ...om 'file.csv' using delimiters "\t" ;
and for 2nd time,
第二次，
ERROR: syntax error at or near "copy"
LINE 2: copy location from 'file.csv' using d...
After Editing,
编辑后，
2nd time i tried to use like ,
第二次我尝试使用 like ，
copy location from 'file.csv' with delimiter E'\t' NULL as 'NULL' CSV;
ERROR: extra data after last expected column
CONTEXT: COPY location, line 1: "AD AD100 Canillo 42.5833 1.6667 6"
My table description is
我的表描述是
Table "public.location"
Column | Type | Modifiers
-------------+------------------------+-----------
countrycode | character varying(2) |
postalcode | character varying(20) |
place | character varying(100) |
state | character varying(20) |
country | character varying(100) |
country2 | character varying(20) |
community | character varying(100) |
community2 | character varying(20) |
latitude | double precision |
longitude | double precision |
accuracy | integer |
how to solve this?
如何解决这个问题？
Input:
输入：
AD AD100 Canillo 42.5833 1.6667 6
AD AD200 Encamp 42.5333 1.6333 6
AD AD300 Ordino 42.6 1.55 6
AD AD400 La Massana 42.5667 1.4833 6
AD AD500 Andorra la Vella 42.5 1.5 6
AD AD600 Sant Julià de Lòria 42.4667 1.5 6
AD AD700 Escaldes-Engordany 42.5 1.5667 6
AR 3636 "POZO CERCADO (EL CHORRO (F), DPTO. RIVADAVIA (S))" SALTA A -23.4933 -61.9267 3
AR 4123 LAS SALADAS SALTA A -25.7833 -64.5 4
AR 4126 LA MARAVILLA SALTA A -26.0833 -65.263 3
AR 4126 TALA SALTA A -26.1167 -65.2833 4
AR 4126 LA ASUNCION SALTA A -26.0833 -65.263 3
AR 4126 BRETE SALTA A -26.0667 -65.3667 4
AR 4126 EL SUNCHAL SALTA A -26.0833 -65.263 3
AR 4126 CEIBAL SALTA A -26.1 -65.0167 4
AR 4126 BARADERO SALTA A -26.0833 -65.263 3
AR 4126 CANDELARIA SALTA A -26.1 -65.1 4
AR 4126 ALEM SALTA A -26.0833 -65.263 3
AR 4126 EL BRETE SALTA A -26.0667 -65.3667 4
AR 4126 EL CUIBAL SALTA A -26.0833 -65.263 3
AR 4126 EL JARDIN SALTA A -26.0833 -65.3833 4
AR 4126 OVEJERO SALTA A -26.0833 -65.263 3
AR 4126 LOS MOGOTES SALTA A -26.0333 -65.2 4
AR 4126 "MIRAFLORES (TALA, DPTO. CANDELARIA)" SALTA A -26.0833 -65.263 3
This is some eg for input...
这是一些例如用于输入...
when i open this file in ms xcel sheet, with delimiter tab , it seperates the data correctly to the relevent coloumn..
当我在 ms xcel 表中打开此文件时，使用分隔符选项卡，它会将数据正确地分隔到相关列。
input files with tab and null values,
带有制表符和空值的输入文件，
AD\tAD100\tCanillo\t\n\t\n\t\n\t\n\t\n\t\n\t42.5833\t1.6667\t6
AD\tAD200\tEncamp\t\n\t\n\t\n\t\n\t\n\t\n\t42.5333\t1.6333\t6
AR\t3636\t"POZO CERCADO (EL CHORRO (F), DPTO. RIVADAVIA (S))"\tSALTA\tA\t\n\t\n\t\n\t\n\t-23.4933\t-61.9267\t3
回答by vyegorov
Per documentationon the COPYstatement, it should be WITH DELIMITERand not DELIMITERS.
根据该COPY语句的文档，它应该是WITH DELIMITER而不是DELIMITERS。
Also, COPYsyntax had changed and you're encouraged to use the following:
此外，COPY语法已更改，鼓励您使用以下内容：
COPY location FROM 'file.csv' WITH (FORMAT csv, DELIMITER '\t', NULL 'NULL');
EDIT:
编辑：
I've used the supplied information to test how COPYwill work on the presented data:
我已经使用提供的信息来测试如何COPY处理所提供的数据：
CREATE TABLE atest (
countrycode text CHECK (length(countrycode) <= 2),
postalcode text CHECK (length(postalcode) <=20),
place text CHECK (length(place) <= 100),
state text CHECK (length(state) <= 20),
country text CHECK (length(country) <= 100),
country2 text CHECK (length(country2) <= 20),
community text CHECK (length(community) <= 100),
community2 text CHECK (length(community2) <= 20),
latitude double precision,
longitude double precision,
accuracy integer);
I've used the above mentioned command (adopted a bit):
我已经使用了上面提到的命令（采用了一点）：
COPY atest FROM '/Users/viy/atest2.csv'
WITH (FORMAT csv, DELIMITER E'\t', NULL '\n');
And got the same error as described above. This is expected, 'cos there're too many empty fields. Let's look at the CSVdata and how it matches table definition (all \treplaced with new lines):
并得到与上述相同的错误。这是意料之中的，因为有太多的空字段。让我们看看CSV数据以及它如何匹配表定义（全部\t替换为新行）：
AD countrycode
AD100 postalcode
Canillo place
\n state
\n country
\n country2
\n community
\n community2
\n latitude
42.5833 longitude
1.6667 accuracy
6 <-- this one gives the error!
So, in order to get data loaded you have to review your source data and remove an extra empty field in the middle. Another way is to adjust table's definition to match you source data.
因此，为了加载数据，您必须查看源数据并删除中间的额外空字段。另一种方法是调整表的定义以匹配您的源数据。
