How to import CSV file data into a PostgreSQL tabl

Question

How can I write a stored procedure that imports data from a CSV file and populates the table?

Answer 1

1. create a table first

2. Then use copy command to copy the table details:

copy table_name (C1,C2,C3....)
from 'path to your csv file' delimiter ',' csv header;

Thanks

Answer 2

Use this SQL code

    copy table_name(atribute1,attribute2,attribute3...)
    from 'E:\test.csv' delimiter ',' csv header

the header keyword lets the DBMS know that the csv file have a header with attributes

for more visit http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/

Answer 3

If you need simple mechanism to import from text/parse multiline CSV you could use:

CREATE TABLE t   -- OR INSERT INTO tab(col_names)
AS
SELECT
   t.f[1] AS col1
  ,t.f[2]::int AS col2
  ,t.f[3]::date AS col3
  ,t.f[4] AS col4
FROM (
  SELECT regexp_split_to_array(l, ',') AS f
  FROM regexp_split_to_table(
$$a,1,2016-01-01,bbb
c,2,2018-01-01,ffffd
e,3,2019-01-01,eee$$, '\n') AS l) t;

DBFiddle Demo

Answer 4

Personal experience with PostgreSQL, still waiting for a faster way.

1. Create table skeleton first if the file is stored locally:

    drop table if exists ur_table;
    CREATE TABLE ur_table
    (
        id serial NOT NULL,
        log_id numeric, 
        proc_code numeric,
        date timestamp,
        qty int,
        name varchar,
        price money
    );
    COPY 
        ur_table(id, log_id, proc_code, date, qty, name, price)
    FROM '\path\xxx.csv' DELIMITER ',' CSV HEADER;

2. When the \path\xxx.csv is on the server, postgreSQL doesn't have the permission to access the server, you will have to import the .csv file through the pgAdmin built in functionality.

Right click the table name choose import.

If you still have problem, please refer this tutorial. http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/

Answer 5

Most other solutions here require that you create the table in advance/manually. This may not be practical in some cases (e.g., if you have a lot of columns in the destination table). So, the approach below may come handy.

Providing the path and column count of your csv file, you can use the following function to load your table to a temp table that will be named as target_table:

The top row is assumed to have the column names.

create or replace function data.load_csv_file
(
    target_table text,
    csv_path text,
    col_count integer
)

returns void as $$

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 'your-schema';

    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;

$$ language plpgsql;

Answer 6

IMHO, the most convenient way is to follow "Import CSV data into postgresql, the comfortable way ;-)", using csvsql from csvkit, which is a python package installable via pip.