CSV2MySQL convert from CSV file into MySQL database

Posted by on 5 November, 2008
12 Comments
This item was filled under [ Code Snippet, Javascript, MySQL, PHP ]

Bagaimana cara convert dari CSV ke dalam database MySQL menggunakan PHP.
Silahkan lihat langkah2 dibawah ini:

How to convert from CSV file into MySQL database using PHP?
Plus how to create a table and fields into database with PHP?
Here it is..
We need 3 files php, and 2 file jpg (the jpg is just for menu).

index.php

<?php
// UPLOAD CSV CUSTOM
// Ver. 1.0
// by Jawaad
// http://www.nusansifor.com
// File: index.php
 
@session_start();
//@ob_start(); 
//@ob_implicit_flush(0); 
 
@error_reporting(E_ALL ^ E_NOTICE);
@ini_set('display_errors', true);
@ini_set('html_errors', false);
@ini_set('error_reporting', E_ALL ^ E_NOTICE);
 
define('jin', true);
 
// CONECT DATABASE
@mysql_connect("localhost", "root", "") OR die ("NOT CONNECT DATABASE");
@mysql_select_db("nama_database") OR die ("CONNECTED, BUT NO DATABASE");
 
 
 
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
  "http://www.w3.org/TR/html4/strict.dtd">
<html xmlns='http://www.w3.org/1999/xhtml' lang='en' xml:lang='en:us'>
  <head>
    <meta http-equiv="Content-type" content="text/html; charset=utf-8">
    <title>UPLOAD CSV CUSTOM</title>
    <style type="text/css" media="screen">
      body {
        background: #fff;
        color: #888;
        font: 100% georgia,times,serif;
      }
      h1 {
        font-weight: normal;
        margin: 0;
        padding: 0 0 .5em 0;
      }
      h2 {
        font-weight: normal;
        margin: 0;
        padding: 0 0 .5em 0;
      }
 
/*- Menu Tabs J--------------------------- */
 
    #tabsJ {
      float:left;
      width:100%;
      background:#fff;
      font-size:93%;
      line-height:normal;
	  border-bottom:1px solid #24618E;
      }
    #tabsJ ul {
	margin:0;
	padding:10px 10px 0 50px;
	list-style:none;
      }
    #tabsJ li {
      display:inline;
      margin:0;
      padding:0;
      }
    #tabsJ a {
      float:left;
      background:url("tableftJ.gif") no-repeat left top;
      margin:0;
      padding:0 0 0 5px;
      text-decoration:none;
      }
    #tabsJ a span {
      float:left;
      display:block;
      background:url("tabrightJ.gif") no-repeat right top;
      padding:5px 15px 4px 6px;
      color:#24618E;
      }
    /* Commented Backslash Hack hides rule from IE5-Mac \*/
    #tabsJ a span {float:none;}
    /* End IE5-Mac hack */
    #tabsJ a:hover span {
      color:#FFF;
      }
    #tabsJ a:hover {
      background-position:0% -42px;
      }
    #tabsJ a:hover span {
      background-position:100% -42px;
      }		
    </style>
    <script type="text/javascript">
function addElement() {
  var ni = document.getElementById('myDiv');
  var numi = document.getElementById('theValue');
  var num = (document.getElementById("theValue").value -1)+ 2;
  numi.value = num;
  var divIdName = "my"+num+"Div";
  var newdiv = document.createElement('div');
  newdiv.setAttribute("id",divIdName); //  + num + 
  newdiv.innerHTML = "<input type=hidden name=\"kode[" + num + "]\" value=\"" + num + "\"><input type=text name=\"field" + num + "\"> <select name=\"tipe" + num + "\"><option>TEXT</option><option>LONG-TEXT</option><option>NUMERIC</option><option>NUMERIC-2-DECIMAL</option><option>DATE</option><option>DATE-TIME</option></select> <a href=\"javascript:;\" onclick=\"removeElement(\'"+divIdName+"\')\">[- del]</a><p>";
  ni.appendChild(newdiv);
}
 
function removeElement(divNum) {
  var d = document.getElementById('myDiv');
  var olddiv = document.getElementById(divNum);
  d.removeChild(olddiv);
}
</script>
</head>
 
<body>
      <h1>UPLOAD CUSTOM CSV INSERT TO MYSQL</h1>
<div id="tabsJ">
  <ul>
    <li><a href="?do=upload_csv" title="Upload CSV"><span>Upload CSV</span></a></li>
    <li><a href="?do=create_table" title="Create New Table"><span>Create New Table</span></a></li>
  </ul>
</div>
<?php
switch($_REQUEST["do"]) {
	case "upload_csv":
		include ("upload_csv.php");
	break;
	case "create_table":
		include ("create_table.php");
	break;
 
	default:
		include ("upload_csv.php");
}
 
?>
 
 
	 </body>
</html>

upload_csv.php

<?php
// UPLOAD CSV CUSTOM
// Ver. 1.0
// by Jawaad
// http://www.nusansifor.com
 
if(!defined('jin')) { die('nice try..'); }
 
if($_POST[upload_csv]=="1") {
	$table_name="`".$_POST[table_name]."`";
	if(!$_FILES["file_csv"]["name"]) {
		echo "<script>alert('Please Browse File.. CSV.');</script>";
	} else if(substr($_FILES["file_csv"]["name"], -3) != "csv") {
		echo "<script>alert('File ".substr($_FILES["file_csv"]["name"],-3)." not support. Please use CSV extension');</script>";
	} else if($_FILES["file_csv"]["size"]>=1672864) {
		echo "<script>alert('File size is too big. File max 1.5 MB');</script>";
	} else {
		// READ FIELD FROM TABEL
		$sql_tabel = mysql_query("SELECT * FROM ".$table_name."");
		$numfields = mysql_num_fields($sql_tabel);
		$field_name = "";
		for ($i=0; $i < $numfields; $i++) {
			$get_field = mysql_field_name($sql_tabel, $i);
			if($i == 1) {
				$field_name .= "`".$get_field."`";
			} elseif($i > 1) {
				$field_name .= ", `".$get_field."`";
			}
		}
		copy($_FILES["file_csv"]["tmp_name"], "tmp/".$_FILES["file_csv"]["name"]);
	  	$ifile = fopen("tmp/".$_FILES["file_csv"]["name"],"r");
	  	$no=1;
		$jum_value = $numfields - 1;
	  	while (($file_list = fgetcsv($ifile, 1000, ",")) !== FALSE) {
			if($no > 1) {
				$field_value = "";
				for ($i=0; $i < $jum_value; $i++) {
						if($i == 0) {
							$field_value .= "'".$file_list[$i]."'";
						} else {
							$field_value .= ", '".$file_list[$i]."'";
						}
				}
					$sql = "INSERT INTO ".$table_name." (".$field_name.") VALUES (".$field_value.")";
					$ex = mysql_query($sql) OR die ("<br>ERROR, DATA TIDAK BISA MASUK...<br>");
					if(!$ex) {
						echo "<script>alert(\"Sorry, Error....\");location.href='?do=upload_csv';</script>";
					} else {
						echo "<script>alert(\"Succesed.\");location.href='?do=upload_csv';</script>";
					}
			}
			$no++;
		}
		fclose($ifile);
		unlink("tmp/".$_FILES["file_csv"]["name"]) OR die ("Cannot DEL file tmp/".$_FILES["file_csv"]["name"]);
	}
}
?>
 
<form method="post" enctype="multipart/form-data">
<br><br>
<h2>Upload CSV</h2>
<table border="0" cellspacing="0" cellpadding="3">
<tr>
	<td valign="top">Table Name</td><td valign="top">: <input name="table_name"></td>
</tr>
<tr>
	<td valign="top">File CSV</td><td valign="top">: <input type="file" name="file_csv" value="1"></td>
</tr>
<tr>
	<td valign="top" colspan="2">
		<input type="hidden" name="upload_csv" value="1">
		<input type="submit" name="submit" value="UPLOAD .CSV">
	</td>
</tr>
</table>
</form>

create_table.php

<?php
// UPLOAD CSV CUSTOM
// Ver. 1.0
// by Jawaad
// http://www.nusansifor.com
// file: create table
 
if(!defined('jin')) { die('nice try..'); }
 
?>
 
<form method="post">
<br><br>
<h2>Create Table</h2>
Table Name: <input name="table_name">
<input type="hidden" value="0" id="theValue" /><a href="javascript:;" onclick="addElement();">+ Add field</a>
<p>
<div id="myDiv"> </div>
 
 
<input type="hidden" name="create_table" value="1">
<input type="submit" name="submit" value="Create Table">
</form>
 
 
<?php
if($_POST[create_table]=="1") {
	//$kode = array($_POST[kode]);
	$table_name = $_POST[table_name];
	echo "Check <b>".$table_name."</b><br>";
	//print_r($_POST[kode]);
	if($table_name != "" && $_POST[kode] != "") {
		foreach ($_POST[kode] as $field_arr) {
			$field =  $_POST["field".$field_arr.""];
			$tipe = $_POST["tipe".$field_arr.""];
				 if($tipe == "TEXT") {$db_tipe = "varchar(255),";}
			elseif($tipe == "NUMERIC") {$db_tipe = "bigint(20),";}
			elseif($tipe == "NUMERIC-2-DECIMAL") {$db_tipe = "float(10,2),";}
			elseif($tipe == "LONG-TEXT") {$db_tipe = "text NOT NULL,";}
			elseif($tipe == "DATE") {$db_tipe = "date NOT NULL,";}
			elseif($tipe == "DATE-TIME") {$db_tipe = "datetime NOT NULL,";}
			$the_fields .= "`" . $field . "` " . $db_tipe . "\n";
		}
		$sql = "
					CREATE TABLE IF NOT EXISTS `".$table_name."` (
						`id` bigint(20) NOT NULL auto_increment,
						".$the_fields."
						PRIMARY KEY  (`id`)
					) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
		";
		$ex = mysql_query($sql);
		if(!$ex) {
			echo "Sorry,... Error...<br>";
		} else {
			echo "Table  <b>".$table_name."</b> created.<br>";
		}
	} else {
		echo "Sorry,... Error... Please fill the table name and the fields...<br>";
	}
}
?>

Image files:

Download file lengkap:

  CSV2MySQL.V.1.0.rar (6.4 KiB, 1,863 hits)

semoga membantu :mrgreen:

Rate this topic:
1 Star2 Stars3 Stars4 Stars5 Stars (5 votes, average: 5.00 out of 5)
Loading ... Loading ...
Popularity: 6,212 views
Tagged with: [ , , , , ]
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Artikel Sejenis

12 Comments on “CSV2MySQL convert from CSV file into MySQL database”

  • adrian
    Mozilla Firefox Mozilla Firefox 3.5.2 <br /> Windows Windows XP
    4 Sep 2009 11:02

    eror mas upload csv-nya..ni errornya

    “Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource in C:\wamp\www\tes\upload_csv.php on line 20 Warning: copy(tmp/dsx464.csv): failed to open stream: No such file or directory in C:\wamp\www\tes\upload_csv.php on line 30 Warning: fopen(tmp/dsx464.csv): failed to open stream: No such file or directory in C:\wamp\www\tes\upload_csv.php on line 31 Warning: fgetcsv() expects parameter 1 to be resource, boolean given in C:\wamp\www\tes\upload_csv.php on line 34 Warning: fgetcsv() expects parameter 1 to be resource, boolean given in C:\wamp\www\tes\upload_csv.php on line 34
    ERROR, DATA TIDAK BISA MASUK…”

    Like or Dislike: Thumb up 0 Thumb down 0
  • adrian
    Mozilla Firefox Mozilla Firefox 3.5.2 <br /> Windows Windows XP
    4 Sep 2009 11:30

    mas mau tanya agi nih,,

    datanya ko g masuk yah???

    pas ddi display cuma ada id yang auto increment tanpa akhir

    mohon pencerahaannya

    Like or Dislike: Thumb up 0 Thumb down 0
  • Jawaad
    Mozilla Firefox Mozilla Firefox 3.5.2 <br /> Windows Windows XP
    4 Sep 2009 17:37

    @adrian, buat folder baru dengan nama “tmp” diletakkan sejajar dengan ketiga nama file php tersebut.

    Like or Dislike: Thumb up 0 Thumb down 0
  • sasa
    Mozilla Firefox Mozilla Firefox 3.5.5 <br /> Windows Windows XP
    8 Dec 2009 12:43

    saya coba masukin csv ke mysql , didalam table ada 4 field , tapi kenapa field terakhir selalu gak bisa masuk yach ??

    Like or Dislike: Thumb up 0 Thumb down 0
  • Jawaad
    PHP PHP
    8 Dec 2009 19:44

    @sasa, jumlah field pada database mysql dan jumlah kolom pada CSV harus sama.

    Like or Dislike: Thumb up 0 Thumb down 0
  • Vegy
    Mozilla Firefox Mozilla Firefox 3.5.7 <br /> Windows Windows XP
    10 Jan 2010 16:05

    mas saya sudah coba dan tabelnya mau ter insert, tapi isi tabelnya yang ter record tidak beraturan, gak beraturannya seperti gni :
    field di mysql jum 6 bgt juga di CSV file columnya ada 6.
    ketika di import colum ke 2 tabel mysql nya terisi semua isi row CSV atau row CSV nya menjadi satu pada row tabel MySQL, dan yang lainnya kosong. gmn itu.?
    apa ada kesalahan pada File CSV nya??

    Like or Dislike: Thumb up 0 Thumb down 0
  • Jawaad
    PHP PHP
    10 Jan 2010 23:47

    @Vegy, file CSV nya apakah sudah menggunakan separate by comma?

    Like or Dislike: Thumb up 0 Thumb down 0
  • Vegy
    Mozilla Firefox Mozilla Firefox 3.5.7 <br /> Windows Windows XP
    11 Jan 2010 10:04

    file CSV nya bulum separate by comma, tapi kalau sata import pake bawaan phpmyadmin berhasil semua row terisi sesuai data csv nya.
    bagai mana saya bisa mengubah file csv nya menjadi separate by comma.?

    Like or Dislike: Thumb up 0 Thumb down 0
  • Jawaad
    PHP PHP
    11 Jan 2010 18:49

    @Vegy, diwaktu save as CSV, pilih option seperate by comma.

    Like or Dislike: Thumb up 0 Thumb down 0
  • tin2
    Mozilla Firefox Mozilla Firefox 3.5.3 <br /> Windows Windows XP
    13 Nov 2010 14:11

    can you speak in english? i can not understand what are you saying… please help me. i need to convert excel into mysql in large amount of data in excel form.

    Like or Dislike: Thumb up 0 Thumb down 0
  • cruisine
    Mozilla Firefox Mozilla Firefox 3.6.12 <br /> Windows Windows XP
    28 Dec 2010 12:32

    mas…data berhasil masuk ke database..tapi datanya kosong..trus data yang masuk 1000 biji…padahal data sebenernya cuma 2 biji…gmn tuh…need ur help soon..

    Like or Dislike: Thumb up 0 Thumb down 0
  • Jawaad
    Mozilla Firefox Mozilla Firefox 3.6.8 <br /> Windows Windows XP
    21 Jun 2011 0:48

    @tin2, yes you can use it, just increase the php execute time.
    Sample:

    ini_set('max_execution_time', 1200); //1200 seconds = 20 minutes

    @cruisine, kok bisa seperti itu? coba dicek kembali file CSV nya dan format nya.

    Like or Dislike: Thumb up 0 Thumb down 0

Leave a Comment

Notify me of followup comments via e-mail. You can also subscribe without commenting.

«
»

Halaman ini di eksekusi dalam waktu 1.72 detik! (koneksi mayan bagus nih...)