Import DBF Files To MySQL Using PHP
Thursday, December 31st, 2009I 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.
/*
* Part 2 code modified by Clint Christopher Canada from different public domain sources
* Part 1 code created by Clint Christopher Canada. BSD licensed.
*/
// This is Part I of the code
$tbl = "table to create";
$db_uname = 'mysql username';
$db_passwd = 'mysql password';
$db = 'database';
$conn = mysql_pconnect('localhost',$db_uname, $db_passwd);
// Path to dbase file
$db_path = ".DBF";
// 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();
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]` BOOL";
break;
case 'date':
$line[]= "`$col[name]` DATE";
break;
case 'memo':
$line[]= "`$col[name]` TEXT";
break;
}
}
$str = implode(",",$line);
$sql = "CREATE TABLE `$tbl` ( $str );";
mysql_select_db($db,$conn);
mysql_query($sql,$conn);
set_time_limit(0); // I added unlimited time limit here, because the records I imported were in the hundreds of thousands.
// This is part 2 of the code
import_dbf($db, $tbl, $db_path);
function import_dbf($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(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();
}
}
}
?>
(Distrowatch got my name wrong too)