Help me, please

PHP
1

Hi, I have a problem…
I am trying to convert database foxpro (.dbf) file to MySQL database file using PHP script… Please help me to convert the .dbf files to MySQL files using PHP script.

Thank you in advance… :slight_smile:

2

Google provides many ways to do this.
Here is a simple one using a free PERL tool.

3

Can you export the foxpro database to .csv file? If so, export it to CSV with the first row being the column names. In MySQL then create a table with the same column names in the same order as the .CSV column names. You can actually create the table using PHP and Mysql by using php script like this:


function createTempTable($temp_table_name){ 
        $sql = "
            CREATE TABLE $temp_table_name (
              id VARCHAR(150)
              , date_entered VARCHAR(150)
              , date_registered VARCHAR(150
                  , date_modified VARCHAR(150)
              , salutation VARCHAR(50)
              , last_name VARCHAR(150)      
              , first_name VARCHAR(150)
              , middle_names VARCHAR(150)
              , address VARCHAR(150)
              , apt_unit_number VARCHAR(150)
              , city VARCHAR(150)
              , state_prov VARCHAR(150)
              , postal_code VARCHAR(150)
              , day_phone VARCHAR(150)
              , evening_phone VARCHAR(150)
              , mobile_phone VARCHAR(150)
              , email VARCHAR(250)
              , sex VARCHAR(150)
              , year_of_birth VARCHAR(150)
              , marital_status VARCHAR(150)
              , language VARCHAR(150)
        ) TYPE=innodb; 
        ";
         $stmt = $o_Db->prepare($sql);
         $results = $stmt->execute();
         return $results;
    }

And


function importCSV($path_to_csv_file, $temp_table_name){
        /* 
         * The file must be in .csv format and needs to be terminated as commas
         * and enclosed by a single dash.
         * Returns: number of successful rows
         */
        $sql=<<<EOL
                 LOAD DATA INFILE "$path_to_csv_file"
                 REPLACE INTO TABLE $temp_table_name
                 FIELDS TERMINATED BY ',' ENCLOSED BY "'"
                 IGNORE 1 LINES
                
EOL;
            $result = $o_Db->exec($sql);
            if ($o_Db->errorCode() != 0){
                echo "\
PDOStatement::errorCode(): ";
                print $this->o_Db->errorCode();
            }
        return $result;
            
    }

Use it like:


$path_to_csv_file = '/my/path/foxpro_data.csv';
$temp_table_name = 'temp_foxpro_import';
createTempTable($temp_table_name);
$num_of_imported_records = importCSV($path_to_csv_file, $temp_table_name);
echo $num_of_imported_records;

This example clearly is devoid of security measures as there are no bound parameters or escaping, but it is a little clearer example without this.

Regards,
Steve

4

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

");
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

";
$i++;
}
die();
}
}
}

?>

5