Re: Importing a comma-delimited text file - Mailing list pgsql-general

Hi

I use the following that I wrote for unix type scenario named
insert_data.  You can use this then copy any file into any database
either tab or comma separated within bash.  If you don't want to use
this the syntax for copying data into a table is included in this
script.


#!/bin/bash
# Procedure to insert data from bash to a table in a database
# valid options:- -ti (tab-separated, insert);
#                         -tr (tab-separated, replace all existing
data);
#                         -ci (comma-separated, insert);
#                         -cr (comma-separated, replace);
#  . insert_data -<option> dbname tablename sourceFileName

insert () {
 echo "The following script will insert data into database: $2, table:
$3
      using data from the file: $4"
 echo "Do you wish to proceed? y/n"
 read confirm
 if [ "$confirm" = "y" ]
 then

  if test -r $4
  then
   echo "Copying in new data"
   psql -c "copy $3 from '$4' using delimiters '$delType'" -q $2
   echo "Done"
  else
   echo "File $4 could not be read"
   echo "Aborting process"
  fi
 else
 echo "Update cancelled"
 fi; }

replace () {
 echo "The following script will replace data in database: $2, table: $3

                                        using data from the file: $4"
 echo "Do you wish to proceed? y/n"
 read confirm
 if [ "$confirm" = "y" ]
 then
  if test -r $4
  then
   echo "Deleting current data"
   psql -c "delete from $3" -q $2
   echo "Done"
   echo "Copying in new Data"
   psql -c "copy $3 from '$4' using delimiters '$delType'" $2
   echo "Done"
  else
   echo "File $4 could not be read"
   echo "Aborting process"
  fi
 else
 echo "Update canceled"
 fi; }



usage_error () {
  echo "-ti | -tr | -ci | -cr <dbname> <tablename> <sourceDataFile>"
  echo "where -ti = tab-separated insert"
  echo "      -tr = tab-separated replace"
  echo "      -ci = comma-separated insert"
  echo "      -cr = comma-separated replace"; }

case $1
in
 ti | -ti) delType=' '
   insert $@;;
 tr | -tr) delType=' '
   replace $@;;
 ci | -ci) delType=\,
   insert $@;;
 cr | -cr) delType=\,
   replace $@;;
 *)  usage_error $0;;

esac

Most importantly, remember to convert any missing data to a null with a
/N

I wrote the following for tab-separated data as replace_Tab_Null:-

#Change each occurrence of two tabs in a row to tab \N tab
{
s/[place two tabs in a row here]  / [place one tab here] \\N /g
}


The above would have commas in place of tabs for comma separated.

Then:-

sed -f replace_Tab_Null $fullFileName1 > $fullFileName2

Once the file has /N for null values, then use . insert_data using
$fullFileName2

Hope this helps
Regards
Julie Hunt

Conrad Lawes wrote:

> What is the command to import data into a table from comma-delimited
> text file?
> Unfortunately, the psql on-line help (\h) does not provide a clear
> explanation.
>
> Thanks.