My CSV file with double quotes enclosed fields - numeric value ' "12131" ' not recognized

Question

I staged a csv file has all the fields enclosed in double quotes (" ") and comma separated and rows are separated by newline character. The value in the enclosed fields also contains newline characters (\n).

I am using the default FILE FORMAT = CSV. When using COPY INTO I am seeing a column mismatch error in this case.

I solved this first error by adding the file type to specify the FIELD_OPTIONALLY_ENCLOSED_BY = attribute in the SQL below.

However when I try to import NUMBER values from csv file, I already used FIELD_OPTIONALLY_ENCLOSED_BY='"'; but it's not working. I get "Numeric value '"3922000"' is not recognized" error.

A sample of my .csv file looks like this:

"3922000","14733370","57256","2","3","2","2","2019-05-23 14:14:44",",00000000",",00000000",",00000000",",00000000","1000,00000000","1000,00000000","1317,50400000","1166,50000000",",00000000",",00000000",",00000000",",00000000",",00000000",",00000000",",00000000",",00000000",",00000000",",00000000",",00000000",",00000000",",00000000",",00000000",",00000000",",00000000","","tcllVeEFPD"

My COPY INTO statement is below:

COPY INTO '..'

   FROM '...'

FILE_FORMAT = (TYPE = CSV 

        STRIP_NULL_VALUES = TRUE 

        FIELD_DELIMITER = ',' 

        SKIP_HEADER = 1  

        error_on_column_count_mismatch=false 

        FIELD_OPTIONALLY_ENCLOSED_BY = '"'

       )

ON_ERROR = "ABORT_STATEMENT";

I get a feeling that NUMBER is interpreted as STRING.

Does anyone have solution for that one?

Chris Chris · Accepted Answer · 2019-12-18T22:25:03

Try using a subquery in the FROM clause of the COPY command where each column is listed out and cast the appropriate columns.

Ex.

COPY INTO '...'
FROM (
    SELECT $1::INTEGER
           $2::FLOAT
           ...
)
FILE_FORMAT = (TYPE = CSV 
        STRIP_NULL_VALUES = TRUE 
        FIELD_DELIMITER = ',' 
        SKIP_HEADER = 1  
        error_on_column_count_mismatch=false 
        FIELD_OPTIONALLY_ENCLOSED_BY = '"'
       )
ON_ERROR = "ABORT_STATEMENT";

My CSV file with double quotes enclosed fields - numeric value ' "12131" ' not recognized

1 Answers