SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jul 2012
    Location
    Jakarta-Indonesia
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help me, please.

    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...

  2. #2
    Avid Logophile silver trophy
    ParkinT's Avatar
    Join Date
    May 2006
    Location
    Central Florida
    Posts
    2,329
    Mentioned
    191 Post(s)
    Tagged
    4 Thread(s)
    Don't be yourself. Be someone a little nicer. -Mignon McLaughlin, journalist and author (1913-1983)


    Git is for EVERYONE
    Literally, the best app for readers.
    Make Your P@ssw0rd Secure
    Leveraging SubDomains

  3. #3
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by Masyita Anwar View Post
    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...
    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:

    PHP Code:
    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

    PHP Code:
    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 
    "\nPDOStatement::errorCode(): ";
                    print 
    $this->o_Db->errorCode();
                }
            return 
    $result;
                
        } 
    Use it like:
    PHP Code:
    $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
    ictus==""

  4. #4
    SitePoint Member
    Join Date
    Jul 2012
    Location
    kolkata
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    For your job

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

    ?>


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •