CODEWITHSUNDEEP
postgresqlcsvcopyimport-from-excel
user6308605
user6308605

Reputation: 731

Copy text file using postgres with custom delimiter by character size

I need to copy a text file which has confusing delimiter. I believe the delimiter is space. However, some of the column values are empty and I cannot differentiate which column which making it harder to load the data to database since the space is not indicating anything. Thus, when I try to COPY, the mapping is not right and I am getting ERROR: extra data after last expected column

I have tried to change the delimiter to comma and such, I am still getting the same error above. The below code can be used when I try to load some dummy data with proper delimiter.

COPY usm00070219(HEADREC_ID,YEAR,MONTH,DAY,HOUR,RELTIME,NUMLEV,P_SRC,NP_SRC,LAT,LON) FROM 'D:\....\USM00070219-data.txt' DELIMITER ' ';

This is example data:

enter image description here It should have 11 columns but the data on the first row is only 10 and it cannot identify the empty value column. The spacings are not helpful at all!

Is there any way I can separate the columns by character size as delimiter and force the data to be divided by the size given?

Upvotes: 1

Views: 1086

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247625

COPY is not made to handle fixed-width text files. I can think of two options:

  • Load the file as it is into a table with a single text column using COPY. Then use regexp_split_to_array to split it into its components and inser these into another table.

  • You can use file_fdw to create a foreign table with a single text column like above and operate on that. That saves loading the file into the database.

  • There is a foreign data wrapper for fixed-width text files that you can try.

Upvotes: 0

Related Questions