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



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



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2