current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 0 down vote favorite

I am having 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 exe file to convert shape file 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

share|improve this question
    
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 Barça Jan 31 at 18:27

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
Great! my problem solved. your code works. –  Farook Feb 1 at 9:41
    
I had edited my post. Since there is a new problem in running the batch file. –  Farook Feb 2 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 Feb 2 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 –  Farook Feb 2 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 Feb 2 at 9:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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