Upload file CSV dan load ke Database

hehe, kalo mao nyoba2 ni ke na menarik juga, lu bisa upload data baris dalam file CSV ke dalam table database cuman dalam sekali klik...

Upload file CSV dan load ke Database

#1 BIKIN DATABASE ma table na

lu bisa bikin file PHP na, create-db.php

code na ke gini:

Code:

<?

$con=mysql_connect("localhost", "root", "");

//bikin database
if (mysql_query("CREATE DATABASE ujicoba", $con))
{
echo "<P>Database terbentuk</P>";
}
else
{
echo "<P>Error membuat DATABASE: <font color=red>" . mysql_error() ." </font></P>";
}

$db=mysql_select_db("ujicoba", $con);

//bikin table
if(mysql_query("CREATE TABLE `nama_temen` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`nama` VARCHAR( 255 ) NOT NULL ,
`profesi` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ;", $con))
{
echo "<P>Table terbentuk</P>";
}
else
{
echo "<P>Error membuat TABLE: <font color=red>". mysql_error() ." </font></p>";
}

mysql_close();

?>

#2 Naaaa, sekarang kita bikin dolo file CONFIG na

bwat file config.php

isi code na ke gini:

Code:

<?

##=====================================##
## Definisi & Konfigurasi ##
##=====================================##

//definisi userconnection untuk koneksi utama
define ("_serverDB", "localhost"); //host server
define ("_userDB", "root"); //user dbase
define ("_passDB", ""); //password dbase
define ("_DBname", "ujicoba"); //dbase utama

define ("_TBLname", "nama_temen"); //table

//definisi root site dan direktori utama website
define ("rootPage", "".$_SERVER["DOCUMENT_ROOT"].""); //root site ( www / htdocs )
define ("folderSite", "/upload-csv/"); //direktori utama website, lokasi file ini berada (upload-csv.php)

define ("_DIR", "lib/csv"); //direktori upload file, berada di dalam definisi folderSite

# individual file size limit - dalam bytes (102400 bytes = 100KB)
$max_file_size = 1024000; //maximal zise file

##=====================================##
## Fungsi - konek ##
##=====================================##

function do_connect()
{
   global $db_con;
   @$db_con = @mysql_connect(_serverDB, _userDB, _passDB) or die("Miss Connection $php_errormsg");
   if ($db_con) @mysql_select_db(_DBname) or die ("Warning: Gagal Nyelek Database,
Cek donk nama Database na");
   return $db_con;
}

?>

fungsi define diatas untuk mendefinisikan sebuah nilai yang akan di load value na lewat file yang dikehendaki

#3 Baru dah lu bikin file FORM lengkap dengan aksina, upload-csv.php

kode na ke gini:

Code:

<?

##============================================##
## ##
## upload file CSV dan load ke Database ##
## php builder : hairul azami [ dr.emi ] ##
##
## file code ini adalah bersifat opensource ##
## lu bebas ngedit na, tutorialna pun bebas ##
## lu boleh tampilin di web lu, asalkan kudu ##
## nyantumin link author na: www.dremi.info ##
## ##
##============================================##

## panggil file config.php
include ("config.php");

##=====================================##
## Eksekusi dimulai di sini ##
## Selamat mencoba ##
##=====================================##

if($_POST['mulaiSubmit'])
{
   ##posting data
$fileSize = $HTTP_POST_FILES['uFile']['size'][0]; //file size yg diupload
   $fileName = $HTTP_POST_FILES['uFile']['name'][0]; //nama file
$tmpFile = $HTTP_POST_FILES['uFile']['tmp_name'][0]; //nama temporary file
   $fileExt = strtolower(substr($fileName, -3)); //ekstensi file,
//misalkan nama_temen.csv (exktensinya csv)

   ##seleksi kesalahan inputan file
if(empty($fileName)) // kalo form file upload kosong
   {
   echo "<SCRIPT>alert(\"Form upload file CSV kosong\");history.go(-1);</SCRIPT>";
   return false;
   }
   else
{

// kalo file size > $max_file_size
if($fileSize > $max_file_size)
   {
   echo "<SCRIPT>alert(\"Ukuran file lebih besar dari ".$max_file_size." BYTES (".round(($max_file_size/1024),2)." KB)\");history.go(-1);</SCRIPT>";
   return false;
   }
   elseif($fileExt != "csv") // kalo tidak ber ekstensi CSV
   {
   echo "<SCRIPT>alert(\"Tipe / ekstensi file salah, harus ber ekstensi CSV\");history.go(-1);</SCRIPT>";
   return false;
   }
   else
   {

## kopi dolo ke $direktori
   if(@copy($tmpFile, _DIR."/$fileName"))
   {
   do_connect(); // konek dolo
   $query= "LOAD DATA INFILE
'".rootPage."".folderSite.""._DIR."/$fileName' replace
INTO TABLE "._TBLname." FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'" or die("Data gagal di load");
   $result = mysql_query($query);

   echo "<SCRIPT>alert(\"ngUpload Sukses !\");history.go(-1);</SCRIPT>";
   }
   else
   {
   echo "<SCRIPT>alert(\"ngUpload Gagal !\");history.go(-1);</SCRIPT>";
   }
   }

}

}
else
{
   echo "<html><head><script type=\"text/javascript\" src=\"ajax/baca.js\"></script>
<title>Ayo ngupload</title></head><body><style>body,input { font-family: arial; font-size: 12px; }</style>";
echo "<H5>Upload CSV na dan load ke Database !</H5>";
   echo "<form action=\"\" enctype=\"multipart/form-data\" method=\"post\">
   File Browse:<BR>";
   echo "<input type=\"file\" name=\"uFile[]\" size=\"30\">";
   echo "<BR><BR>
   <input style=\"padding:10px; font-size: 18px;\" type=\"submit\" name=\"mulaiSubmit\" value=\"Upload !\">
   <input style=\"padding:10px; font-size: 18px;\" type=\"reset\" name=\"reset\" value=\"Reset !\">
   </form>
<p><a href=\"#readData\" title=\"Ajax Responsibility\" onClick=\"readData()\">Read Data Table</a> OR
<a href=\"$PHP_SELF\" title=\"ReLoad Page\">ReLoad</a></p>
<div id=\"queryData\"></div>
</body></html>";
}

?>

sample form diatas, gue terapin fungsi Ajax, cuma skedar variasi dowank, kalo lu ga mau ganti aja form na ke gini:

Code:

echo "<html><head>
<title>Ayo ngupload</title></head><body><style>body,input { font-family: arial; font-size: 12px; }</style>";
echo "<H5>Upload CSV na dan load ke Database !</H5>";
   echo "<form action=\"\" enctype=\"multipart/form-data\" method=\"post\">
   File Browse:<BR>";
   echo "<input type=\"file\" name=\"uFile[]\" size=\"30\">";
   echo "<BR><BR>
   <input style=\"padding:10px; font-size: 18px;\" type=\"submit\" name=\"mulaiSubmit\" value=\"Upload !\">
   <input style=\"padding:10px; font-size: 18px;\" type=\"reset\" name=\"reset\" value=\"Reset !\">
   </form></body></html>";

WOKE sekarang coba test pake http://localhost/upload-csv/upload-csv.php, tapi sebelumna, bikin dolo sample data CSV di Microsoft Excel:

abis ntu lu save dengan nama file sesuka lu, misal na: nama_temen.csv

Cara ngesave na di Ms Excel, pili menu File > Save As > Type na : Comma Delimited

Selamat mencoba dah..hahahahaha Cheesy