How to copy from CSV file to PostgreSQL table with headers in CSV file

Question

I want to copy a CSV file to a Postgres table  There are about 100 columns in this table  so I do not want to rewrite them if I don t have to   I am using the  copy table from  table csv  delimiter     csv  command but without a table created I get ERROR  relation  table  does not exist  If I add a blank table I get no error  but nothing happens  I tried this command two or three times and there was no output or messages  but the table was not updated when I checked it through PGAdmin    Is there a way to import a table with headers included like I am trying to do

User · Answer

I have been using this function for a while with no problems  You just need to provide the number columns there are in the csv file  and it will take the header names from the first row and create the table for you   create or replace function data load csv file               target table  text  -- name of the table that will be created         csv file path text          col count     integer            returns void  as     declare     iter      integer  -- dummy integer to iterate columns with     col       text  -- to keep column names in each iteration     col first text  -- first column name  e g   top left corner on a csv file or spreadsheet  begin     set schema  data        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 file 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    using quote ident or  I does not work here       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   gt  0 then         execute format   alter table temp table rename to  I   target table       end if  end      language plpgsql

User · Answer

Alternative by terminal with no permission  The pg documentation at NOTES say     The path will be interpreted relative to the working directory of the server process  normally the cluster s data directory   not the client s working directory    So  gerally  using psql or any client  even in a local server  you have problems     And  if you re expressing COPY command for other users  eg  at a Github README  the reader will have problems      The only way to express relative path with client permissions is using STDIN       When STDIN or STDOUT is specified  data is transmitted via the connection between the client and the server    as remembered here   psql -h remotehost -d remote mydb -U myuser -c       copy mytable  column1  column2  from STDIN with delimiter as            lt    relative path file csv

User · Answer

With the Python library pandas  you can easily create column names and infer data types from a csv file   from sqlalchemy import create engine import pandas as pd  engine   create engine  postgresql   user pass localhost db name   df   pd read csv   path to csv file   df to sql  pandas db   engine    The if exists parameter can be set to replace or append to an existing table  e g  df to sql  pandas db   engine  if exists  replace    This works for additional input file types as well  docs here and here

User · Answer

This worked  The first row had column names in it   COPY wheat FROM  wheat crop data csv  DELIMITER     CSV HEADER

User · Answer

You can use d6tstack which creates the table for you and is faster than pd to sql   because it uses native DB import commands  It supports Postgres as well as MYSQL and MS SQL   import pandas as pd df   pd read csv  table csv   uri psql    postgresql psycopg2   usr pwd localhost db  d6tstack utils pd to psql df  uri psql   table     It is also useful for importing multiple CSVs  solving data schema changes and or preprocess with pandas  eg for dates  before writing to db  see further down in examples notebook  d6tstack combine csv CombinerCSV glob glob    csv         apply after read apply fun  to psql combine uri psql   table

[postgresql] How to copy from CSV file to PostgreSQL table with headers in CSV file?

Examples related to postgresql

Examples related to csv

Examples related to postgresql-copy