导入字符串值中带逗号的CSV [英] Importing CSV with commas in string values

问题描述

我正在尝试将简单的CSV导入Postgres 8.4数据库:

I am trying to import a trivial CSV to Postgres 8.4 database:

这是一张桌子:

CREATE TABLE public.sample (
  a VARCHAR, 
  b VARCHAR
) WITHOUT OIDS;

这是CSV文件示例:

"foo","bar, baz"

查询:

COPY sample FROM '/tmp/sample.csv' USING DELIMITERS ',';

引发异常:

ERROR:  extra data after last expected column
CONTEXT:  COPY sample, line 1: ""foo","bar, baz""

但是，好吧，CSV解析不是火箭科学，我想知道如果不重新格式化源CSV文件就无法解决该问题.

But, well, CSV parsing is not rocket science and I wonder if it is not possible to solve without reformatting the source CSV file.

输入来自第三方，我无法更改格式.
(我知道我可以在导入定界符之前进行预处理，以更改定界符.)

Input comes from a 3rd party, I cannot change the format.
(I understand I could pre-process to change the delimiter before I import it.)

最终解决方案:

COPY sample FROM '/tmp/sample.csv' WITH DELIMITER ',' CSV;

最初取自 https://stackoverflow.com/a/9682174/251311 ，文档页面为 http://www.postgresql.org/docs/8.4/static/sql-copy.html

Originally taken from https://stackoverflow.com/a/9682174/251311, and documentation page is http://www.postgresql.org/docs/8.4/static/sql-copy.html

推荐答案

很明显，当您尝试将其导入为文本格式时，您有一个CSV文件.对于Postgres 9.1或更高版本，请使用:

Clearly, you have a CSV file while you try to import it as text format. For Postgres 9.1 or newer, use:

COPY sample FROM '/tmp/sample.csv' (FORMAT csv);

CSV格式的默认分隔符还是逗号(，).手册中的更多内容.
对于PostgreSQL 8.4或更早的版本:

The default delimiter for CSV format is the comma (,) anyway. More in the manual.
For PostgreSQL 8.4 or older:

COPY sample FROM '/tmp/sample.csv' CSV;

这篇关于导入字符串值中带逗号的CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！