Skip site navigation (1) Skip section navigation (2)

Re: Importing an ASCII file

From: Vijay Deval <deval(at)giaspn01(dot)vsnl(dot)net(dot)in>
To: Francisco Reyes <lists(at)natserv(dot)com>, Prasad Paranjape <prasad_paranjape_2000in(at)yahoo(dot)com>
Cc: Pgsql Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Importing an ASCII file
Date: 2001-09-09 05:06:43
Message-ID: 3B9AF8E3.6AF56EBF@giaspn01.vsnl.net.in (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Francisco ReyesDate: 2001-09-10 15:49:55
Subject: Re: Importing an ASCII file
Previous:From: Andrew McMillanDate: 2001-09-07 23:06:45
Subject: Re: pl/pgsql function problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group