Logo
Blog: How to Load a CSV file into PostgreSQL using a BASH script

As a DBA and a Linux Hack, I sometimes want to be able to do simple database interactions from the command line. Plus not using Python really annoys Joel. So there's that too. I could never find a straight-up example of how to do a load from a CSV file straight into PostgreSQL using just a BASH script. There may be better ways to do this but hopefully you find this useful.

First let's take a look at our CSV file. I say 'CSV' file but I usually dump data using pipes '|' since Users are always putting commas into data and causing havoc. So here's a simple example loading a 'Supplier' table with three fields 'Supplier #', 'Customer' and 'Location'

 
1000|CompanyA|Location1
2000|CompanyB|Location2
3000|CompanyC|Location3
4000|CompanyD|Location4
5000|CompanyE|Location5
6000|CompanyF|Location6
7000|CompanyG|Location7
 

My sample script does a few things that you might need to. I set a date field to today's date and I'm lazy and embed the PostgreSQL password into the script. This example is inserting one number field(domain) and the rest are strings. You will obviously need to replace my input file, database name and user with yours:

 
#!/bin/bash
 
# From Table 'Supplier'
INPUT=/home/sls/data/supplier.csv
OLDIFS=$IFS
PGPASSWORD=mypassword
IFS='|'
domain=1
today='2013-06-05'
table='supplier'
cols='"_domain","_created", "supplier", "customer", "location"'
DBNAME='sls_db'
 
export PGPASSWORD
 
while read supp cust loc
do
  supplier=$supp
  customer=$cust
  location=`echo $loc | sed 's/\\r//g'`
  export supplier customer location 
 
#
  INSERTSQL="insert into $table ($cols) VALUES($domain,'$today','$supplier','$customer','$location');"
  echo $INSERTSQL
  psql -p 5432 -U pg_admin_user --set ON_ERROR_STOP=on -c "${INSERTSQL}" $DBNAME
done < $INPUT
 
 
IFS=$OLDIF
 

BAM. Data Loaded.

Blog Index