1

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

  • psql already is an executable. When I do this I just wrap a bunch of calls to psql -c "copy some_table from some_csv csv" db_name in the shell. – John Powell Jan 31 '15 at 18:27
  • Answers below do work but assume you have the tables created already. Another tool which can a) create a CREATE TABLE expression based on a CSV and b) load the CSV into a table and create the table in the same expression is CSVKit - I have a tutorial here, and I think you might find a way to utilize the comand on multiple files using Python: github.com/dpsspatial/Installation-Instructions/blob/master/… – DPSSpatial Dec 3 '18 at 18:44
3

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.

  • Great! my problem solved. your code works. – User123 Feb 1 '15 at 9:41
  • I had edited my post. Since there is a new problem in running the batch file. – User123 Feb 2 '15 at 7:02
  • You don't have to create sql-files.all you need is adapt my batch file template so it fits to your server-ip, database name and so on.don't insert any other commands – Thomas B Feb 2 '15 at 7:54
  • Before loading my CSV files into a table. I need to create a table right for loading CSV files? That's what the above code snippet (prep.sql) does – User123 Feb 2 '15 at 9:02
  • well in you question you said "to a particular table (say import_error_table, which i had already created in postgreSQL"). You can't create a table using CSV. You have to manually create the table and its columns, because a csv does not contain information about which data type should be used for each column. – Thomas B Feb 2 '15 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.