Unterminated CSV quoted field when to import csv data file into postgresql

copycsvimportpostgresql

Create a table :

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)
);

I want to import the sample.txt data into num table:

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     

The command to import the data in sample.txt into the table:

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

It encounter an error:

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..."

Please fix it without editing the primitive data.

Best Answer

" is the default quotation character in a CSV file. It quotes fields that contains delimiters and cannot be unbalanced

Use the QUOTE option to specify a different quote character:

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

This assumes that the BEL character (ASCII 7) does not appear in your data.