How to import CSV file data into a PostgreSQL table

Question

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

User · Answer

You can use the pandas library if the file is note very large. Be careful when using iter over pandas dataframes. I am doing this here to demonstrate the possibility. One could also consider the pd.Dataframe.to_sql() function when copying from a dataframe to an sql table

Assuming you have created the table you want you could:

import psycopg2
import pandas as pd
data=pd.read_csv(r'path\to\file.csv', delimiter=' ')

#prepare your data and keep only relevant columns

data.drop(['col2', 'col4','col5'], axis=1, inplace=True)
data.dropna(inplace=True)
print(data.iloc[:3])


conn=psycopg2.connect("dbname=db user=postgres password=password")
cur=conn.cursor()

for index,row in data.iterrows():
      cur.execute('''insert into table (col1,col3,col6) 
    VALUES (%s,%s,%s)''', (row['col1'], row['col3'], row['col6'])
        
cur.close()
conn.commit()

conn.close()
print('\n db connection closed.')

User · Answer

In Python  you can use this code for automatic PostgreSQL table creation with column names   import pandas  csv  from io import StringIO from sqlalchemy import create engine  def psql insert copy table  conn  keys  data iter       dbapi conn   conn connection     with dbapi conn cursor   as cur          s buf   StringIO           writer   csv writer s buf          writer writerows data iter          s buf seek 0          columns        join        format k  for k in keys          if table schema              table name           format table schema  table name          else              table name   table name         sql    COPY         FROM STDIN WITH CSV  format table name  columns          cur copy expert sql sql  file s buf   engine   create engine  postgresql   user password localhost 5432 my db    df   pandas read csv  my csv   df to sql  my table   engine  schema  my schema   method psql insert copy    It s also relatively fast  I can import more than 3 3 million rows in about 4 minutes

User · Answer

DBeaver Community Edition  dbeaver io  makes it trivial to connect to a database  then import a CSV file for upload to a PostgreSQL database   It also makes it easy to issue queries  retrieve data  and download result sets to CSV  JSON  SQL  or other common data formats     It is a FOSS multi-platform database tool for SQL programmers  DBAs and analysts that supports all popular databases  MySQL  PostgreSQL  SQLite  Oracle  DB2  SQL Server  Sybase  MS Access  Teradata  Firebird  Hive  Presto  etc   It s a viable FOSS competitor to TOAD for Postgres  TOAD for SQL Server  or Toad for Oracle     I have no affiliation with DBeaver   I love the price  FREE   and full functionality  but I wish they would open up this DBeaver Eclipse application more and make it easy to add analytics widgets to DBeaver   Eclipse  rather than requiring users to pay for the  199 annual subscription just to create graphs and charts directly within the application   My Java coding skills are rusty and I don t feel like taking weeks to relearn how to build Eclipse widgets   only to find that DBeaver has probably disabled the ability to add third-party widgets to the DBeaver Community Edition      Can DBeaver power users who are Java developers provide some insight about the steps to create analytics widgets to add into the Community Edition of DBeaver

User · Answer

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     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   gt  0 then         execute format  alter table temp table rename to  I   target table       end if   end      language plpgsql

User · Answer

If you don t have permission to use COPY  which work on the db server   you can use  copy instead  which works in the db client   Using the same example as Bozhidar Batsov   Create your table   CREATE TABLE zip codes   ZIP char 5   LATITUDE double precision  LONGITUDE double precision   CITY varchar  STATE char 2   COUNTY varchar  ZIP CLASS varchar     Copy data from your CSV file to the table    copy zip codes FROM   path to csv ZIP CODES txt  DELIMITER     CSV   You can also specify the columns to read    copy zip codes ZIP CITY STATE  FROM   path to csv ZIP CODES txt  DELIMITER     CSV   See the documentation for COPY      Do not confuse COPY with the psql instruction  copy   copy invokes COPY FROM STDIN or COPY TO STDOUT  and then fetches stores the data in a file accessible to the psql client  Thus  file accessibility and access rights depend on the client rather than the server when  copy is used    and note      For identity columns  the COPY FROM command will always write the column values provided in the input data  like the INSERT option OVERRIDING SYSTEM VALUE

User · Answer

One quick way of doing this is with the Python pandas library  version 0 15 or above works best    This will handle creating the columns for you - although obviously the choices it makes for data types might not be what you want   If it doesn t quite do what you want you can always use the  create table  code generated as a template   Here s a simple example   import pandas as pd df   pd read csv  mypath csv   df columns    c lower   for c in df columns   postgres doesn t like capitals or spaces  from sqlalchemy import create engine engine   create engine  postgresql   username password localhost 5432 dbname    df to sql  my table name   engine    And here s some code that shows you how to set various options     Set it so the raw sql output is logged import logging logging basicConfig   logging getLogger  sqlalchemy engine   setLevel logging INFO   df to sql  my table name2              engine             if exists  append     options are    fail        replace        append     default    fail              index False   Do not output the index of the dataframe           dtype   col1   sqlalchemy types NUMERIC                    col2   sqlalchemy types String    Datatypes should be  sqlalchemy types  1

User · Answer

create a table first 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

User · Answer

COPY table name FROM  path to data csv  DELIMITER     CSV HEADER

User · Answer

As Paul mentioned  import works in pgAdmin   right click on table -  import  select local file  format and coding  here is a german pgAdmin GUI screenshot     similar thing you can do with DbVisualizer  I have a license  not sure about free version   right click on a table -  Import Table Data

User · Answer

Take a look at this short article     Solution paraphrased here   Create your table   CREATE TABLE zip codes   ZIP char 5   LATITUDE double precision  LONGITUDE double precision   CITY varchar  STATE char 2   COUNTY varchar  ZIP CLASS varchar     Copy data from your CSV file to the table   COPY zip codes FROM   path to csv ZIP CODES txt  WITH  FORMAT csv

User · Answer

You can create a bash file as import sh  that your CSV format is a tab delimiter     usr bin env bash  USER  quot test quot  DB  quot postgres quot  TBALE NAME  quot user quot  CSV DIR  quot   pwd  csv quot  FILE NAME  quot user txt quot   echo   psql -d  DB -U  USER  -c  quot  copy  TBALE NAME from   CSV DIR  FILE NAME  DELIMITER E  t  csv quot  2 gt  amp 1  tee  dev tty    And then run this script

User · Answer

You can also use pgfutter  or  even better  pgcsv  These tools create the table columns from you  based on the CSV header  pgfutter is quite buggy  I d recommend pgcsv  Here s how to do it with pgcsv  sudo pip install pgcsv pgcsv --db  postgresql   localhost postgres user postgres amp password      my table my file csv

User · Answer

Create table and have required columns that are used for creating table in csv file      Open postgres and right click on target table which you want to load  amp  select import and Update the following steps in file options section Now browse your file in filename  Select csv in format Encoding as ISO 8859 5   Now goto Misc  options and check header and click on import

User · Answer

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 ddd e 3 2019-01-01 eee      n   AS l  t    DBFiddle Demo

User · Answer

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

User · Answer

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

User · Answer

You could also use pgAdmin  which offers a GUI to do the import  That s shown in this SO thread  The advantage of using pgAdmin is that it also works for remote databases   Much like the previous solutions though  you would need to have your table on the database already  Each person has his own solution but what I usually do is open the CSV in Excel  copy the headers  paste special with transposition on a different worksheet  place the corresponding data type on the next column then just copy and paste that to a text editor together with the appropriate SQL table creation query like so   CREATE TABLE my table         paste data from Excel here for example            col 1 bigint      col 2 bigint                    col n bigint

User · Answer

I created a small tool that imports csv file into PostgreSQL super easy  just a command and it will create and populate the tables  unfortunately  at the moment all fields automatically created uses the type TEXT   csv2pg users csv -d     -H 192 168 99 100 -U postgres -B mydatabase   The tool can be found on https   github com eduardonunesp csv2pg

User · Answer

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

User · Answer

How to import CSV file data into a PostgreSQL table   steps    Need to connect postgresql database in terminal  psql -U postgres -h localhost  Need to create database  create database mydb   Need to create user  create user siva with password  mypass    Connect with database   c mydb   Need to create schema  create schema trip   Need to create table  create table trip test VendorID int passenger count int trip distance decimal RatecodeID int store and fwd flag varchar PULocationID int DOLocationID int payment type decimal fare amount decimal extra decimal mta tax decimal tip amount decimal tolls amount int improvement surcharge decimal total amount     Import csv file data to postgresql  COPY trip test VendorID int passenger count int trip distance decimal RatecodeID int store and fwd flag varchar PULocationID int DOLocationID int payment type decimal fare amount decimal extra decimal mta tax decimal tip amount decimal tolls amount int improvement surcharge decimal total amount  FROM   home Documents trip csv  DELIMITER     CSV HEADER   Find the given table data  select   from trip test

[postgresql] How to import CSV file data into a PostgreSQL table?

Examples related to postgresql

Examples related to csv

Examples related to postgresql-copy