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

Question

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:\Documents\Data’ DELIMITER ‘,’ CSV HEADER;

Answer 1

1. Use your computer's command-line environment to list the names of all the files in your directory
   • On windows, use cmd + dir or PowerShell+gci
   • On macOS, probably terminal+ls
   • This might also be a good opportunity to familiarise yourself with psql and the meta-commands which you could use for this purpose
2. Use the list of files to create the commands you want
   • Some people use excel for this
   • I prefer multi-line editing in something like VSCode
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 ;)

Answer 2

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,colb\naval1,bval1\naval2,bval2" > first.csv
sh-5.0$ cat first.csv 
cola,colb
aval1,bval1
aval2,bval2
sh-5.0$ echo -e "cola,colb\naval3,bval3\naval4,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.

Answer 3

I solved the problem by merging all csv files into one with the shell script:

for i in ./*.csv
do
    cat $i >> all.csv
done

Then I imported the all.csv file. Instead of pgAdmin or COPY statement I just used IntelliJ: right click on the table and select Import data. This is the most convenient way that I found