PostgreSQL Import – How to Import 100 CSV Files into PostgreSQL

csvimportpostgresql

I have a 100 csv files named sequentially 1,2,3….100.csv

I copy the csv files with this script: 

COPY location FROM 'C:\Program Files\PostgreSQL\9.5\data\ยด1.csv' DELIMITER ',';

I would to execute the COPY with a single script that covers all the files (1,2,3 …. 100.csv).

I use pgadminIII on MS Windows

Best Answer

You can use pgScript:

DECLARE @i, @filename; 
SET @i = 1;
WHILE @i <= 100 
BEGIN
    SET @filename = 'C:\\Program Files\\PostgreSQL\\9.5\\data\\' + CAST(@i AS STRING) + '.csv' ;
    COPY location FROM '@filename' DELIMITER ',' ; 
    SET @i = @i + 1;
END

NOTE: You shouldn't have your CSV files under the PostgreSQL data directory. This directory is for the database engine to use, not users. Mistakenly overwriting or deleting any of PostgreSQL files could have your database rendered useless.

You should keep the files somewhere under C:\Users\user\Documents\CSV\... or a similar location in "userland" and give proper permissions to that directory for the PostgreSQL process to read. For a standard install of this version, you normally need to give read permission to the LocalSystem account. Some installations can use the Postgres account).

NOTE 2: pgScript hasn't had much traction. It's not been ported to pgAdmin 4.

Related Question