在字符串值中导入CSV和逗号 [英] Importing CSV and commas in string values

问题描述

我使用postgres 8.4，目前正在尝试汇入一个不重要的CSV档案：

I use postgres 8.4 and currently trying to import a trivial CSV:

下面是一张表格：

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

这里是一个csv文件示例：

and here is a csv file sample:

"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 a rocket science and I would wonder if it is not possible to solve without reformatting the source csv.

任何想法？

PS：csv来自第三方，我无法更改格式。

PS: the csv comes from 3rd party, I cannot change the format.

PPS：是的，我知道我可以在导入之前预处理它（手动更改分隔符）。

PPS: yes, I understand I could pre-process it (change the delimiter manually) before I import it

strong>：

Final solution:

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

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

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

推荐答案

显然，您尝试以文本格式导入时，您有一个CSV文件。使用：

Clearly you have a CSV file while you try to import it as text format. Use:

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

CSV格式的默认分隔符是逗号（，）无论如何。 手册中的更多。语法是当前版本9.1。对于PostgreSQL 8.4：

The default delimiter for CSV format is the comma (,) anyway. More in the manual. Syntax is for current version 9.1. For PostgreSQL 8.4:

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

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