3

I have a folder with multiple csv files, they all have the same column attributes.

My goal is to make every csv file into a distinct postgresql table named as the file's name but as there are 1k+ of them it would be a pretty long process to do manually.

I've been trying to search a solution for the whole day but the closest I've came up to solving the problem was this code:

for filename in select pg_ls_dir2 ('/directory_name/') loop
    if (filename ~ '.csv$') THEN create table filename as fn
        copy '/fullpath/' || filename to table fn
    end if;
END loop;

the logic behind this code is to select every filename inside the folder, create a table named as the filename and import the content into said table.

The issue is that I have no idea how to actually put that in practice, for instance where should I execute this code since both for and pg_ls_dir2 are not SQL instructions?

amongosus
  • 45
  • 6
  • 1) A procedural function in Postgres. Since you want to reach out to the file system [plpython3u](https://www.postgresql.org/docs/current/plpython.html) comes to mind. 2) External code that does the same thing. – Adrian Klaver Jul 24 '22 at 18:04
  • I'd typically do this by writing a Perl script which outputs a list of `CREATE TABLE...` and `\copy` commands, then pipe that output into psql. But to start with, I'd just send the output to a file so it can be inspected before running. – jjanes Jul 25 '22 at 00:02

2 Answers2

2

If you use DBeaver, there is a recently-added feature in the software which fixes this exact issue. (On Windows) You have to right click the section "Tables" inside your schemas (not your target table!) and then just select "Import data" and you can select all the .csv files you want at the same time, creating a new table for each file as you mentioned.

RuggeroAnello
  • 36
  • 3
  • Thanks for that. An way to load the csv to preset tables with primary keys? Cause it seems we can't set the PKs after import https://stackoverflow.com/questions/946011/sqlite-add-primary-key – Lod Nov 08 '22 at 03:01
1

Normally, I don' t like giving the answer directly, but I think you will need to change a few things at least.

Depending on the example from here I prepared a small example using bash script. Let' s assume you are in the directory that your files are kept.

postgres@213b483d0f5c:/home$ ls -ltr
total 8
-rwxrwxrwx 1 root root 146 Jul 25 13:58 file1.csv
-rwxrwxrwx 1 root root 146 Jul 25 14:16 file2.csv

On the same directory you can run:

for i in `ls | grep csv`
do
export table_name=`echo $i | cut -d "." -f 1`;
psql -d test -c "CREATE TABLE $table_name(emp_id SERIAL, first_name VARCHAR(50), last_name VARCHAR(50), dob DATE, city VARCHAR(40), PRIMARY KEY(emp_id));";
psql -d test -c "\COPY $table_name(emp_id,first_name,last_name,dob,city) FROM './$i' DELIMITER ',' CSV HEADER;";
done
Umut TEKİN
  • 856
  • 1
  • 9
  • 19