CODEWITHSUNDEEP
sqlpostgresqlcsvcopy
Sherin Green
Sherin Green

Reputation: 358

How to remove double quotes from column field value of csv file,that is copy to a table in postgreSQL

I used \copy parts from home/sherin_ag/parts.csv with (format csv, header false) this command on bash the problem is on of the row in csv column value comtain double quotes between the characters

example: "35387","20190912","X99","1/4" KEYWAY","KEYWAY","1","FORD"

The problem is "1/4" KEYWAY" it contain a double quotes after 1/4 so i got an error like

ERROR: unterminated CSV quoted field

Upvotes: 0

Views: 1036

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

This CSV is invalid, since it has an unescaped " in a record with the same character as quote character. As @404 said, the generated csv file is the problem.

That being said, you can correct the file beforehand using sed from your console, e.g:

 cat vasher_dummy.txt | sed -r 's/\" /\\"" /g' | psql db -c "COPY t FROM STDIN CSV"

This command will search for every occurrence of "_ ( _ meaning space) in your file and will replace it with an escaped "

SELECT * FROM t;
   a   |    b     |  c  |      d       |   e    | f |  g   
-------+----------+-----+--------------+--------+---+------
 35387 | 20190912 | X99 | 1/4\" KEYWAY | KEYWAY | 1 | FORD
(1 Zeile)

If it isn't the only problem you have in your file, you'll have to change the sed string to make further corrections. Check the sed documentation.. it's really powerful.

Update based on the comments: the OP needs a counter for each line and needs to filter out a few lines.

nl -ba -nln -s, < vasher_dummy.txt | sed -r 's/\" /\ /g' | grep 'SVCPTS' | psql db -c "COPY t FROM STDIN CSV"

Upvotes: 1

Related Questions