TransWikia.com

How to import multiple CSV files to a postgres table using pgadmin or other method?

Database Administrators Asked by Chris Jenner on January 29, 2021

I’m looking to import a folder of over 100+ csv files into one table on PostgreSQL using pgadmin. I have the following script but is there a way to change the file path to all csvs?

COPY schema.table FROM ‘C:DocumentsData’ DELIMITER ‘,’ CSV HEADER;

2 Answers

Peter's answer is probably the way to go, but wanted to chime in with another approach using COPY FROM PROGRAM. Unfortunately I only have a linux env so I can't give you a command/statement that will work for your case, but hopefully this gives you the idea and/or is useful for someone else.

COPY FROM with a file can only support one file at a time, but being a bit clever with a COPY FROM PROGRAM is one way of importing multiple files with one COPY. Here's a trivial example:

sh-5.0$ mkdir /tmp/so_267604/
sh-5.0$ cd /tmp/so_267604/
sh-5.0$ echo -e "cola,colbnaval1,bval1naval2,bval2" > first.csv
sh-5.0$ cat first.csv 
cola,colb
aval1,bval1
aval2,bval2
sh-5.0$ echo -e "cola,colbnaval3,bval3naval4,bval4" > second.csv
sh-5.0$ tail --quiet -n +2 *.csv
aval1,bval1
aval2,bval2
aval3,bval3
aval4,bval4
sh-5.0$ psql -X testdb
testdb=# create table tt(cola text, colb text);
CREATE TABLE
testdb=# copy tt from program 'tail --quiet -n +2 /tmp/so_267604/*.csv' csv;
COPY 4
testdb=# select * from tt;
 cola  | colb  
-------+-------
 aval1 | bval1
 aval2 | bval2
 aval3 | bval3
 aval4 | bval4
(4 rows)

Note that there's no HEADER modifier on the copy statement; this because -n +2 on the tail command will always start at line #2 of each file it outputs. This should not break anything; the HEADER directive only ever tells pg to skip reading one line, the actual data is always imported using column order specified in the COPY statement, or the table's column order if the COPY did not specify it.

Answered by AdamKG on January 29, 2021

  1. Use your computer's command-line environment to list the names of all the files in your directory
  2. Use the list of files to create the commands you want
  3. Manually tweak your commands line-by-line or change the formula/process you used in step 2 until you get the output you want.

Gl;hf ;)

Answered by Peter Vandivier on January 29, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP