CoolComputing Logo
Coupons/Deals  ·  New Promo Codes/Coupons  · February 21, 2017

Import/Convert DBF files to MySQL Using PHP

Posted on Tuesday, November 25, 2014 @ 09:53:07 PM CST by David Yee [] [read 8055 times]
 
PHP Tips and Code Examples

If you have been presented with the task of having to import a DBase/DBF - here is a two step way to approach the problem. First the MySql table structure needs to be created, and then the DBF is then read and inserted into the table. Step 1 involves the conversion the output from the "disp stru" command in FoxPro into a MySQL create table statement. Consider a example 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 transform that to this, which is useful to create a matching MySQL table:
HNAME1 char (30)
ADDRESS char (30)
EXTRA char (30)
CITY char (28)
STATE char (2)
ZIP char (10)
Here is the script after the break.



<?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
';

echo 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;
}
?>

Now for step 2, we focus on reading from the DBF file (addresses.dbf) and then inserting the data into the MySQL table (in this case a table called "addresses") created from the CREATE TABLE statement generated in part 1.

Before continuing note that you will need the DBase extensions compiled/loaded. In Windows, in your php.ini file, 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
//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)){
            echo (mysql_error() . " SQL: $q");
            echo (substr_count($q, ',') + 1) . " Fields total.<br>";
            $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){
                echo "$i column: {$columns[$i]} data: $pq<br>";
                $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>"; flush(); return; }
    if ($precise_mode){
        echo '...' . (round($i/$max * 100)) . "%";
        flush();
    }
}
?>


Please rate this article or post a brief review of Import/Convert DBF files to MySQL Using PHP or comment on benchmark/performance, features, availability, price or anything else regarding Import/Convert DBF files to MySQL Using PHP. Thanks!

Import/Convert DBF Files To MySQL Using PHP Rating: 3.0/5 (25 votes cast)

Your Name:
I have read and agreed to the Review Posting Agreement.
Review Title:
Comment/Review: