Generate SQL Script File from Xbase

I have a task to write a new system of friend of mine. His existing system was run on Xbase, a MS DOS based application.

One of interesting part is doing data transformation from DBase format (table) into a database server such MySQL or PostgreSQL.
The scenario I planned were:
1. Read DBase table format and data
2. Generate a sql script file for creating table an inserting data
3. Run the script

Okay, there are many GUI tools already. I've a try using GUI tools such as Exportizer even another tool Foxpro-Postgresql import-export or MySQL Front. Those are good, later I arrived at dbf2sql. I compile and test the program . It works.

After some testing I realize that there are no NULL keyword generated for numeric, date, and character data type. I modify the source to generate a NULL keyword for non existing value of every field/column and generate some commands to satisfy my requirements.

Here the snippet code using C:
...
for(X = 0; X <>Field[Y].Length; X++)
{
temp[X] = *(data++);
}
temp[X] = '\0';

if(DBase->Field[Y].Type == 'N' DBase->Field[Y].Type == 'n')
{
if(!Trim) //it's a number, trim anyway...
TrimSpaces(temp, DBase->Field[Y].Length);
if(temp[0] == '.')
fprintf(output, "0%s", temp);
else
if(temp[0] == '-' && temp[1] == '.')
fprintf(output, "-0%s", &(temp[1]));
else if(temp[0] == '\0' )
fprintf(output, "null");
else
fprintf(output, "%s", temp);
}
....


The command to generate a script file:
C:\TEST>dbf2sql -i rekening.dbf -t tova.rek0 -o cmd.sql

The parameter provide input file name i.e rekening.dbf, and specify the schema and table name of the generated sql, and specify the destination to file cmd.sql. The contents of cmd.sql file cotains such following code:

DROP TABLE tova.rek0;
create table tova.rek0 (I've cut the detail);
INSERT INTO tova.rek0 VALUES ('11010001', 20, 20, 20, 4069, 4069, null, null, null, null, null, null, null, null, null, null, 0, '!', 'IB', 'L', 18450, 0, '2009-01-19', '2008-12-16', 'A', 'A', 20, 20, 0, '2009-01-19', null, null, null, null, null, null, null, null, null, null, null, 20, null, null, null, null, null, null, null, null, 0, 0, 0, 0, null, null, null, null, null, null, null, null, 0, 0, 0, 1000, null, null, null, null, null, null, null, null, null, null, null, 2000, null, null, null, null, null, null, null, null, null, null, null, 0, null, null, null, null, null, null, null, null, null, null, null, 15450, null, null, null, null, null, null, null, null, null, null, null, 'A', null, null, null, null, null, null, null, null, null, null, null, 'IB', null, null, null, null, null, null, null, null, null, null, null, 'L', null, null, null, null, null, null, null, null, null, null, null, '!', null, null, null, null, null, null, null, null, null, null, null, 'L', 'IB', 0, 'ZAMAG', null, null, null, null, null, null, null, null, null, null, null, 19, null, null, null, null, null, null, null, null, null, null, null, 0, 0, 0, 'A', 'A', 'A');

I love to use the dbf2sql to generate sql script for simplicity, customizable and speed reasons. The next step is to run the script into database. For PostgreSQL try this.