Importing non formatted CSV into a Postgres Database

Question

I am trying to import a csv into postgres.

I have my tables set up and my csv file is formatted in a way such that it is clear where the data I need is, but it is not formatted in the same way as the tables.

For a simple example: I have

Table1 with field1 field2 and field3

Table2 with field1 and field2

my csv has value1,value2,value3,value4

I want to load value1 into Table1 field2, value2 into Table2 field1, and value3 into Table1 field3.

I was wondering if there exists any tool, within postgres or without for handling the mapping and import, or is this something I would need to write a separate script or program for. If the second, any suggestions would be welcome.

Thank you very much!

Jeremy · Accepted Answer · 2020-02-01 10:25:27Z

1

Create a temporary table with the same structure as your CSV, load the CSV data into this table, and then insert data into table1 and table2 from the temporary table.

answered Feb 1, 2020 at 10:25

Jeremy

6,77322 silver badges21 bronze badges

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

2 Comments

Belayer Over a year ago

Climbing up on soapbox. You say "it is clear where the data I need is". This is completely false. It is clear to you as you created it knowing the data source and destination. I on the other hand would never have concluded to put value1 in field2, value2 in field1, totally ignore value4 and ignore table2 all together. Further software is always completely stupid you must specifically tell it. This is why the suggestion by @Jeremy should be standard practice to always follow. External files,esp a CVS from Excel, are notoriously very bad at format and consistency. OK, getting off soapbox.

wlodarmt Over a year ago

Yes, thank you. That is part of the reason I was asking. I wanted to know if there was a standard accepted procedure for handling this situation.

Collectives™ on Stack Overflow

Importing non formatted CSV into a Postgres Database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related