pgAdmin4:导入CSV [英] pgAdmin4: Importing a CSV

问题描述

我正在尝试使用pgAdmin4导入CSV.我使用查询创建了表，

I am trying to import a CSV using pgAdmin4. I created the table using the query,

CREATE TABLE i210_2017_02_18
(
  PROBE_ID character varying(255),
  SAMPLE_DATE timestamp without time zone,
  LAT numeric,
  LON numeric,
  HEADING integer,
  SPEED integer,
  PROBE_DATA_PROVIDER character varying(255),
  SYSTEM_DATE timestamp without time zone
)

我的CSV读取文件的标题和第一行是...

The header and first line of my CSV read is...

PROBE_ID,SAMPLE_DATE,LAT,LON,HEADING,SPEED,PROBE_DATA_PROVIDER,SYSTEM_DATE
841625st,2017-02-18 00:58:19,34.11968,-117.80855,91.0,9.0,FLEET53,2017-02-18 00:58:58

当我尝试使用导入对话框时，该过程失败，并显示错误代码1:

When I try to use the import dialogue, the process fails with Error Code 1:

ERROR:  invalid input syntax for type timestamp: "SAMPLE_DATE"
CONTEXT:  COPY i210_2017_02_18, line 1, column sample_date: "SAMPLE_DATE"

对我来说，什么都没错-有任何想法吗?

Nothing seems wrong to me - any ideas?

推荐答案

根据您的表结构，此导入将在列HEADING和SPEED中失败，因为它们的值带有小数位并且您将它们声明为.删除小数点或将列类型更改为例如NUMERIC.

According to your table structure, this import will fail in the columns HEADING and SPEED, since their values have decimals and you declared them as INTEGER. Either remove the decimals or change the column type to e.g. NUMERIC.

话虽如此，只需从pgAdmin尝试一下(考虑到文件和数据库位于同一服务器中):

Having said that, just try this from pgAdmin (considering that file and database are in the same server):

COPY i210_2017_02_18 FROM '/home/jones/file.csv' CSV HEADER;

如果您要处理的是远程服务器，请使用

In case you're dealing with a remote server, try this using psql from your console:

$ cat file.csv | psql yourdb -c "COPY i210_2017_02_18 FROM STDIN CSV HEADER;"

您还可以检查答案.

如果您确实想使用我不建议使用的pgAdmin导入工具，只需选择Header选项和适当的Delimiter:

In case you really want to stick to the pgAdmin import tool, which I discourage, just select the Header option and the proper Delimiter:

这篇关于pgAdmin4:导入CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！