Postgresql

何時將 csv 數據文件導入 postgresql 時未終止的 CSV 引用欄位

  • January 27, 2022

創建一個表:

CREATE TABLE num (
   id serial NOT NULL ,
   adsh VARCHAR(20) NOT NULL,
   tag VARCHAR(256) NOT NULL,
   version VARCHAR(20) NOT NULL,
   coreg VARCHAR(256),
   ddate date NOT NULL,
   qtrs DECIMAL(8) NOT NULL,
   uom VARCHAR(20),
   value DECIMAL(28,4),
   footnote VARCHAR(512)
);

我想將sample.txt數據導入num表中:

adsh    tag version coreg   ddate   qtrs    uom value   footnote
0001213900-20-033598    DueToAsiyaCommunicationsSapiDeC.v.Current   0001213900-20-033598        20191231    0   USD     
0001213900-20-033598    DueToDinarZuzLLC    0001213900-20-033598        20200630    0   USD 178000.0000 Due to the April 6, 2020 180 days Loan Agreement with the Company to borrow up to $250 at an annual interest rate of nine percent (9.0%) ("the second "Dinar Zuz Note").
0001213900-20-033598    DueToNextCalaCurrent    0001213900-20-033598        20181231    0   USD -14000.0000 
0001213900-20-033598    DueToNextCalaCurrent    0001213900-20-033598        20191231    0   USD     

將數據sample.txt導入表中的命令:

COPY num(adsh,tag,version,coreg,ddate,qtrs,uom,value,footnote)
FROM 'sample.txt' 
DELIMITER E'\t'
CSV HEADER;

它遇到一個錯誤:

ERROR:  unterminated CSV quoted field
CONTEXT:  COPY num, line 6: "0001213900-20-033598   DueToDinarZuzLLC    0001213900-20-033598        20200630    0   USD 178000.0000 Due to the Ap..."

請在不編輯原始數據的情況下修復它。

"是 CSV 文件中的預設引號字元。它引用包含分隔符且不能不平衡的欄位

使用該QUOTE選項指定不同的引號字元:

COPY num(adsh,tag,version,coreg,ddate,qtrs,uom,value,footnote)
FROM 'sample.txt'
(QUOTE E'\u0007', DELIMITER E'\t', FORMAT 'csv', HEADER);

這假定 BEL 字元 (ASCII 7) 未出現在您的數據中。

引用自:https://dba.stackexchange.com/questions/306662