I have a file like (CSV file):
value1|value2|value2....
value1|value2|value2....
value1|value2|value2....
value1|value2|value2....
and would like to load these data into a postgresql table.
This question is related to
database
postgresql
csv
There's Pgloader that uses the aforementioned COPY
command and which can load data from csv (and MySQL, SQLite and dBase). It's also using separate threads for reading and copying data, so it's quite fast (interestingly enough, it got written from Python to Common Lisp and got a 20 to 30x speed gain, see blog post).
To load the csv file one needs to write a little configuration file, like
LOAD CSV
FROM 'path/to/file.csv' (x, y, a, b, c, d)
INTO postgresql:///pgloader?csv (a, b, d, c)
…
COPY description_f (id, name) FROM 'absolutepath\test.txt' WITH (FORMAT csv, HEADER true, DELIMITER ' ');
Example
COPY description_f (id, name) FROM 'D:\HIVEWORX\COMMON\TermServerAssets\Snomed2021\SnomedCT\Full\Terminology\sct2_Description_Full_INT_20210131.txt' WITH (FORMAT csv, HEADER true, DELIMITER ' ');
Check out the COPY command of Postgres:
Let consider that your data are in the file values.txt
and that you want to import them in the database table myTable
then the following query does the job
COPY myTable FROM 'value.txt' (DELIMITER('|'));
https://www.postgresql.org/docs/current/static/sql-copy.html
Source: Stackoverflow.com