Consultor Eletrônico

Status: Unverified

GOAL:

How to programmatically batch convert FoxPro .dbf files and load the resulting Progress format .p data files into the appropriate tables in your existing database.

FIX:

As an example scenario, assume you have some 5000 or so FoxPro .dbf files. You have created the database schema and loaded all the data definitions .df files into the database.
You need a batch mode solution that allows you to convert all the Fox Pro data (.dbf) files into Progress data (.d) files and load their contents into your database without any human intervention.

This solution was developed and tested using Progress 8.3B. It assumes:

- The database is connected.

- For each file named X.dbf, there is a table named X in the
database with matching data definitions.

- The only (.dbf) files in the session PROPATH are those to be
converted and loaded.

Follow these steps:

1) Save the following Procedure as ConvertFoxPro.p in your
working directory where the database is assumed to be:

/*
**********************************************************************
/
/*
**********************************************************************
/
/* ********************** ConvertFoxPro.p
***********************/
/*
**********************************************************************
/
/* This batch mode procedure does the following tasks:
*/
/* 1. Scans all the directories in the current progress session's
*/
/* PROPATH for ANY FoxPro data file of type (.dbf).
*/
/*
*/
/* 2. Creates a Progress (.d)file for each (.dbf) file found giving
it */
/* the same name as the FoxPro file but with the .d extension
replacing*/
/* the .dbf extension.
*/
/*
*/
/* 3. Loads the generated .d files into the connected database that
is*/
/* assumed to already have the appropriate tables and data
definitions */
/*
**********************************************************************
/
/*
**********************************************************************
/
/* ********************** Variable Definitions
***********************/
/*
**********************************************************************
/
/* cPropathDirList : Comma separated list of all PROPATH
directories*/
/* cDirectoryName : Current directory's FULL-PATH name
*/
/* iDirectoryCounter : Integer counter used directories loop
*/
/* iDirectoryCounter : Total number of directories in the PROPATH
*/
/* cfilename : Current directory file name
*/
/* cConvertedFileName: Current converted .d file name
*/
/* cDataBaseTableName: Current table name corresponding to the .d
file */
/*
**********************************************************************
/

DEFINE VARIABLE cPropathDirList AS CHARACTER NO-UNDO.
DEFINE VARIABLE cDirectoryName AS CHARACTER NO-UNDO.
DEFINE VARIABLE iDirectoryCounter AS INTEGER NO-UNDO.
DEFINE VARIABLE iDirectoryNumber AS INTEGER NO-UNDO.
DEFINE VARIABLE cfilename AS CHARACTER NO-UNDO.
DEFINE VARIABLE cConvertedFileName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cDataBaseTableName AS CHARACTER NO-UNDO.

/*
**********************************************************************
/
/*
**********************************************************************
/
/* ********************** Pseudo-Code
********************************/
/*
**********************************************************************
/
/* Get the list of all directories in the PROPATH
*/
/* Get the number of all the directories in the PROPATH
*/
/* Do for each PROPATH Directory:
*/
/* Get the directory name
*/
/* If it is a library. Ignore it and get next directory
*/
/* Do for each file found in the directory
*/
/* If it is not a. .dbf file ignore it and get the next file
*/
/* Convert the .dbf file into a .d file
*/
/* Load the data from the .d file into the database
*/
/* End the current directory file's loop
*/
/* End the PROPATH directories' loop
*/
/*
**********************************************************************
/

ASSIGN
cPropathDirList = PROPATH
iDirectoryNumber = NUM-ENTRIES(cPropathDirList).

DO iDirectoryCounter = 1 TO iDirectoryNumber:
cDirectoryName = ENTRY(iDirectoryCounter, cPropathDirList).
IF cDirectoryName MATCHES "*~~.pl" THEN
NEXT.
DEFINE STREAM stream1.
INPUT STREAM stream1 FROM OS-DIR (cDirectoryName).
REPEAT:
IMPORT STREAM stream1 cfilename.
IF LENGTH(cfilename) < 3 THEN
NEXT.
IF cfilename MATCHES "*~~.dbf" THEN DO:
RUN ConvertAdbfFile(INPUT cfilename, OUTPUT
cConvertedFileName, OUTPUT cDataBaseTableName).
RUN ImportDotDFile.p cConvertedFileName
cDataBaseTableName.
END.
END.
INPUT STREAM stream1 CLOSE.
END.

/*
**********************************************************************
/
/*
**********************************************************************
/
/* **** PROCEDURE ConvertAdbfFile Coverts a .dbf file to .d file
*****/
/*
**********************************************************************
/

/*
**********************************************************************
/
/*
**********************************************************************
/
/* ********************** Parameter Definitions
**********************/
/*
**********************************************************************
/
/* cDBFFileName : Current .dbf file INPUT Parameter
*/
/* cConvertedFileName: Current converted .d file name OUTPUT
parameter*/
/* cDataBaseTableName: Current table name OUTPUT parameter
*/
/*
**********************************************************************
/
/*
**********************************************************************
/
/* ********************** Variable Definitions
***********************/
/*
**********************************************************************
/
/* cExecutableCommand: Full path name of the dbf.exe command utility
*/
/*
*/
/* cConversionMode: Output generated by the dbf.exe utility:
*/
/* If cConversionMode:
*/
/* = 1 Then dbf.exe produces produce .d file
*/
/* = 2 Then dbf.exe produces old v5 format .df file
*/
/* = 3 Then dbf.exe produces old v5 format .df file
*/
/* = -2 Then dbf.exe produces new V6 format .df file
*/
/* = -3 Then dbf.exe produces new V6 format .df file (index info))
*/
/*
*/
/* cBitOrderFormat: Bit ordering format of CPU that created .dbf file
*/
/* If cBitOrderFormat:
*/
/* = 0 Then the .dbf file was created on a CPU using the Most
*/
/* Significant Bit First Format (MSB). This MSB group of CPU
*/
/* manufacturers, aka the Big-Endians includes Motorola, IBM
*/
/* and other processor manufacturers.
*/
/* = 1 Then the .dbf file was created on a CPU using the the
Least */
/* Significant Bit First Format (LSB). This LSB group of CPU
*/
/* manufacturers, aka the Little-Endians includes INTEL, AMD,
*/
/* DEC and other processor manufacturers.
*/
/*
*/
/* c.CommandArguments: The dbf.exe arguments string depends on
dbfmode:*/
/* If dbfmode:
*/
/* = 1 Arguments = filename.dbf filename.err >filename.d
*/
/* = 2 Arguments = filename.dbf filename.err >filename.df
*/
/* = 3 Arguments = filename.dbf filename.ndx filename.err
>filename.df*/
/*
*/
/* cDBFErrorFileName: Generated error file name.
*/
/*
**********************************************************************
/
/*********************************************************************
***/
/* ********************** Pseudo-Code
********************************/
/*
**********************************************************************
/
/* Assign the full path name of the dbf.exe conversion utility
*/
/* Construct the name of the current database table from .dbf file
info*/
/* Construct the name of the current .d file from .dbf file info
*/
/* Construct the name of the current error file from .dbf file info
*/
/* Assign the value of Conversion and Bit Order format variables
*/
/* Run the dbf.exe utility passing it desired parameters and
arguments */
/* Return the names of the .d file name and the table name
*/
/*
**********************************************************************
/


PROCEDURE ConvertAdbfFile:
DEFINE INPUT PARAMETER cDBFFileName AS CHARACTER NO-UNDO.
DEFINE OUTPUT PARAMETER cConvertedFileName AS CHARACTER NO-UNDO.
DEFINE OUTPUT PARAMETER cDataBaseTableName AS CHARACTER NO-UNDO.

DEFINE VARIABLE cExecutableCommand AS CHARACTER NO-UNDO.
DEFINE VARIABLE cConversionMode AS CHARACTER NO-UNDO.
DEFINE VARIABLE cBitOrderFormat AS CHARACTER NO-UNDO.
DEFINE VARIABLE cCommandArguments AS CHARACTER NO-UNDO.
DEFINE VARIABLE cDBFErrorFileName AS CHARACTER NO-UNDO.

ASSIGN
cExecutableCommand = SEARCH("dbf.exe")
FILE-INFO:FILE-NAME = cDBFFileName
cDataBaseTableName = SUBSTRING(cDBFFileName, 1,
INDEX(cDBFFileName, ".dbf") - 1 )
cDBFFileName = FILE-INFO:FULL-PATHNAME
cConvertedFileName = SUBSTRING(cDBFFileName, 1,
INDEX(cDBFFileName, ".dbf") ) + "d"
cDBFErrorFileName = SUBSTRING(cDBFFileName, 1,
INDEX(cDBFFileName, ".dbf") ) + "err"
cConversionMode = "1"
cBitOrderFormat = "1"
cCommandArguments = cDBFFileName + CHR(32) + cDBFErrorFileName +
CHR(62) + cConvertedFileName.
OS-COMMAND SILENT VALUE(cExecutableCommand) VALUE(cConversionMode +
CHR(32) + cBitOrderFormat + " " + cCommandArguments).
END.
/*****************************************************************/


2) Save the following Procedure as ImportDotDFile.p in your
working directory where the database is assumed to be:

/*****************************************************************/
/****************** PROCEDURE ImportDotdFile.p *****************/
/* Import the converted .d files into the database tables */
/*****************************************************************/

INPUT FROM {1}.
REPEAT:
CREATE {2}.
IMPORT {2}.
END.
INPUT CLOSE.
/*****************************************************************/

3) Define your PROPATH variable to include all the directories
that have FoxPro .dbf files that you need to convert and load
into your database. Make sure that you exclude all the
directories that might have .dbf files that you do not want to
convert and load.

4) Run the ConvertFoxPro.p either from the procedure editor or
from the command line..