30

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.

Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
walves
  • 2,026
  • 6
  • 28
  • 46

5 Answers5

26

The slightly modified version of COPY below worked better for me, where I specify the CSV format. This format treats backslash characters in text without any fuss. The default format is the somewhat quirky TEXT.

COPY myTable FROM '/path/to/file/on/server' ( FORMAT CSV, DELIMITER('|') );
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • 7
    If you're using `psql` and the file is in a location not accessible to the *server* (but only to the client), use the exact same command except start with `\copy`. – Wildcard Apr 21 '17 at 05:51
  • 2
    for those who want to skip the first line of the CSV file, use HEADER option, like in `COPY myTable FROM '/path/to/file/on/server' ( FORMAT CSV, DELIMITER('|') , HEADER);` – CodeBrew Aug 27 '17 at 20:13
  • @Wildcard Great comment. – Abdollah Apr 19 '21 at 13:34
18

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

CarlG
  • 590
  • 2
  • 9
Fopa Léon Constantin
  • 11,863
  • 8
  • 48
  • 82
  • 2
    It needs quotes around the filename/filepath. Could you please update your answer with `COPY myTable FROM 'value.txt' (DELIMITER('|'));` – PradeepKumbhar May 11 '16 at 06:39
6

Check out the COPY command of Postgres:

http://www.postgresql.org/docs/current/static/sql-copy.html

3

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)  
  …
Ehvince
  • 17,274
  • 7
  • 58
  • 79
0
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 ' ');
Mohsin Ejaz
  • 316
  • 3
  • 7