Postgresql
何時將 csv 數據文件導入 postgresql 時未終止的 CSV 引用欄位
創建一個表:
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) 未出現在您的數據中。