Sebagai bahan pembelajaran import data CSV ke database ini; dibuat database dengan nama db_import(dalam hal ini menggunakan MySQL/MariaDB Database server) sebagai berikut:
CREATE DATABASE IF NOT EXISTS db_import;
USE db_import;
Membuat Tabel Penampung di MySQL
Dalam pembelajaran ini kita membuat sebuah tabel baru bernama data yang dibuat dalam database db_import yang sudah dibuat sebelumnya.
DROP TABLE IF EXISTS data;
CREATE TABLE IF NOT EXISTS data(
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
);
Membuat Data CSV
Sedangkan untuk keperluan percobaan import data dari file CSV ini dibuat file CSV dengan nama data.csv yang contoh isinya adalah sebagai berikut :
1,Gibbie,Mossop,gmossop0@disqus.com
2,Hazel,Sapsforde,hsapsforde1@dyndns.org
3,Chandal,Rizzardi,crizzardi2@bing.com
4,Morrie,Bernier,mbernier3@ehow.com
5,Honey,Doulden,hdoulden4@163.com
6,Susy,Fawcitt,sfawcitt5@redcross.org
7,Ailbert,Darracott,adarracott6@diigo.com
8,Rowney,Adamiak,radamiak7@spotify.com
9,Marjory,Blindermann,mblindermann8@com.com
10,Pooh,Maffioni,pmaffioni9@whitehouse.gov
...
998,Merla,Paskerful,mpaskerfulrp@amazon.co.jp
999,Juditha,Halcro,jhalcrorq@pagesperso-orange.fr
1000,Sally,Lamartine,slamartinerr@histats.c
contoh file CSV tersebut bisa diunduh [di sini], sebagai catatan : dalam file CSV tersebut, datanya disajikan tanpa memakai header (baris pertama langsung record pertama data)
Ada banyak cara untuk meng-import data dari file CSV ke tabel database MySQL, beberapa cara yang umum adalah :
Import Data Sederhana
Meletakkan file CSV yang akan diimport (data.csv) ke dalam database (db_import) dalam satu folder dengan script pengimportnya, lalu melalui script tersebut file CSV tersebut dibaca dan diparsing baris per baris dan di INSERT ke tabel dalam suatu looping/perulangan sesuai jumlah baris yang ada pada file CSV tersebut
<!DOCTYPE html>
<html>
<head>
<title>IMPORT CSV FILE TO MYSQL #1</title>
</head>
<body>
<?php
//-- konfigurasi koneksi ke server database
$dbhost='localhost';
$dbuser='root';
$dbpass='';
$dbname='db_import';
//-- membuat koneksi ke database server
$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
//-- Membuka file 'data.csv' dan membacanya
$handle= fopen('data.csv','r');
while(($data=fgetcsv($handle,100,','))!== FALSE){
$sql="INSERT INTO data VALUES('{$data[0]}','{$data[1]}','{$data[2]}','{$data[3]}')";
$db->query($sql); //-- melakukan import
}
fclose($handle); //Menutup CSV file
$db->close(); //Menutup koneksi ke database
echo "<br><strong>Import data selesai.</strong>";
?>
</body>
</html>
Menambahkan Fungsi Upload Data
Sebagai pengembangan dari script sebelumnya, agar file CSV yang akan diimport dapat ditentukan secara dinamis maka bisa ditambahkan fungsi untuk upload file CSV sebagai berikut :
<!DOCTYPE html>
<html>
<head>
<title>IMPORT CSV FILE TO MYSQL #2</title>
</head>
<body>
<?php
//-- konfigurasi koneksi ke server database
$dbhost='localhost';
$dbuser='root';
$dbpass='';
$dbname='db_import';
//-- membuat koneksi ke database server
$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
if (isset($_POST['submit'])) {//Script akan berjalan jika di tekan tombol submit..
//Script upload file csv..
if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
echo "<h1>" . "File ". $_FILES['filename']['name'] ." Berhasil di Upload" . "</h1>";
echo "<h2>Menampilkan Hasil Upload:</h2>";
readfile($_FILES['filename']['tmp_name']);
}
//Import uploaded file ke Database, Letakan dibawah sini..
$handle = fopen($_FILES['filename']['tmp_name'], "r"); //Membuka file dan membacanya
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$sql="INSERT INTO data VALUES('{$data[0]}','{$data[1]}','{$data[2\data[3]}')";
$db->query($sql);
}
fclose($handle); //Menutup CSV file
echo "<br><strong>Import data selesai.</strong>";
}else { ?>
<b>Silahkan masukan file csv yang ingin diupload</b><br />
<form enctype='multipart/form-data' action='' method='post'>
<input type='file' name='filename' size='100' /><br />
<input type='submit' name='submit' value='Upload' />
</form>
<?php
}
$db->close(); //Menutup koneksi ke database
?>
</body>
</html>
Mengoptimalkan Query INSERT
Pada script-script yang sebelumnya, request query INSERT dari PHP ke database MySQL dilakukan dalam perulangan sesuai jumlah baris dalam file CSV; dalam artian request query INSERT dari PHP ke MySQL dilakukan sebanyak jumlah baris yang terdapat pada file CSV. Hal ini tentu saja akan memboroskan resources dan traffic data antara webserver dengan database server. Dalam script berikut ini dilakukan sedikit perbaikan sehingga cukup dilakukan satu kali request query INSERT dari PHP ke database MySQL dengan menggunakan Extended Insert
<!DOCTYPE html>
<html>
<head>
<title>IMPORT CSV FILE TO MYSQL #2</title>
</head>
<body>
<?php
//-- konfigurasi koneksi ke server database
$dbhost='localhost';
$dbuser='root';
$dbpass='';
$dbname='db_import';
//-- membuat koneksi ke database server
$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
if (isset($_POST['submit'])) {//Script akan berjalan jika di tekan tombol submit..
//Script upload file csv..
if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
echo "<h1>" . "File ". $_FILES['filename']['name'] ." Berhasil di Upload" . "</h1>";
echo "<h2>Menampilkan Hasil Upload:</h2>";
readfile($_FILES['filename']['tmp_name']);
}
//Import uploaded file ke Database, Letakan dibawah sini..
$handle = fopen($_FILES['filename']['tmp_name'], "r"); //Membuka file dan membacanya
$sql="INSERT INTO data VALUES";
$values=array();
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$values[]="('{$data[0]}','{$data[1]}','{$data[2\data[3]}')";
}
$db->query($sql.implode(',',$values));
fclose($handle); //Menutup CSV file
echo "<br><strong>Import data selesai.</strong>";
}else { ?>
<b>Silahkan masukan file csv yang ingin diupload</b><br />
<form enctype='multipart/form-data' action='' method='post'>
<input type='file' name='filename' size='100' /><br />
<input type='submit' name='submit' value='Upload' />
</form>
<?php
}
$db->close(); //Menutup koneksi ke database
?>
</body>
</html>
Menggunakan LOAD DATA
Meskipun dalam script yang sebelumnya hanya dilakukan satu request query INSERT ke database, namun jika datanya banyak/besar maka bisa terjadi timeout dari PHP karena data dari file CSV tetap di baca satu persatu oleh PHP, kemudian digabungkan dalam variable array untuk dibuat string dari VALUES yang akan di INSER ke database. Selain proses-nya lama, juga memakan resources memory yg cukup besar untuk menampung data string sebelum dijalankan query INSERT-nya ke database.
Sebagai perbaikan maka bisa dibuatkan mekanisme import data ke database secara background proses dengan statement LOAD DATA INFILE, sehingga proses pembacaan data dari file dan INSERT ke database-nya dilakukan di level database, sedangkan PHP hanya digunakan untuk meng-upload file CSV yang akan di import ke database, dan menjalankan query LOAD DATA INFILE dari database servernya
<!DOCTYPE html>
<html>
<head>
<title>IMPORT CSV FILE TO MYSQL</title>
</head>
<body>
<form method='post' enctype='multipart/form-data'>
<input type="file" name="file" />
<input type="submit" name="importFile" value="IMPORT">
</form>
</body>
</html>
<?php
if(isset($_POST['importFile'])){
$MIME_types = array(
'text/x-comma-separated-values',
'text/comma-separated-values',
'application/octet-stream',
'application/vnd.ms-excel',
'application/x-csv',
'text/x-csv',
'text/csv',
'application/csv',
'application/excel',
'application/vnd.msexcel',
'text/plain'
);
if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'],$MIME_types)){
if(is_uploaded_file($_FILES['file']['tmp_name'])){
$dbhost='localhost';
$dbuser='root';
$dbpass='';
$dbname='test';
$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
$sql="LOAD DATA LOCAL INFILE '".addslashes($_FILES['file']['tmp_name'])."' "
."INTO TABLE data "
."FIELDS TERMINATED BY ',' "
."LINES TERMINATED BY '\n' ";
$db->query($sql) or
die("Fail to import data file to db, error number [".$db->errno."] : ".$db->error." {$sql}");
echo 'import data successfully';
}
}
}
