Обсуждение: Importing a comma-delimited text file

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.

With your Postgres distribution should have come a number of PDF files.
These are a far better source of information than the \h of psql.  They can
be found in the /doc directory of the distribution tree.  For your question,
look in the user.pdf file.

Briefly, the command would look like:

COPY table_name FROM '/foo/bar/myfile.txt' USING DELIMITERS ',';

Phil Culberson
DAT Services

-----Original Message-----
From: Conrad Lawes [mailto:clawes@home.com]
Sent: Tuesday, April 11, 2000 5:37 AM
To: pgsql-questions@postgresql.org
Subject: [GENERAL] Importing a comma-delimited text file


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.

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.

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.

Try COPY.

The delimiter is "," in your case rather than the default \t.

For details see: http://alibaba.dei.uminho.pt/psql/postgres/sql-copy.htm

--
James (Jim) R.R. Service        jservice at yahoo dot com