Importing CSV file into PostgreSQL

Question

Using MySQL Administrator GUI tool I have exported some data tables retrieved from an sql dumpfile to csv files.

I then tried to import these CSV files into a PostgreSQL database using the postgres COPY command. I've tried entering

COPY articles FROM '[insert .csv dir here]' DELIMITERS ',' CSV;

and also the same command without the delimiters part.

I get an error saying

ERROR: invalid input syntax for integer: "id"
CONTEXT: COPY articles, line 1, column id: "id"

In conclusion my question is what are some thoughts and solutions to this problem? Could it possibly be something to do with the way I created the csv files? or have I made a rookie mistake elsewhere?

I'm just wondering why you tagged your question as python? (am I missing something really obvious?) — brice
– brice, Commented Jul 19, 2010 at 8:21
Write some scripts to populate the table, by reading your CSV file. — thegeek
– thegeek, Commented Jul 19, 2010 at 8:52
Well I was open to some python suggestions since I did remove the non-ascii chars from the csv files using python, but yeah Gavin seemed to have solved my problem. Thanks for the input though. — user395558
– user395558, Commented Jul 20, 2010 at 1:17

Gavin · Accepted Answer · 2010-07-19 10:34:20Z

4

If you have header columns just add the header qualifier to the copy statement as per documentation to skip that line http://www.postgresql.org/docs/8.4/static/sql-copy.html

answered Jul 19, 2010 at 10:34

Gavin

6,5603 gold badges28 silver badges25 bronze badges

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

3 Comments

rfusca Over a year ago

COPY articles FROM '[insert .csv dir here]' CSV WITH HEADER;

user395558 Over a year ago

Ah yeah awesome. Thanks so much this worked well. lol although it did produce a lot of unwanted white space in the data put into the table, but I guess thats a new problem I have to try and solve.

user395558 Over a year ago

Oh disregard what I said about the post-problem encountered. I didnt realise that the data is actually squished and displays wierdly when viewing it in the terminal.

Collectives™ on Stack Overflow

Importing CSV file into PostgreSQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related