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.
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.
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:
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:
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.