Postgres CSV import with JSON [til/data]

The problem: CSV files generally quoted using ” and delimited using , – both of which feature heavily in JSON strings.
So importing a CSV with JSON doesn’t fly.
One solution (it took me hours and hours to solve) is to use an alternative byte for quote and delimiter.
For example, quote using \x1 and delimiter using \x2. Inspecting the bytes for the source data should look a little like this if done right:

Now to import requires careful command-line-fu:
$ cat data.csv | psql postgres://localhost:5432/my_db -c “\copy quiz (id,title,description,\”maxPoints\”,source,attribution,lessons) FROM STDIN csv QUOTE E’\x1′ DELIMITER E’\x2′”

A few key points:

The mixed case column needs quoting (and I’m escaping the quotes)
I’m using hex representation of the byte values 1 and 2, so that’s \xDIGIT
For the postgres import to understand the escaped digit, I must use E before the string

Now I’m able to import JSON in a CSV file.


This content originally appeared on remy sharp's b:log and was authored by remy sharp's b:log

The problem: CSV files generally quoted using " and delimited using , - both of which feature heavily in JSON strings.

So importing a CSV with JSON doesn't fly.

One solution (it took me hours and hours to solve) is to use an alternative byte for quote and delimiter.

For example, quote using \x1 and delimiter using \x2. Inspecting the bytes for the source data should look a little like this if done right:

the 01 and 02 bytes between fields

Now to import requires careful command-line-fu:

$ cat data.csv  | psql postgres://localhost:5432/my_db -c "\copy quiz (id,title,description,\"maxPoints\",source,attribution,lessons) FROM STDIN csv QUOTE E'\x1' DELIMITER E'\x2'"

A few key points:

  1. The mixed case column needs quoting (and I'm escaping the quotes)
  2. I'm using hex representation of the byte values 1 and 2, so that's \xDIGIT
  3. For the postgres import to understand the escaped digit, I must use E before the string

Now I'm able to import JSON in a CSV file.

Originally published on Remy Sharp's b:log


This content originally appeared on remy sharp's b:log and was authored by remy sharp's b:log


Print Share Comment Cite Upload Translate Updates
APA

remy sharp's b:log | Sciencx (2021-03-09T12:53:55+00:00) Postgres CSV import with JSON [til/data]. Retrieved from https://www.scien.cx/2021/03/09/postgres-csv-import-with-json-til-data/

MLA
" » Postgres CSV import with JSON [til/data]." remy sharp's b:log | Sciencx - Tuesday March 9, 2021, https://www.scien.cx/2021/03/09/postgres-csv-import-with-json-til-data/
HARVARD
remy sharp's b:log | Sciencx Tuesday March 9, 2021 » Postgres CSV import with JSON [til/data]., viewed ,<https://www.scien.cx/2021/03/09/postgres-csv-import-with-json-til-data/>
VANCOUVER
remy sharp's b:log | Sciencx - » Postgres CSV import with JSON [til/data]. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2021/03/09/postgres-csv-import-with-json-til-data/
CHICAGO
" » Postgres CSV import with JSON [til/data]." remy sharp's b:log | Sciencx - Accessed . https://www.scien.cx/2021/03/09/postgres-csv-import-with-json-til-data/
IEEE
" » Postgres CSV import with JSON [til/data]." remy sharp's b:log | Sciencx [Online]. Available: https://www.scien.cx/2021/03/09/postgres-csv-import-with-json-til-data/. [Accessed: ]
rf:citation
» Postgres CSV import with JSON [til/data] | remy sharp's b:log | Sciencx | https://www.scien.cx/2021/03/09/postgres-csv-import-with-json-til-data/ |

Please log in to upload a file.



There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.

Related Posts