Database Import and Conversion with PHP

The previous two articles showed how to convert and import data from DBF files into MySQL. It's simple enough to enable the dbase extension for PHP for Windows, but if you're using Red Hat/Fedora, this is not as simple. There's no rpm that you can simply install to enable the extension, so you have to recompile and build the PHP Redhat/Fedora RPM. Here are the steps to do this (note that this can work with other extensions that you can't find the rpm for):

1) download the source rpm for PHP, usually it'll have the word "src" in the file name
2) install it - e.g. "rpm -Uvh php_src.rpm"
3) edit the php.spec file, usually located in
/usr/src/redhat/SPECS/php.spec, and add "--enable-dbase"
4) run rpmbuild - "rpmbuild -ba php.spec"
5) Go to the directory of the newly created RPMs (e.g. /usr/src/redhat/RPMS/i386/) and install them (note the --force):

rpm -Uvh --force php-4.3.8-1.1.i386.rpm php-devel-4.3.8-1.1.i386.rpm php-imap-4.3.8-1.1.i386.rpm php-ldap-4.3.8-1.1.i386.rpm php-mysql-4.3.8-1.1.i386.rpm php-pgsql-4.3.8-1.1.i386.rpm php-odbc-4.3.8-1.1.i386.rpm php-snmp-4.3.8-1.1.i386.rpm php-domxml-4.3.8-1.1.i386.rpm php-xmlrpc-4.3.8-1.1.i386.rpm php-mbstring-4.3.8-1.1.i386.rpm php-debuginfo-4.3.8-1.1.i386.rpm

That should get the job done!

Before continuing note that you will need the DBase extensions compiled/loaded. In Windows you simply uncomment out the line:

;extension=php_dbase.dll

In Linux and other *nix you will have to compile PHP with the "--enable-dbase" option.

The code will look for matching field names as it loops through the entire file and generate & execute INSERT statements in MySQL. Note that I start with record 1 as record 0 doesn't seem to contain actual data when using the dbase_get_record_with_names() function. Here it goes:

<?php
//by David @ PHP4IT.com 1/20/2006

//support for importing multiple DBF files at a time
//the key is the location of the DBF file & the value is the table to import
$dbf_files = array('/home/dbfs/addresses.dbf' => 'addresses');

$db_uname = '';
$db_passwd = '';
$db = 'test';
$conn = mysql_p_connect($db_uname, $db_passwd, $db);
foreach ($dbf_files as $key => $val){
    import_dbf2($db, $val, $key);
}

function import_dbf2($db, $table, $dbf_file)
{
    global $conn;
    if (!$dbf = dbase_open ($dbf_file, 0)){ die("Could not open $dbf_file for import."); }
    $num_rec = dbase_numrecords($dbf);
    $num_fields = dbase_numfields($dbf);
    $fields = array();

    for ($i=1; $i<=$num_rec; $i++){
        $row = @dbase_get_record_with_names($dbf,$i);
        $q = "insert into $db.$table values (";
        foreach ($row as $key => $val){
            if ($key == 'deleted'){ continue; }
            $q .= "'" . addslashes($val) . "',";
        }
        if (isset($extra_col_val)){ $q .= "'$extra_col_val',"; }
        $q = substr($q, 0, -1);
        $q .= ')';
        //if the query failed - go ahead and print a bunch of debug info
        if (!$result = mysql_query($q, $conn)){
            print (mysql_error() . " SQL: $q<br>\n");
            print (substr_count($q, ',') + 1) . " Fields total.<p>";
            $problem_q = explode(',', $q);
            $q1 = "desc $db.$table";
            $result1 = mysql_query($q1, $conn);
            $columns = array();
            $i = 1;
            while ($row1 = mysql_fetch_assoc($result1)){
                $columns[$i] = $row1['Field'];
                $i++;
            }
            $i = 1;
            foreach ($problem_q as $pq){
                print "$i column: {$columns[$i]} data: $pq<br>\n";
                $i++;
            }
            die();
        }
        show_status($i, $num_rec);
    }
}

//this function outputs the current progress of the import
function show_status($i, $max, $precise_mode=FALSE){
    if ($i == floor($max/4)){ print "...25%"; flush(); }
    if ($i == floor($max/2)){ print "...50%"; flush(); }
    if ($i == floor($max*0.75)) { print "...75%"; flush(); }
    if ($i >= $max) { print "...100%<br>\n"; flush(); return; }
    if ($precise_mode){
        print '...' . (round($i/$max * 100)) . "%";
        flush();
    }
}
?>

I'll present step 1 today, which involves the conversion the output from the "disp stru" command in FoxPro into a MySQL create table statement.

Consider a DBF file called "addresses.dbf"- and the "DISP STRU" of the file looks like this:

1 HNAME1 Character 30
2 ADDRESS Character 30
3 EXTRA Character 30
4 CITY Character 28
5 STATE Character 2
6 ZIP Character 10

The convert function would convert that to this:

HNAME1 char (30)
ADDRESS char (30)
EXTRA char (30)
CITY char (28)
STATE char (2)
ZIP char (10)

Here's the script:

<?php
$ddf = '    1  HNAME1      Character     30
    2  ADDRESS     Character     30
    3  EXTRA       Character     30
    4  CITY        Character     28
    5  STATE       Character      2
    6  ZIP         Character     10
';

print process_it($ddf);

//takes in string
function process_it($str)
{
    $new_line = '';
    $lines = explode("\n", $str);
    foreach($lines as $line){
        $line = substr($line, 7);
        $line = str_replace('Character', 'char', $line);
        $line = str_replace('Numeric', 'int', $line);
        $elements = explode(' ', $line);
        foreach ($elements as $key => $val){
            if (!strlen(trim($val))){ continue; }
            if (is_numeric($val)){
                $newline .= "($val),";
            }else{
                $newline .= "$val ";
            }
        }
        $newline .= "\n";
    }
    return $newline;
}
?>