Skip to main content

Import DBF to Postgre

dbf-posrgre.php
 
<!DOCTYPE html>
<html>
<head>
  <title>Progress Bar</title>
</head>

<body>
<!-- Progress bar holder -->

<link rel="stylesheet" href="progress/bootstrap.min.css">

<div id="progress" class="progress-bar progress-bar-striped active" style="width:500px;border:1px solid #000;"></div>

<!-- Progress information -->
<div id="information" style="width"></div>
</br>

<?php

@include "config-postgre.php";            
@include "classes/XBase/Table.php";
@include "classes/XBase/Column.php";
@include "classes/XBase/Record.php";
@include "classes/DBFhandler.php";

use XBase\Table;

 // Initializing vars
ini_set( 'memory_limit', '2048M' );
set_time_limit( 0 );

$time_start = time();
$files = scandir($xbase_dir) or die ("Error! Could not open directory '$xbase_dir'.");


$conn =    pg_connect("host=$db_host dbname=$db_name user=$db_uname password=$db_passwd");
   
/* $conn = new mysqli($db_host, $db_uname, $db_passwd, $db_name) or die ("Error connecting to mysql $mysqli->connect_error"); */

foreach ($files as $file) {
  switch ($file) {
  case (preg_match("/dbf$/i", $file) ? true : false):

   
    echo "File ditemukan : $file </br>";
   
      dbftopostgre($file);
   
      break;
  default:
 
      /* echo "Other file: $file</br>"; */
   
  }

}
$time_end = time();
echo "Import Selesai! Dengan waktu: ". round( ( $time_end - $time_start ) / 60, 2 ) ." minutes</br>";



function dbftopostgre($file) {
    // Path to dbase file

    global $xbase_dir;
    global $conn;
    global $die_on_pgsql_error;

    $db_path = sprintf("%s/%s",$xbase_dir,$file);

    // Open dbase file
    $table = new Table($db_path);
    $tbl = substr($file,0,strlen($file)-4);

/*     echo "$tbl"; */
   
    $line = array();

    foreach ($table->getColumns() as $column) {
/*         print_r("\t$column->name ($column->type / $column->length)\n"); */
        switch($column->type) {
            case 'C':    // Character field
                $line[]= "$column->name character varying($column->length) [] NOT NULL";
                break;
            case 'F':    // Floating Point
                $line[]= "$column->name FLOAT";
                break;
            case 'N':    // Numeric
                $line[]= "$column->name numeric[] NOT NULL";
                break;
            case 'L':    // Logical - ? Y y N n T t F f (? when not initialized).
                $line[]= "$column->name integer [] NOT NULL";
                break;
            case 'D':    // Date
                $line[]= "$column->name date [] NOT NULL";
                break;
            case 'T':    // DateTime
                $line[]= "$column->name time [] NOT NULL";
                break;
            case 'M':    // Memo type field
            default:
                $line[]= "$column->name char($column->length) NOT NULL";
                break;
        }
    }

    $str = implode(",",$line);
    $sql = 'CREATE TABLE IF NOT EXISTS "'. $tbl .'" ( '.$str.' ) WITH (oids = false);';

   
    if (pg_query($conn, $sql) === TRUE) {
       
        echo "Tabel: $tbl telah dibuat </br>";
       
    } else {
        echo "Error SQL: ".$conn->error ." >> $sql \n";
        if ($die_on_pgsql_error) {
            die;
        }
    }

/*     $sql = 'TRUNCATE TABLE public."'.$tbl.'"'; //hapus data lama
    pg_query($conn, $sql);   */
   
    $table->close();

// Import using dbf + fpt files (for MEMO data...)
    $fpt_file = str_replace( '.dbf', '.fpt', $db_path );
    $fpt_path = ( file_exists( $fpt_file ) ? $fpt_file : '' );
    import_dbf_to_mysql( $tbl, $db_path, $fpt_path );
//    import_dbf($db_path, $tbl);
}


function import_dbf($db_path, $tbl) {
    global $conn;
    global $die_on_pgsql_error;
    // print_r ("$db_path\n");
    $table = new Table($db_path);

/*     echo "$table->recordCount</br>"; */
    $total=$table->recordCount;
    echo sizeof($table->columns) ."</br>";
    $i = 0;
    while ($record=$table->nextRecord()) {


        $fields = array();
        $line = array();
        foreach ($record->getColumns() as $column) {
            $fields[]=$column->name;
            echo $column->name;
            switch($column->type) {
                case 'C':    // Character field
                case 'M':    // Memo type field
                    $line[]= sprintf("'%s'", $record->getObject($column) );
                    break;
                case 'F':    // Floating Point
                    $line[]=sprintf("%7.2f", $record->getObject($column) );
                    break;
                case 'N':    // Numeric
                    $line[]=sprintf("%d", $record->getObject($column) );
                    break;
                case 'L':    // Logical - ? Y y N n T t F f (? when not initialized).
                    // $line[] = sprintf("%d", ($record->getBoolean($column) ? 1 : 0) );
                    $line[] = sprintf("%d", $record->getString($column->name) );
                    break;
                case 'T':    // DateTime
                case 'D':    // Date
                    $line[]= sprintf("'%s'", strftime("%Y-%m-%d %H:%M", $record->getObject($column) ) );
                    break;
            }
        }

        $val = implode(",",$line);
        $col = implode(",",$fields);

        if($GLOBALS['from_encoding']!="")$val = mb_convert_encoding($val, 'UTF-8', $GLOBALS['from_encoding'] );

        $sql = 'INSERT INTO "'.$tbl.'" ("'. strtolower($col).'") VALUES ("'.$val.'")\n';
        echo "$sql";
        if (pg_query($conn, $sql) === TRUE) {
            $i++;
            if ( $i % 100 == 0 ) {
                echo "$i records inserted in $tbl\n";
            }
            die;
        } else {
            echo "Error SQL: ".$conn->error ." >> $sql \n";
            if ($die_on_pgsql_error) {
                die;
            }
        }
    }
    $table->close();
    echo "Table $tbl telah diimpor</br>";

}

function import_dbf_to_mysql( $table, $dbf_path, $fpt_path ) {
    echo "Proses impor: Tabel $table </br>";

    global $conn;
    global $die_on_pgsql_error;
    $i = 0;
   
    $Test = new DBFhandler( $dbf_path, $fpt_path );
   
   
    while ( ($Record = $Test->GetNextRecord( true )) and ! empty( $Record ) ) {

        // Calculate the percentation http://stackoverflow.com/questions/15298071/progress-bar-with-mysql-query-with-php
            $percent = intval($i/10000 * 100)."%";

            // Javascript for updating the progress bar and information
              echo '<script language="javascript">
              document.getElementById("progress").innerHTML="<div style=\"width:'.$percent.';background-color:#000000;\">&nbsp;</div>";
              document.getElementById("information").innerHTML=" '.$i.' data sedang diproses.";
              </script>';
             // This is for the buffer achieve the minimum size in order to flush data
              echo str_repeat(' ',1024*64);

              // Send output to browser immediately
              flush();

              // Sleep one second so we can see the delay
              sleep(0);
             
        $a = 0;
        $sql1 = 'INSERT INTO "'.$table.'" (';
        $sql2 = ") VALUES (";
        $sql = "";
        foreach ( $Record as $key => $val ) {
            $key = (strpos($key, 0x00) !== false ) ? substr($key, 0, strpos($key, 0x00)) : $key;

            if ( $val == '{BINARY_PICTURE}' ) {
                continue;
            }
            $val = str_replace( "'", "", $val );
            if($GLOBALS['from_encoding']!="")$val = mb_convert_encoding($val, 'UTF-8', $GLOBALS['from_encoding'] );
            $a = $a + 1;
            if ( $a == 1 ) {
                $sql1 .='"'. strtolower($key).'"';
                $sql2 .="'" . trim( $val ) . "'";
            } else {
                $sql1 .=', "'.strtolower($key).'"';
                $sql2 .=", '".$val."'";
            }
        }
        $sql = "$sql1 $sql2)";
       
       
        if (pg_query($conn, $sql) === TRUE) {
            $i++;
           
            if ( $i % 10 == 0 ) {
   
                 echo '.';
            }
        } else {
/*             echo "Error SQL: ".$conn->error ." >> ".$sql; */
            if ($die_on_pgsql_error) {
                die;
            }
        }
  }

    echo "<strong>Table $table Telah selesai dimport</strong></br></br>";

}

echo '<script language="javascript">document.getElementById("information").innerHTML=" Proses selesai"</script>';


?>

</body>
</html>


config-postgre.php
 
<!DOCTYPE html>
<?php

$db_uname = 'postgres';
$db_passwd = '********';
$db_name = 'layar_biru';
$db_host = 'localhost';

$xbase_dir = 'D:\ACADEMIC\htdocs\dbf_input\files'; //lokasi dbf

$die_on_mysql_error = false; // when investigating errors, set this to true

$from_encoding=""; //Encoding of database, e.g. CP866 or empty, if convert is not required
 

Comments

Post a Comment

Popular posts from this blog

FPDF dengan CodeIgniter

Cetak Surat Keputusan Controller: <?php //File in controller named surat_keputusan.php defined('BASEPATH') OR exit('No direct script access allowed'); class Cetak_surat_keputusan extends CI_Controller { public function __construct()     {         parent::__construct();         $this->load->helper('url');         $this->load->database();                $this->db->select();         $this->db->from('surat.config_sk');                $query = $this->db->get();                 return $query->result();             } public function index() {      ...
1 comment
Read more

Layar Biru versi PHP Bagian 1 (file prefil_dbf.php)

file config.php <?php $db_uname = 'root'; $db_passwd = ''; $db_name = 'layar_biru'; //database yang dipilih $db_host = 'localhost'; $xbase_dir = 'D:\ACADEMIC\htdocs\layar_biru\files'; $die_on_mysql_error = false; // when investigating errors, set this to true $from_encoding=""; //Encoding of database, e.g. CP866 or empty, if convert is not required     file prefil.dbf   <?php include "config.php";            // please copy the config.sample.php and edit the correct fields include "classes/XBase/Table.php"; include "classes/XBase/Column.php"; include "classes/XBase/Record.php"; include "classes/DBFhandler.php"; use XBase\Table;  // Initializing vars ini_set( 'memory_limit', '2048M' ); set_time_limit( 0 ); $time_start = time(); $files = scandir($xbase_dir) or die ("Error! Could not open directory '$xbase_dir'."); $conn = new mysqli($db_host,...
Post a Comment
Read more

Converter dbf ke MYSQL

Banyak tool yang dipakai dan direkomendasikan orang untuk mengkonversi database , khususnya DBF ke MySQL. Ada yang menggunakan / menyarankan Navicat, CDBF for Linux , dbf2mysql, MySQL Migration Toolkit , dll. Bahkan ada pula yang mau bersusah-payah melakukannya dengan cara tradisionil dan ribet, yaitu dengan menggunakan MS Access: ekspor file ini, ganti syntax-nya menjadi sql, bla..bla..bla… dsb. Setelah menyimak semuanya, endingnya ternyata gak bikin happy. Meski begitu saya hargai effort mereka. Karena dari upaya mereka itulah kita menjadi tahu bahwa tool yang ini dan itu tidak cocok  dipakai. Tool yang saya pakai adalah Full Convert Enterprise (FCE). Komen saya terhadap tool ini cuma satu, keren! Kenapa? Karena bukan hanya source DBF saja yang bisa di-convert, tapi juga source lainnya (Interbase/Firebird, Oracle , Paradox, SQL Server, dll). Download: Full Convert Enterprise Full Version di sini (size 4.7 MB) Password: bayusibond Berikut adalah langkah-langkah m...
Post a Comment
Read more