Importing and maintaining multiple csv files into PostgreSQL

Question

I am new to using SQL, so please bear with me.

I need to import several hundred csv files into PostgreSQL. My web search has only indicated how to import many csv files into one table. However, most csv files have different column types (all have one line headers). Is it possible to somehow run a loop, and have each csv imported to a table with the same name as the csv? Creating each table manually and specifying columns is not an option. I know that COPY will not work as the table needs to already by specified.

Perhaps this is not feasible in PostgreSQL? I would like to accomplish this in pgAdmin III or the PSQL console, but I am open to other ideas (using something like R to change the csv to a format more easily entered into PostgreSQL?).

I am using PostgreSQL on a Windows 7 computer. It was requested that I use PostgreSQL, thus the focus of the question.

The desired result is a database full of tables, that I will then join with a spreadsheet that includes specific site data. Thanks!

It looks like something where you would want a separate process to iterate through the csv files, declaring tables with the appropriate names, then a process to iterate through and evaluate the types for each column that would be imported and insert them into the tables, then finally import the data. C# might be a good venue for this due to its excessive File.IO processing and compatibility with SQL. This won't be a short answer. — CSS
– CSS, Commented Sep 24, 2015 at 20:53
Extracting the column names from the header names will be possible and could be done in any scripting language. Determining the column types could be difficult, it either needs to rely on a naming convention, or you'll have to add them manually (eg enter them into a name->type dict) — wildplasser
– wildplasser, Commented Sep 24, 2015 at 22:00
I'd script this, though some of the ETL tools can probably help with this too. — Craig Ringer
– Craig Ringer, Commented Sep 25, 2015 at 0:24

Sean · Accepted Answer · 2015-10-07 23:37:32Z

1

Use pgfutter.

The general syntax looks like this:

   pgfutter csv

In order to run this on all csv files in a directory from Windows Command Prompt, navigate to the desired directory and enter:

   for %f in (*.csv) do pgfutter csv %f

Note that the path for the downloaded program must be added to the list of accepted paths for Environmental Variables.

EDIT: Here is the command line code for Linux users

Run it as

   pgfutter *.csv

Or if that won't do

   find -iname '*.csv' -exec pgfutter csv {} \;

edited Oct 7, 2015 at 23:37

Sean

1331 silver badge5 bronze badges

answered Sep 25, 2015 at 0:24

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Sean Over a year ago

Thank you Jakub for pointing out pgfutter. However, the syntax for running the program is incorrect. I will edit the answer and then mark it as the accepted one.

Jakub Kania Over a year ago

@Sean Yeah, I didn't notice you were using Windows not Linux, sorry.

Sean Over a year ago

No problem, I added your original code to the accepted answer for Linux users

TheDataGuy Over a year ago

pq: password authentication failed for user "postgres" I got this error.

Paul.j Over a year ago

I use OS X EI Capitan 10.11.6. I tried the code for Linux on my mac but it did not work. Any idea?

|

charlesreid1 · Accepted Answer · 2017-10-02 15:10:47Z

1

In the terminal use nano to make a file to loop through moving csv files under my directory to postgres DB

>nano run_pgfutter.sh

The content of run_pgfutter.sh:

#! /bin/bash
for i in /mypath/*.csv
do
   ./pgfutter csv ${i}
done

Then make the file executable:

chmod u+x run_pgfutter.sh

edited Oct 2, 2017 at 15:10

charlesreid1

4,9114 gold badges35 silver badges54 bronze badges

answered Apr 21, 2017 at 21:21

Paul.j

8342 gold badges9 silver badges19 bronze badges

Collectives™ on Stack Overflow

Importing and maintaining multiple csv files into PostgreSQL

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related