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;\"> </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
<!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;\"> </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