How to auto populate data from CSV files to PostgreSQL?

Question

i`m using this SQL query to populate a database:

CREATE TEMP TABLE tmp_x AS SELECT * FROM testcsv LIMIT 0; -- but see below

COPY tmp_x FROM 'D:\water_utility.csv' (header, FORMAT CSV, ENCODING 'WIN1251');

insert into testcsv select * from (
select x.* 
from tmp_x x
left outer join testcsv t on t. timestamp = x. timestamp
where t. timestamp is null
) as missing;

DROP TABLE tmp_x; -- else it is dropped at end of session automatically

Can you help me by giving advice how to get all files from folder named like this and populate the table with data:

sample_1.csv sample_2.csv ... sample_12.csv

The numbers are the months that are created the files. How to create auto populate script?

Vao Tsun · Accepted Answer · 2017-08-24 14:21:46Z

1

try smth like:

do
$$
begin
 execute format ($f$COPY tmp_x FROM 'D:\water_utility%s.csv' (header, FORMAT CSV, ENCODING 'WIN1251');$f$,extract(month from now()));
end;
$$
;

instead of your copy line.

update it is often called dynamic sql - I format the statement before calling it. here I append the current month number to the name of the file, so the line execute would be:

COPY tmp_x FROM 'D:\water_utility8.csv' (header, FORMAT CSV, ENCODING 'WIN1251');

because August is 8th month (which I get with extract(month from now()))

edited Aug 24, 2017 at 14:21

answered Aug 24, 2017 at 13:58

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

7 Comments

Hristian Yordanov Over a year ago

Can you explain the code i cant understand it on 100%, please?

Hristian Yordanov Over a year ago

Thanks for the great help! Cheers!

Hristian Yordanov Over a year ago

Hello again. Can you help me again. I want to the same and for the year. How to accomplish this?

Vao Tsun Over a year ago

hi, please post a new question instead

Hristian Yordanov Over a year ago

i think i will have a lot of negative votes because of duplicate the question.

|

Collectives™ on Stack Overflow

How to auto populate data from CSV files to PostgreSQL?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related