[comp.os.msdos.misc] Need help with dBase ->Informix conversion

snoopy@ixos.de (11/19/90)

Dear Net,
I have been given a floppy with the copy of an MS-DOS Database from dBase
i.e. there are *.dbf files etc.

I do not have a PC/MS-DOS machine and hence I would like to load these
dbf files into Informix. I could even do with a ASCII dump and load from
that.

However I have no idea how to go about it, dumping the dbf file yields
two lines of nearly 250K length each, with lots of NULLs etc. I have no idea
about dBAse and the format it uses etc.

Is there any program out there for UNIX that can help me ? If others are
interested the I will gladly summarise or e-mail them.

I do not usually read the MS-DOS related groups hence I would be very grateful
if you could send email directly to me. I need something cheap, preferably
PD, because I only need it this once (famous last words).

Thanks in advance,

Love,
Snoopy (Sebastian).

rhake@well.sf.ca.us (Randall Hake) (11/24/90)

In article <1281@ixos.de> snoopy@ixos.de writes:
>
>Dear Net,
>I have been given a floppy with the copy of an MS-DOS Database from dBase
>i.e. there are *.dbf files etc.
>
>I do not have a PC/MS-DOS machine and hence I would like to load these
>dbf files into Informix. I could even do with a ASCII dump and load from
>that.
>

Here are some routines that will access the Dbase3 dbf files
directly from C. The routines are located in the (following) file
"db3.c" which uses a header, "db3.h". There is a sample use file
("yield.c, "yield.h") which should show you how to call the db3
routines.

I tried to model the db3 routines after Informix. Therefore, you
provide the routines with the "view" that you want and a
structure into which the data are dumped.

If you need more information on Dbase formats see "File Formats
for Popular PC Software A Programmer's Reference" by Jeff Walden
(Wiley Press, 1986).

I haven't looked at these procedures for a long time, but I know
that they work. Unfortunately, they were written for the
Microsoft 5.0 ANSI C compiler, so you probably will have to
change the format of the function calls to Unix C syntax.

I would like to suggest that a good way to convert your data
might be to create an ASCII update file that could be used the
Informix's Informer.  Your output would look something like

add noprompt filename field1 = "XXX" field2 = "YYY";

From the informer enter ex asciifile;

Hope this helps.

                                        Randy Hake
                                        ..!apple!well!rhake

-------------------------CUT HERE-------------------------------------------
/* db3.c */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <malloc.h>
#include "db3.h"

static const long ONUMREC = 4;          /*offset to start of numrec field*/
static const long OHLEN = 8;            /*offset to start of header len field*/
static const long ORECLEN = 10;         /*offset to start of record len field*/
static const int  OFD = 32;             /*offset in header to start of field descriptors*/
static const int  OFDLEN = 16;          /*offset in field descriptor to field len*/

static const int FNLEN = 11;            /* length of db3 field names in header*/
static const int FDLEN = 32;            /* length of each field descriptor field*/

static const int ANDMASK = 0x7f;        /*should mask off bits 0x80 and above. Used in getting Dbase version number*/

enum db3_enum
{
        LEN,
        INSET
};

/* function prototypes */
static void initdb( DB3 * );
static void readh( DB3 * );
static void setver( DB3 * );
static int readrec( long, DB3 * );

/* initialize database -- always call this one first */
void db3_ctor( char *fname, DB3 *db)
{
        int exitcode = 0;

        if ( (db->f = fopen( fname, "rb")) == NULL )
                {
                fprintf(stderr, "Could not open file %s\n", fname);
                exit(-1);
                }

        initdb( db );
        readh( db );
        setver( db );
}


static void initdb( DB3 *db )
{
        if ( fseek( db->f, (long) ONUMREC, SEEK_SET ) != 0 )
                {
                fprintf(stderr, "unable to seek\n");
                exit(-1);
                }
        fread( (char * ) &db->numrec, sizeof(long), 1, db->f );

        if ( fseek( db->f, (long) OHLEN, SEEK_SET ) != 0 )
                {
                fprintf(stderr, "unable to seek\n");
                exit(-1);
                }
        fread( (char * ) &db->hlen, sizeof(int), 1, db->f );

        if ( fseek( db->f, (long) ORECLEN, SEEK_SET ) != 0 )
                {
                fprintf(stderr, "unable to seek\n");
                exit(-1);
                }
        fread( (char * ) &db->reclen, sizeof(int), 1, db->f );
        db->recbuf = (char *) malloc( sizeof(char) * db->reclen);
}

static void readh( DB3 *db  )
{
        db->header = (char * ) malloc( sizeof(char) * db->hlen );

        if ( fseek( db->f, (long) 0, SEEK_SET ) != 0 )
                {
                fprintf(stderr, "unable to seek\n");
                exit(-1);
                }
        fread( db->header, sizeof(char), db->hlen, db->f );
}

static void setver( DB3 *db  )
{
        char ch = *db->header;
        db->version = ( (int) ch & ANDMASK);
}

/* loads record into ibuf  */
int db3getrec( long recno, char *ibuf, DB3 *db)
{
        register int i;
        int flen, offset, cumlen = 0; /*field length, field offset into buffer, cumulative bytes written*/

        if ( readrec( recno, db) != 0 )
                return -1;
        if (*db->recbuf != ' ')                     /* if deleted record*/
                return -1;

        for (i = 0; i < db->numf; i++)
                {
                flen = *(db->farray + i * 2 + LEN);                         /*get field len*/
                offset = *(db->farray + i * 2 + INSET);                    /*get offset from record buff*/
                strncpy((ibuf + cumlen), (db->recbuf + 1 + offset), flen );     /*copy the string to last open position of ibuf*/
                cumlen += flen;                                         /*set cumulative length of string written to ibuf to correct value*/
                *(ibuf + cumlen ) = '\0';                               /*null terminate*/
                cumlen++;                                               /*adjust null*/
                }
        return 0;
}

static int readrec( long recno, DB3 *db)
{
        int result = 0;                 /*value to be returned is 0 or -1*/

        if ( recno < 0L || recno >= db->numrec )
                result = -1;
        else
                {
                if ( fseek( db->f, (long) (db->hlen + db->reclen * recno), SEEK_SET )  ) 
                        {
                        fprintf(stderr, "unable to seek\n");
                        exit(-1);
                        }

                fread( db->recbuf, sizeof(char), db->reclen, db->f );
                }

        return result;
}

/* prepares internal array of field names, offsets, and lengths*/
void db3setview( const char *fnames[], int numf, DB3 *db )
{
        int i;
        int cumflen = 0;                    /*cumulative field length*/
        register int thisflen = 0;          /* field length of this field    */

        db->numf = numf;


        /* allocate space to array*/
        if ( (db->farray = malloc(sizeof(int)*numf*2 )) == NULL )
           {
           printf("Unable to allocate space for dbsetview array.\nProgram aborted\n");
           exit(1);
           }

        /*for entire length of header, check each field for name.*/
        for ( i = OFD; i < db->hlen-1; i +=FDLEN ) /*start at offset of field descriptors*/
                {
                register int j;
                thisflen = (int) *(db->header + i + OFDLEN);
                for ( j = 0; j < db->numf; j++)
                        {
                        if( strncmp(*(fnames + j), db->header+i, FNLEN ) == 0 )
                                {
                                 *(db->farray + j * 2 + LEN) = thisflen;
                                 *(db->farray + j * 2 + INSET) = cumflen;
                                }
                        }
                cumflen += thisflen;
                }
}

void db3m2sdate( char *date, DB3DATE *db )
{
        char *next;    /*for use by strtok*/

        /* set century to 19*/
        strcpy (db->century, "19");
        next = strtok( date, "/" );      /*locate first field and null terminate the first so that strcpy will work*/
        
        /* set month to first value of string*/
        if (strlen( next ) < 2 )
                {
                db->month[0] = '0';
                strcpy(db->month+1, next);
                }
        else
                {
                strncpy( db->month, next, 2 );          /*use strncpy in case invalid date is input*/
                db->day[2] = '\0';                      /*null terminate in case strncpy did not          */
                }

        next = strtok( NULL, "/" );     /*locate next field and null terminate the first so that strcpy will work*/

        /*set day to second value of string*/
        if (strlen( next ) < 2 )
                {
                db->day[0] = '0';
                strcpy(db->day+1, next);
                }
        else
                {
                strncpy( db->day, next, 2 );        /*use strncpy in case invalid date is input*/
                db->day[2] = '\0';                   /*null terminate in case strncpy did not          */
                }

        next = strtok( NULL, "/" );     /*locate next field and null terminate the first so that strcpy will work*/

        /*set year to third value of string*/
        strncpy( db->year, next, 2 );        /*use strncpy in case invalid date is input*/
        db->year[2] = '\0';                   /*null terminate in case strncpy did not          */
}

void db3db2sdate( char *date, DB3DATE *db )
{
        strncpy( db->century, date, 2 );
        db->century[2] = '\0';

        strncpy( db->year, date+2, 2 );
        db->year[2] = '\0';

        strncpy( db->month, date+4, 2 );
        db->month[2] = '\0';

        strncpy( db->day, date+6, 2 );
        db->day[2] = '\0';
}

void db3s2mdate( char *date, DB3DATE *db )
{

        strcpy( date, db->month );
        strcat( date, "/" );
        strcat( date, db->day );
        strcat( date, "/" );
        strcat( date, db->year );

}

void db3s2dbdate( char *date, DB3DATE *db )
{

        strcpy( date, db->century );
        strcat( date, db->year );
        strcat( date, db->month );
        strcat( date, db->day );
}

int db3getreclen( DB3 *db )
{
   return( db->reclen );
}

long db3getnumrec( DB3 *db  )
{
   return( db->numrec );
}

int db3getver( DB3 *db )
{
   return( db->version );
}


int db3gethlen( DB3 *db )
{
   return( db->hlen);
}


/* call this at end -- closes file and frees memory */
void db3_dtor( DB3 *db)
{
        if (db->f != NULL)
                fclose( db->f );
        free(db->header);
        free(db->farray);
        free(db->recbuf);
}

--------------------------CUT HERE----------------------------------------
/* db3.h */
typedef struct db3date_st
{
        char century[3];
        char year[3];
        char month[3];
        char day[3];
} DB3DATE;



typedef struct db3_st
{
        FILE *f;
        char *header;                   /*pointer to header fields*/
        int *farray;                    /*pointer to field lengths and offsets*/
        int numf;                       /*number of fields in array*/
        long numrec;                    /*number of records in database*/
        char *recbuf;                   /*record is read into here*/
        int hlen;                       /*length of header file*/
        int reclen;                     /*length of a record*/
        int version;                    /*db3 version number*/
} DB3;

void db3_ctor(char *, DB3 *);
int db3gethlen( DB3 * );
int db3getreclen( DB3 *);
long db3getnumrec( DB3 * );
int db3getver( DB3 * );
void db3setview( const char *[], int, DB3 * );  /*prepares internal array of offset and length*/
                                        /* based on field name array*/
int db3getrec( long, char *, DB3 * );     /* reads record into buffer based on record number. returns 0 if OK -1 if not*/
void db3m2sdate( char *, DB3DATE * );       /* converts mm/dd/yy date to dbdate struct*/
void db3db2sdate( char *, DB3DATE * );      /* converts yyyymmdd date to dbdate struct*/
void db3s2mdate( char *, DB3DATE * );       /* converts dbdate struct to mm/dd/yy (char * must be 9 long)*/
void db3s2dbdate( char *, DB3DATE * );      /*converts dbdate struct to yyyymmdd date (char * must be 9 long)*/
void db3_dtor( DB3 * );

---------------------------CUT HERE----------------------------------
/* yield.c --- shows uses of db3 files */
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <conio.h>
#define GLOBAL 
#include "yield.h"
#include "db3.h"


static const char *ftitle[] =   /*titles of fields as listed in dbase file*/
{
        "Q",                    /*district*/
        "C",                    /*fiscal year of closing*/
        "B",                    /*name*/
        "ACT",                  /*activity code*/
        "CG",                   /*cep status*/
        "H",                    /*type of closing*/
        "I",                    /*date closed*/
        "TOTAL_HRS",            /*hours*/
        "INCOME_TX",            /*income change*/
        "FTC",                  /*foreign change*/
        "PENALT",               /*penalties change*/
        "OTHER_CHG",            /*other change*/
        "WHIP_SAW"              /*whipsaw*/
};

static const int NUMFIELDS = sizeof(ftitle)/sizeof(char *);


static VIEW v;


static char *dbfile = "/u2/exam.dbf";

static DB3DATE date;

/*///////////////////////////////////////////////////////////////////////////*/
/* function declarations*/
void convdate( DB3DATE * );

/*///////////////////////////////////////////////////////////////////////////*/

static DB3 db;

/*///////////////////////////////////////////////////////////////////////////*/
void main( int argc, char **argv)
{
        long numrec, l;


        db3_ctor( dbfile, &db );
        
        /*convert command line dates to db3format dates*/
        convdate( &date );

        printf("Dbase%d file \"%s\" is now open.\n\n", db3getver( &db ), dbfile );


        db3setview( ftitle, NUMFIELDS, &db );



        printf("\nScanning %ld records.\n", numrec = db3getnumrec( &db ) );



        /* while number of records searched less than number of records in file and number selected less than max space allocated*/
        for (l = 0L; l < numrec; l++) 
                {
                db3getrec(l, v.dist, &db );
                /* process each record here */
                }
}

/*///////////////////////////////////////////////////////////////////////////*/
/*convert command line dates to db3format dates*/
void convdate( DB3DATE *d )
{

        db3m2sdate( (char * ) isdate, d );
        db3s2dbdate( (char * ) sdbdate, d );
        db3s2mdate( (char * ) isdate, d );

        db3m2sdate( (char *) iedate, d);
        db3s2dbdate( (char * ) edbdate, d);
        db3s2mdate( (char * ) iedate, d );
}

--------------------------------------CUT HERE-------------------------
/* yield.h */

typedef struct view_st         /* field lengths + 1 for null */
{
        char dist[3];
        char year[5];
        char name[46];
        char act[4];
        char cep[2];
        char closcode[2];
        char closdate[9];
        char hours[6];
        char income[11];
        char ftc[11];
        char penalty[11];
        char other[11];
        char whipsaw[2];
}VIEW;