postgresql import csv columns change dynamically

Question

Database: PostgreSQL

I need function in PL/pgSQL to load data into a table from CSV file automatically. Important: columns of CSV file and columns of a target table must declared.

some_function(data_file_name, table_name, columns_from, columns_to)

EXAMPLE:

CSV file has columns: A, B, C, D

target table has columns: one, two

some_function('file.csv', 'table1', array['A', 'B'], array['one', 'two'])

Array definition can be changed, I don't know how to pass columns if the other way.

create or replace function load_csv_procedure
(
csv_path text,
target_table text,
columns_from text[],
columns_to text[]
)

returns void as $$

declare

target_table text;
col_count integer;
iter integer; 
col text;
col_first text; 

begin
set schema 'public';

create table temp_table ();
col_count := 6; 

-- add just enough number of columns
for iter in 1..col_count
loop
    execute format('alter table temp_table add column col_%s text;', 
iter);
end loop;

-- copy the data from csv file
execute format('copy temp_table from %L with delimiter '','' quote 
''"'' csv ', csv_path);

iter := 1;
col_first := (select col_1 from temp_table limit 1);

for col in execute format('select 
unnest(string_to_array(trim(temp_table::text, ''()''), '','')) from 
temp_table where col_1 = %L', col_first)
loop
    execute format('alter table temp_table rename column col_%s to 
%s', iter, col);
    iter := iter + 1;
end loop;

execute format('delete from temp_table where %s = %L', col_first, 
col_first);

--EXECUTE format('INSERT INTO %I (' || columns_to::text || ' ) VALUES 
( select (' || columns_from::text || ' ) from temp_table);'::text, 
target_table) using columns_to, columns_from;
--
EXECUTE format('INSERT INTO %I (''name'', code ) VALUES (select 
DepartmentName, DepartmentCode from temp_table);'::text, 
target_table); -- using columns_to, columns_from;

drop table temp_table;

end;

$$ language plpgsql;

did you try to write anything? this is not a sample code site — JustMe
– JustMe, Commented Oct 26, 2017 at 10:33
Not the solution to your problem but it would be better to create yout temp_table as a temp table: CREATE TEMP TABLE temp_table () ON COMMIT DROP; this will make it local to the session and it will garantee cleanup at end of transaction. — Eelke
– Eelke, Commented Oct 26, 2017 at 10:55
sounds doable - where you stuck? please ask questions on some distinct problem - don't ask to write the code for you — Vao Tsun
– Vao Tsun, Commented Oct 26, 2017 at 12:56

h3llca7 · Accepted Answer · 2017-10-26 13:12:32Z

I made it work :)

create or replace function csv_to_table (in target_table text, in 
csv_path text, in col_count integer, in columns_from text[], in 
columns_to text[])

returns void as $$

declare 

iter integer; --dummy integer to iterate with
col text; --dummy variable to iterate with
col_first text; --first column label, e.g., top left corner on a csv 
file or spreadsheet

columns_from_str text;
columns_to_str text;

begin
set schema 'public';

drop table if exists temp_table;

create table temp_table ();

-- add just enough number of columns
for iter in 1..col_count
loop
    execute 'alter table temp_table add column col_' || iter || ' 
varchar;';
end loop;

-- copy the data from csv file
execute 'copy temp_table from ''' || csv_path || ''' with delimiter 
'',''';

iter := 1;
col_first := (select col_1 from temp_table limit 1);

-- update the column names based on the first row which has the 
column names
for col in execute 'select 
unnest(string_to_array(trim(temp_table::text, ''()''), '','')) from 
temp_table where col_1 = ''' || col_first || ''''
loop
    execute 'alter table temp_table rename column col_' || iter || ' 
to ' || col;
    iter := iter + 1;
end loop;

-- delete the columns row
execute 'delete from temp_table where ' || col_first || ' = ''' || 
col_first || '''';

-- make string from all columns 
columns_from_str := array_to_string(columns_from, ', ');
columns_to_str := array_to_string(columns_to, ', ');

execute format('insert into %s(%s) select %s from temp_table;', 
target_table, columns_to_str, columns_from_str);


end;

$$ language plpgsql;

Collectives™ on Stack Overflow

postgresql import csv columns change dynamically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related