CSV2MySQL convert from CSV file into MySQL database

by Jawaad · Published 5 November 2008 · Updated 28 November 2008

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>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

<?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">

<head>

<title>UPLOAD CSV CUSTOM</title>

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>

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>

<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>

<?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"]);

}

<h2>Upload CSV</h2>

<tr>

<td valign="top">Table Name</td><td valign="top">: <input name="table_name"></td>

</tr>

<tr>

</tr>

<tr>

</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>";
	}
}
?>

<?php

// UPLOAD CSV CUSTOM

// Ver. 1.0

// by Jawaad

// http://www.nusansifor.com

// file: create table

if(!defined('jin')) { die('nice try..'); }

<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>

</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, 3,600 hits, Updated: 14 April 2011)

semoga membantu

Tags: code csv Javascript mysql php

adrian says:

4 September 2009 at 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…”

Reply
adrian says:

4 September 2009 at 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

Reply
- Jawaad says:
  
  4 September 2009 at 17:37
  
  @adrian, buat folder baru dengan nama “tmp” diletakkan sejajar dengan ketiga nama file php tersebut.
sasa says:

8 December 2009 at 12:43

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

Reply
- Jawaad says:
  
  8 December 2009 at 19:44
  
  @sasa, jumlah field pada database mysql dan jumlah kolom pada CSV harus sama.
Vegy says:

10 January 2010 at 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??

Reply
- Jawaad says:
  
  10 January 2010 at 23:47
  
  @Vegy, file CSV nya apakah sudah menggunakan separate by comma?
Vegy says:

11 January 2010 at 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.?

Reply
- Jawaad says:
  
  11 January 2010 at 18:49
  
  @Vegy, diwaktu save as CSV, pilih option seperate by comma.
tin2 says:

13 November 2010 at 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.

Reply
cruisine says:

28 December 2010 at 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..

Reply
Jawaad says:

21 June 2011 at 00:48

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

PHP

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

1

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

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

Reply
dennisYo says:

26 June 2013 at 11:10

Terima Kasih gan, very helpfully
setelah saya coba formatnya harus csv(ms-dos)

Reply
sultan says:

26 March 2015 at 14:46

Makasih gan,hasilnya OK punya gan.

PHP

echo 'ok';

1

echo 'ok';

posting tutorial yg CRUD dong gan, keep it up gan!

Reply