Cara Input Data dari File Excel Ke Mysql DB

Males juga kalo input data dari excel ke database dengan cara input satu persatu, pada kali ini saya akan menyempatkan waktu buat share salah satu tools andalan saya buat input data yang banyak dari excel ke database (Mysql)
Buat dulu Database-nya, namanya bebas :

SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS `tbl_agen`; CREATE TABLE `tbl_agen` (   `id_agen` int(11) NOT NULL AUTO_INCREMENT,   `nama_agen` varchar(99) DEFAULT NULL,   `alamat_agen` varchar(99) DEFAULT NULL,   `kota_agen` varchar(99) DEFAULT NULL,   `kontak_agen` varchar(99) DEFAULT NULL,   PRIMARY KEY (`id_agen`) ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `tbl_agen`;

CREATE TABLE `tbl_agen` (

`id_agen` int(11) NOT NULL AUTO_INCREMENT,

`nama_agen` varchar(99) DEFAULT NULL,

`alamat_agen` varchar(99) DEFAULT NULL,

`kota_agen` varchar(99) DEFAULT NULL,

`kontak_agen` varchar(99) DEFAULT NULL,

PRIMARY KEY (`id_agen`)

) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=latin1;

Kemudian download library excel reader :
http://code.google.com/p/php-excel-reader/
Setelah itu simpan filenya bersampingan dengan file “import_excel.php”
Script import_excel.php

<!DOCTYPE  html><html><head><meta name="viewport" content="width=device-width, initial-scale=1.0"  /><title> Cara Input Data dari File Excel Ke Database</title></head><body><fieldset>        <div style="font:9px/24px  Verdana,Arial,Helvetica,sans-serif;">            <form method="post" enctype="multipart/form-data"  name="upload" action="">                <fieldset>                    <label>Silakan Pilih File Excel:  </label>                        <input name="userfile" type="file"  class="btn">                                                 <input type="submit" name="upload"  value="import" onclick="return confirm('Apakah Anda yakin dengan data  ini?')"  class="btn btn-primary" />  </fieldset></form></div></fieldset></body></html><?php error_reporting(E_ERROR | E_PARSE); if(isset($_POST['upload'])) { // koneksi ke database class database {  private $dbHost = "HOST_DATABASE";   private $dbUser = "USER_DATABASE";  private $dbPass ="PASSWORD_DATABASE";  private $dbName = "NAMA_DATABASE";  function connectMySQL() {  mysql_connect ($this->dbHost, $this->dbUser, $this->dbPass);  mysql_select_db ($this->dbName) OR DIE ("Database Tidak Ada! ");  /*if (@mysql_connect) {   echo "koneksi berhasil";  }  */    } }  $db = new database(); $db->connectMySQL();  include "excel_reader2.php"; $data = new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']); $baris = $data->rowcount($sheet_index=0); $sukses = 0; $gagal = 0;  for ($i=2; $i<=$baris; $i++) {  $id_agen = $data->val($i,1);   $nama_agen = $data->val($i,2);   $alamat_agen = $data->val($i,3);  $kota_agen = $data->val($i,3);  $kontak_agen = $data->val($i,3);    /*Query SQL*/  $query = "INSERT INTO tbl_agen values  ('$id_agen','$nama_agen','$alamat_agen','$kota_agen','$kontak_agen')";  $hasil = mysql_query($query);  if($hasil) $sukses++;  else $gagal++; }  //tampilkan report hasil import  echo "<h3> Proses Import Berhasil</h3>";  echo "<p>Jumlah data sukses di import: ".$sukses."<br>";  echo "Jumlah data gagal di import: ".$gagal."<p>"; }  ?>

<!DOCTYPE html>

<html>

<head>

<title> Cara Input Data dari File Excel Ke Database</title>

</head>

<body>

<label>Silakan Pilih File Excel: </label>

</fieldset>

</form>

</div>

</fieldset>

</body>

</html>

<?php

error_reporting(E_ERROR | E_PARSE);

if(isset($_POST['upload']))

{

// koneksi ke database

class database {

private $dbHost = "HOST_DATABASE";

private $dbUser = "USER_DATABASE";

private $dbPass ="PASSWORD_DATABASE";

private $dbName = "NAMA_DATABASE";

function connectMySQL() {

mysql_connect ($this->dbHost, $this->dbUser, $this->dbPass);

mysql_select_db ($this->dbName) OR DIE ("Database Tidak Ada! ");

/*if (@mysql_connect) {

echo "koneksi berhasil";

}

$db = new database();

$db->connectMySQL();

include "excel_reader2.php";

$data = new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);

$baris = $data->rowcount($sheet_index=0);

$sukses = 0;

$gagal = 0;

for ($i=2; $i<=$baris; $i++) {

$id_agen = $data->val($i,1);

$nama_agen = $data->val($i,2);

$alamat_agen = $data->val($i,3);

$kota_agen = $data->val($i,3);

$kontak_agen = $data->val($i,3);

/*Query SQL*/

$query = "INSERT INTO tbl_agen values ('$id_agen','$nama_agen','$alamat_agen','$kota_agen','$kontak_agen')";

$hasil = mysql_query($query);

if($hasil) $sukses++;

else $gagal++;

}

//tampilkan report hasil import

echo "<h3> Proses Import Berhasil</h3>";

echo "<p>Jumlah data sukses di import: ".$sukses."<br>";

echo "Jumlah data gagal di import: ".$gagal."<p>";

}

Catatan : filetype harus .*xls jangan .*xlsx ataupun format lainnya
sumber:http://rahmansurya.net/cara-input-data-dari-file-excel-ke-database/

Cara Input Data dari File Excel Ke Mysql DB

Cara Input Data dari File Excel Ke Mysql DB

0 Response to " Cara Input Data dari File Excel Ke Mysql DB"

Post a Comment