Import/Convert DBF files into MySQL - Part 2

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();
    }
}
?>