1

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.

1 Answer 1

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.