CODEWITHSUNDEEP
postgresqlpgadmin-4
Abdulrahman Yasser Mahmoud
Abdulrahman Yasser Mahmoud

Reputation: 11

any way to import a csv file to postgresql without creating new tables from scratch?

I have 2 files and want to import them to PostgreSql using pgAdmin4 so the only way I know is to create the tables and manually create the columns and every thing from the beginning, but this will take a lot of time hoping I didn't miss any thing, so any way to simply import them and PostgreSql consider the headers as the columns, creates the tables and saves me tons of time. I'm new to all of this so my question form maybe wrong but this is the best I can ask.

Upvotes: 1

Views: 4525

Answers (1)

Tejas
Tejas

Reputation: 1

This is a approach, which I found here (from mmatt). Basically you call a function within Postgres (last argument specifies the number of columns).

CREATE OR REPLACE FUNCTION load_csv_file(
target_table text,
csv_path text,
col_count integer)
RETURNS void AS
$BODY$

declare

iter integer; -- dummy integer to iterate columns with
col text; -- variable to keep the column name at each iteration
col_first text; -- first column name, e.g., top left corner on a csv file or spreadsheet

begin
set schema 'public';

create table temp_table ();

-- 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);

-- update the column names based on the first row which has the column names
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;

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

-- change the temp table name to the name given as parameter, if not blank
if length(target_table) > 0 then
    execute format('alter table temp_table rename to %I', target_table);
    end if;

end;

$BODY$
  LANGUAGE plpgsql;

ALTER FUNCTION load_csv_file(text, text, integer)
  OWNER TO postgres;

After use this function to create table

select load_csv_file('myTable','C:/MyPath/MyFile.csv',24)

Upvotes: 0

Related Questions