Thread: Importing an ASCII file

I have an ASCII file with fields separated by tabs.
My only issue is that text fields are enclosed with "
Is there a way to tell Postgresql to ignore those "?
Mean time I am going to try and take out the " using one of the Unix
tools.

Didn't see anything on the FAQ on importing data from ASCII files. Isn't
this an FAQ?

Francisco Reyes wrote:
>
> I have an ASCII file with fields separated by tabs.
> My only issue is that text fields are enclosed with "
> Is there a way to tell Postgresql to ignore those "?
> Mean time I am going to try and take out the " using one of the Unix
> tools.
>
> Didn't see anything on the FAQ on importing data from ASCII files. Isn't
> this an FAQ?

Couldn't you pipe it through tr?

A simple 'c' script might do the job of converting a fixed field length
text file to a format suitable for Postgres. Some days back I had
submitted a script that puts a field separator at at predefined
locations. The script did not remove trailing blanks. It did not put "'"
before and after the text fields. I have tested the following script on
one file and found it to work. The file has 5 fields, 4,45,40,40,40
long. first is numeric, others are text.  "|" could be replaced by ","
if that is more convenient. If this is found suitable, it could be
developed into a script where the information could be passed to the
program as  parameters.
It would be possible do develop it further so that the program
identifies whether the field is character or neumeric. But "DATE" field
could pose a problem. If experienced users feel that conditions (field
length equal to 8 and first character as 1 or 2 ) would be sufficient
conditions, then it should be possible to modify the script to identify
field types.

-------------------------------------------------------------------------

#include <stdio.h>

#define FNM 5     /* number of fields */
#define F1 4
main()
{
int c,i,len,count,flg;
int flen[FNM];
int nmchr[FNM];
 flen[0]=4;
 flen[1]=45;    /*length of second field */
 flen[2]=40;    /* length of third field */
 flen[3]=40;
 flen[4]=40;

 nmchr[0]=0; /* 0 for numbers */
 nmchr[1]=1; /* 1 for char field */
 nmchr[2]=1;
 nmchr[3]=1;
 nmchr[4]=1;
 flg=0;
count=0;
i=0;
 len=F1;
 c=getchar();
 while (c != EOF){
   if( (nmchr[i]==1)&&(count==0))
     printf("'");
   if(flg<2)    /* when flg==2 it has already printed one blank */
     putchar(c);
   else
       ;    /* do not print anything, that is remove blanks*/
     count=count+1;
     c=getchar();
     if (c==' ')
       flg=flg+1;
     else
       flg=0;

     if (count==len){
       if(nmchr[i]==1)
         printf("'|");
       else
         printf("|");
         i=i+1;
         len=len+flen[i];
         if (nmchr[i]==1)
           printf("'");
     }
     if(c=='\n'){
       if (nmchr[i]==1)
         printf("'");
       count=0;
       i=0;
       len=F1;
       putchar(c);
       c=getchar();
     }
 }
}

----------------------------------------------------------------

Vijay

On 9 Sep 2001, Andrew McMillan wrote:

> On Fri, 2001-09-07 at 04:08, Francisco Reyes wrote:
> > I have an ASCII file with fields separated by tabs.
> > My only issue is that text fields are enclosed with "
> > Is there a way to tell Postgresql to ignore those "?
> > Didn't see anything on the FAQ on importing data from ASCII files. Isn't
> > this an FAQ?
>
> I have a perl script which handles this sort of thing, but in my use it
> was comma-delimited rather than tab delimited, so would need some
> minimal change.

yes, at your convenience please send it.

My problem was two fold. First I didn't know what command was needed to do
the import (copy from). I was surprised to NOT find this info on the FAQ.
I thought this would have been a FAQ.

The second problem were the quotes. Given that the data was coming from a
Foxpro table I just ended up creating a Foxpro program to spit out the
data the way I needed.

There are DBF to PostgreSQL converters, but the Foxpro program gives me
the most flexibility and speed (no need to copy all fields, only the ones
I need).