import file data di file *.csv jadi *.sql

script di bawah ini berfungsi untuk meng-konversi file berformat csv menjadi file berformat sql…

tinggal copy, lalu paste ke dalam file .php
simpan di dokumen root…
sesuaikan dengan konfigurasi anda
lalu jalankan di browser

———————————————————-

$host = “localhost”;
$databasename = “test”;
$databasetable = “test”;
$user =”root”;
$pass = “”;
$fieldseparator = “,”;
$lineseparator = “\n”;
$csvfile = “file.csv”;
/********************************/
/*apa anda ingin menambahkan field kosong di depan record?
/*ini berguna jika anda memiliki table dengan field pertama berupa auto_increment integer
/*dan file csv tidak mempunyai field kosong di awal record
/*ubah nilai $addauto jadi 1 untuk iya, dan 0 untuk tidak..PERHATIAN:jangan ubah jadi 1 jika anda tidak yakin
/********************************/
$addauto = 0;
/********************************/

//untuk menyimpan hasilnya ke dalam file, ubah nilai $save jadi 1
//buat file .sql di dalam folder
/********************************/
$save = 1;
$outputfile = “output.sql”;
/********************************/

if(!file_exists($csvfile)) {
echo “File not found. Make sure you specified the correct path.\n”;
exit;
}

$file = fopen($csvfile,”r”);

if(!$file) {
echo “Error opening data file.\n”;
exit;
}

$size = filesize($csvfile);

if(!$size) {
echo “File is empty.\n”;
exit;
}

$csvcontent = fread($file,$size);

fclose($file);

$con = @mysql_connect($host,$user,$pass) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

$lines = 0;
$queries = “”;
$linearray = array();

foreach(split($lineseparator,$csvcontent) as $line) {

$lines++;

$line = trim($line,” \t”);

$line = str_replace(“\r”,””,$line);

/************************************
baris ini mengabaikan special character. hapus jika sudah ada special character di dalam file csv
************************************/
$line = str_replace(“‘”,”\'”,$line);
/*************************************/

$linearray = explode($fieldseparator,$line);

$linemysql = implode(“‘,'”,$linearray);

if($addauto)
$query = “insert into $databasetable values(”,’$linemysql’);”;
else
$query = “insert into $databasetable values(‘$linemysql’);”;

$queries .= $query . “\n”;

@mysql_query($query);
}

@mysql_close($con);

if($save) {

if(!is_writable($outputfile)) {
echo “File is not writable, check permissions.\n”;
}

else {
$file2 = fopen($outputfile,”w”);

if(!$file2) {
echo “Error writing to the output file.\n”;
}
else {
fwrite($file2,$queries);
fclose($file2);
}
}

}

echo “Found a total of $lines records in this csv file.\n”;

?>
———————————————————-

Advertisements

2 responses to “import file data di file *.csv jadi *.sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s