[GIS] Loading multiple CSV files into PostgreSQL Table

csvpostgresql

I have multiple CSV files. Those CSV files are the log files created as a result of running a Windows batch file. The location of these CSV files lies on the server and I want all these CSV files to copy from the server to a particular table (say import_error_table, which I had already created in PostgreSQL).

How to accomplish this using PostgreSQL?

I tried running a Windows batch file which is shown below.

enter image description here

And in the above snapshot, there is an exe file to convert a shapefile to pgsql. I would like to know is there any specific exe for converting CSV to pgsql.

I had run the above code as a batch file. The prep.sql file is created with (0KB size). But also schema was not created in the PostgreSQL.

for %%f in (*.csv) do \\192.158.5.170\working\PostGIS\psql.exe -p -k -s 32643 %%f CSV_Logs.%%~nf > prep_%%~nf.sql
set PGPASSWORD=rpc123
for %%f in (prep_*.sql) do \\192.158.5.170\working\PostGIS\psql -h 192.158.5.170  -p 5432 -d NPCL_test -U postgres -f %%f

The result of which is prep.sql files created but schema was not created in PostgreSQL.

enter image description here

Best Answer

Here are two examples of how this can be done:

Just put the batchfile in the same folder where the csv-files are:

for copy from local machine to local database:

for %%f in (*.csv)  do psql -d your_database_name -h localhost -U postgres -p 5432 -c "\COPY public.yourtablename FROM '%%~dpnxf' DELIMITER ',' CSV;"
pause

You can also run the batchfile on your computer and send the content of the CSV-Files to the remote database. Just put the csv-files on your local computer and the batchfile in the same folder. Content of the batchfile:

@echo off
setlocal
set PGPASSWORD=yourpassword



for %%f in (*.csv)  do psql -d your_database -h your_server_ip -U postgres -p 5432 -c "\COPY public.yourtablename FROM '%%~dpnxf' DELIMITER ',' CSV;"

pause
endlocal

Just tested it on my machine and works both: local PostgreSQL on Windows7 and remote PostgreSQL on Ubuntu Linux.

PS: Using the \COPY instead of just COPY is important if you want to read the local csv's and copy them to the remote database.

Related Question