Loading...

Import DBF Files To MySQL Using PHP

I recently had a task to do for a friend of mine to migrate Visual FoxPro data into a mysql database.

As a programmer that doesn’t like to re-invent the wheel, I tried searching through google, but was only partially successful in getting code to insert dbf data into mysql (it was public domain code, but only had code to insert data into a table that was created earlier).

This is the code modified to work with any dbf file – it creates the table, then inserts the dbf data into the created table. The code was further modified to strip whitespaces from the text values (FoxPro/DBase pads text with spaces up to the length of the field). You may want to change this further, since this is a quick and dirty code for the sole purpose of importing dbf tables. This works with my rpms at the repository.


$val){
if ($key == 'deleted'){ continue; }
$q .= "'" . addslashes(trim($val)) . "',"; // Code modified to trim out whitespaces
}
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
\n");
print (substr_count($q, ',') + 1) . " Fields total.

";
$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
\n";
$i++;
}
die();
}
}
}

?>

Update:

Some users running php 5.3 and above cannot run dbase as this has been removed from php. To reinstall dbf functionality, please do the following:

1. Download latest dbase extension on this URL: http://pecl.php.net/get/dbase
2. Extract tarball and cd into the directory.
3. sudo phpize
4. sudo ./configure && sudo make && sudo make install
5. Add dbase.so into php configuration file and restart your web server.

 
 

23 comments
  1. Irina

    Hello

    What version of php have you used? I’m trying to do the same thing in php5.3 and It’s not working. It won’t load the php_dbase.dll module. I’m guessing this is the module you used for the dbf functions.

    Thanks.

  2. Norbert Bassano

    Hi! I’ve been reading your web site for some time now and finally got the courage to go ahead and give you a shout out from Porter Tx! Just wanted to tell you keep up the fantastic job!

  3. pathic

    Hello clint,
    I tried import dbf to mysql is succeeded but why everytime upload with numeric values result different with source.

    Thanks

  4. Jeff

    I need this to work so bad it hurts! Our entire call center uses old school dbase…which i know nothing about…but I know mysql pretty well.

    I’ve tried just about everything, but windows keeps saying it can’t find the php_dbase.dll dynamic link library. I’ve added it to the ext folder, the php folder, it’s the environmental variable paths…it’s in system32…AND STILL NADA. This is so frustrating!!! >:?(

    Any advice?

  5. clintcan

    You’ll have to compile the dbf extension if you’re on php 5.3 above. I’ve included instructions on how to compile for Linux, but not windows (not really familiar on compiler tools in windows)

  6. clintcan

    Hi Ferdinand,

    Like what I said, I’m mostly on Linux, so I know compiling stuff on Linux. Anyways, try doing this in your php.ini (comment out or add this line, since you’re still using 5.2.x – so it might still be included in your version)

    extension=dbase.so

  7. Charles Jaranilla

    Hello there thanks for this awesome tutorial. Im using wamp in windows and im trying to search for a workaround like this. im using php 5.4 is there anyway you could tell me the “windows-version” of the update instructions. please please please. thanks a lot!

  8. Dario Alispahic

    Hi, thanks for this awesome script. I have noticed one thing, with DBF files that I am using, when I have memo type column, insert statement doesn’t create value for insert. Instead I get error that tells me that I am inserting lesser number of values than there is columns in table. That column value is totaly irelevant for me, I’m just wondering, can you help me to modify script, so If there is a memo column, It adds blank insert value on that place.. stupid memo..

  9. Dario Alispahic

    To answer my previous question.. If you, like me don’t need to import memo type column.. Just comment 45 line of code near
    case ‘memo’:
    // $line[]= “`$col[name]` TEXT”;
    Therefore script won’t make memo column.. problem solved. Also, for people who are using Windows and have trouble of PHP compatibility, I also couldn’t find normal instructions for adding this DBF functionality to newer PHP versions.. Instead, I’we downloaded older version of Xampp 1.7.1.. Installed regulary, In php.ini uncomment
    extension=php_dbase.dll
    extension=php_dbx.dll
    restart Apache service and you are good to go..

  10. wawan

    Dear clintcan,

    Thank you so much for your great script, it does help me to migrating VFP DBF. But I’m wondering if you could help me/teach me or give me a clue to custom this awesome script. I’m having problem with one of my client, they want to make their inventory application to be accessed online for the report only (not entirely), but they don’t want to migrate the program to the web based because still want to use the desktop VFP program. So I decide to just get the VFP DBF file to be accessed by php program and save the data to Mysql and then make the web based for just reporting needs. Still stuck, but I thing this script can help me a lot if its can update table that has been create in previously step. so this is what I need.

    1. first I export the VFP DBF to MySQL and create database and table.
    2. Second, when client add new row to their VFP DBF, Only the new row that should be inserted to the table that created on first step.

    Please, please … give me a solution so that this is can be achieved.

    regard
    wawan

  11. Alexis Arauz

    I have this script modified for everybody that need.
    Enjoy!

    <?php
    //Apertura de la base de datos mysql
    global $db, $conn;
    $db_uname = 'root';
    $db_passwd = '';
    $db = 'db';
    $conn = mysql_pconnect('localhost',$db_uname, $db_passwd);
    mysql_select_db($db,$conn);

    //definicion de los dbf a migrar al mysql
    //migrar(mysql_table, full_path_to_file.dbf, full_path_to_file.fpt)

    migrar("file1","C:\db\file1.dbf","C:\db\file1.fpt"); // in this example the dbf has memo fields
    migrar("file2","C:\db\file2.dbf",""); //In this example the dbf doesn't has memo fields
    exit;

    //———————————————————————————
    function migrar($mysql_table,$dbf_path,$memo_file) {
    create_table($mysql_table, $dbf_path);
    importdbf($mysql_table, $dbf_path, $memo_file);
    return;
    }
    //—————————————————-
    function create_table($mysql_table, $db_path)
    {
    global $conn;

    // Open dbase file
    $dbh = dbase_open($db_path, 0) or die("Error! Could not open dbase database file '$db_path'.");

    // Get column information
    $column_info = dbase_get_header_info($dbh);

    // Display information
    //print_r($column_info);

    $line = array();
    $b=0;
    foreach($column_info as $col)
    {
    switch($col['type'])
    {
    case 'character':
    $line[]= "`$col[name]` VARCHAR( $col[length] )";
    break;
    case 'number':
    $line[]= "`$col[name]` FLOAT";
    break;
    case 'boolean':
    $line[]= "`$col[name]` CHAR(2)";
    break;
    case 'date':
    $line[]= "`$col[name]` DATE";
    break;
    case 'memo':
    $line[]= "`$col[name]` TEXT";
    break;
    }
    $b=$b+1;
    //print "field $b = $col[name] “;
    }
    $str = implode(“,”,$line);
    $sql = “CREATE TABLE `$mysql_table` ( $str );”;
    //print $sql.””;
    if (!$result = mysql_query($sql, $conn)){
    //print (mysql_error() . ” SQL: $sql “);
    print (“”. mysql_error() . “”);
    } else {
    $i +=1;
    print “Tabla Creada: $mysql_table“;
    }

    }
    //—————————————————–
    function importdbf($mysql_table, $dbf_path,$fpt_path)
    {
    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();
    $i=0;
    $Test = new Prodigy_DBF($dbf_path, $fpt_path);
    while(($Record = $Test->GetNextRecord(true)) and !empty($Record)) {
    $a=0;
    $sql1 = “insert into $db.$mysql_table (“;
    $sql2 = “) values (“;
    $sql=””;
    foreach ($Record as $key => $val){
    if ($val == ‘{BINARY_PICTURE}’){ continue; }
    $val = str_replace(“‘”, “”, $val);
    $a = $a +1;
    //print “a($a)=$key = $val”;
    if ($a==1) {
    $sql1 .=$key;
    $sql2 .=”‘”.trim($val).”‘”;
    } else {
    $sql1 .=”,$key”;
    $sql2 .=”,’$val'”;
    }
    }
    $sql=”$sql1 $sql2)”;
    //print “Sql=$sql”;
    if (!$result = mysql_query($sql, $conn)){
    print (mysql_error() . ” SQL: $sql “);
    } else {
    $i +=1;
    }
    }
    print “$i Registro Insertado”;

    }

    class Prodigy_DBF {
    private $Filename, $DB_Type, $DB_Update, $DB_Records, $DB_FirstData, $DB_RecordLength, $DB_Flags, $DB_CodePageMark, $DB_Fields, $FileHandle, $FileOpened;
    private $Memo_Handle, $Memo_Opened, $Memo_BlockSize;

    private function Initialize() {

    if($this->FileOpened) {
    fclose($this->FileHandle);
    }

    if($this->Memo_Opened) {
    fclose($this->Memo_Handle);
    }

    $this->FileOpened = false;
    $this->FileHandle = NULL;
    $this->Filename = NULL;
    $this->DB_Type = NULL;
    $this->DB_Update = NULL;
    $this->DB_Records = NULL;
    $this->DB_FirstData = NULL;
    $this->DB_RecordLength = NULL;
    $this->DB_CodePageMark = NULL;
    $this->DB_Flags = NULL;
    $this->DB_Fields = array();

    $this->Memo_Handle = NULL;
    $this->Memo_Opened = false;
    $this->Memo_BlockSize = NULL;
    }

    public function __construct($Filename, $MemoFilename = NULL) {
    $this->Prodigy_DBF($Filename, $MemoFilename);
    }

    public function Prodigy_DBF($Filename, $MemoFilename = NULL) {
    $this->Initialize();
    $this->OpenDatabase($Filename, $MemoFilename);
    }

    public function OpenDatabase($Filename, $MemoFilename = NULL) {
    $Return = false;
    $this->Initialize();

    $this->FileHandle = fopen($Filename, “r”);
    if($this->FileHandle) {
    // DB Open, reading headers
    $this->DB_Type = dechex(ord(fread($this->FileHandle, 1)));
    $LUPD = fread($this->FileHandle, 3);
    $this->DB_Update = ord($LUPD[0]).”/”.ord($LUPD[1]).”/”.ord($LUPD[2]);
    $Rec = unpack(“V”, fread($this->FileHandle, 4));
    $this->DB_Records = $Rec[1];
    $Pos = fread($this->FileHandle, 2);
    $this->DB_FirstData = (ord($Pos[0]) + ord($Pos[1]) * 256);
    $Len = fread($this->FileHandle, 2);
    $this->DB_RecordLength = (ord($Len[0]) + ord($Len[1]) * 256);
    fseek($this->FileHandle, 28); // Ignoring “reserved” bytes, jumping to table flags
    $this->DB_Flags = dechex(ord(fread($this->FileHandle, 1)));
    $this->DB_CodePageMark = ord(fread($this->FileHandle, 1));
    fseek($this->FileHandle, 2, SEEK_CUR); // Ignoring next 2 “reserved” bytes

    // Now reading field captions and attributes
    while(!feof($this->FileHandle)) {

    // Checking for end of header
    if(ord(fread($this->FileHandle, 1)) == 13) {
    break; // End of header!
    } else {
    // Go back
    fseek($this->FileHandle, -1, SEEK_CUR);
    }

    $Field[“Name”] = trim(fread($this->FileHandle, 11));
    $Field[“Type”] = fread($this->FileHandle, 1);
    fseek($this->FileHandle, 4, SEEK_CUR); // Skipping attribute “displacement”
    $Field[“Size”] = ord(fread($this->FileHandle, 1));
    fseek($this->FileHandle, 15, SEEK_CUR); // Skipping any remaining attributes
    $this->DB_Fields[] = $Field;
    }

    // Setting file pointer to the first record
    fseek($this->FileHandle, $this->DB_FirstData);

    $this->FileOpened = true;

    // Open memo file, if exists
    if(!empty($MemoFilename) and file_exists($MemoFilename) and preg_match(“%^(.+).fpt$%i”, $MemoFilename)) {
    $this->Memo_Handle = fopen($MemoFilename, “r”);
    if($this->Memo_Handle) {
    $this->Memo_Opened = true;

    // Getting block size
    fseek($this->Memo_Handle, 6);
    $Data = unpack(“n”, fread($this->Memo_Handle, 2));
    $this->Memo_BlockSize = $Data[1];
    }
    }
    }

    return $Return;
    }

    public function GetNextRecord($FieldCaptions = false) {
    $Return = NULL;
    $Record = array();

    if(!$this->FileOpened) {
    $Return = false;
    } elseif(feof($this->FileHandle)) {
    $Return = NULL;
    } else {
    // File open and not EOF
    fseek($this->FileHandle, 1, SEEK_CUR); // Ignoring DELETE flag
    foreach($this->DB_Fields as $Field) {
    $RawData = fread($this->FileHandle, $Field[“Size”]);
    // Checking for memo reference
    if($Field[“Type”] == “M” and $Field[“Size”] == 4 and !empty($RawData)) {
    // Binary Memo reference
    $Memo_BO = unpack(“V”, $RawData);
    if($this->Memo_Opened and $Memo_BO != 0) {
    fseek($this->Memo_Handle, $Memo_BO[1] * $this->Memo_BlockSize);
    $Type = unpack(“N”, fread($this->Memo_Handle, 4));
    if($Type[1] == “1”) {
    $Len = unpack(“N”, fread($this->Memo_Handle, 4));
    $Value = trim(fread($this->Memo_Handle, $Len[1]));
    } else {
    // Pictures will not be shown
    $Value = “{BINARY_PICTURE}”;
    }
    } else {
    $Value = “{NO_MEMO_FILE_OPEN}”;
    }
    } else {
    $Value = trim($RawData);
    }

    if($FieldCaptions) {
    $Record[$Field[“Name”]] = $Value;
    } else {
    $Record[] = $Value;
    }
    }

    $Return = $Record;
    }

    return $Return;
    }

    function __destruct() {
    // Cleanly close any open files before destruction
    $this->Initialize();
    }
    }
    ?>

  12. Devi

    Hello,
    i tried to install dbase extension in my MAMP using your step for PHP 5.3 above.

    i have done this:
    1. Download latest dbase extension on this URL: http://pecl.php.net/get/dbase <-Done
    2. Extract tarball and cd into the directory.<-Done.
    3. sudo phpize <-done
    4. sudo ./configure && sudo make && sudo make install <-done sucessfully
    5. Add dbase.so into php configuration file and restart your web server. <-done i have copy and paste manually from my extracted at step number 2 to my php extension folder. also i have added this into php.ini:

    extension=dbase.so

    then i restart Apache.

    but after look at phpinfo, do dbase extension installed/show

    then i try to run your code it gets only blank page…

    please anyone or writer can help me. i'm so confused what to do now

    Thank You..!

  13. Devi

    sorry for mistype:

    “but after look at phpinfo, DO dbase extension installed/show” <- i mean is NO dbase extension installed/show

  14. Seweyewu

    Nice code.

    I have used it to import more than 10000 rows and it works. but it is slow for larger rows. does anyone has any recommendations.

    Appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *