comment 0

Import BIG csv to database

Do you facing problem when import big csv file into database ?

[fruitful_pbar][fruitful_bar type=”progress-bar-success” width=”69%” stripped=”true” active=”true”][/fruitful_bar][/fruitful_pbar]

already waiting since a hours and still have no progress in loading bar ?

Import big csv to mysql database sometimes takes more time. If you’re using import feature for phpmyadmin it will waste your time.


Sponsored links


I have a solution that will take a minute, eg. for 100 megabytes csv file only take 36 seconds. I wrote it using PHP , for command line and UI.

this is the codes

[fruitful_tabs type=”default” width=”100%” fit=”false”]
[fruitful_tab title=”UI”]

 
<html>
<head>
<title>cs2sql</title>
<link rel="stylesheet" href="https://bootswatch.com/superhero/bootstrap.min.css"/>
</head>
<body>
<br />
<h1> CSV to Mysql </h1>
<p> This script will import big CSV file to MYSQL database in a minute</p>
 
 
<form class="form-horizontal"action="index.php" method="post">
    <div class="form-group">
        <label for="mysql" class="control-label col-xs-2">Mysql Server address (or)<br />Host name</label>
		<div class="col-xs-3">
        <input type="text" class="form-control" name="mysql" id="mysql" placeholder=""/>
		</div>
    </div>
	<div class="form-group">
        <label for="username" class="control-label col-xs-2">Username</label>
		<div class="col-xs-3">
        <input type="text" class="form-control" name="username" id="username" placeholder=""/>
		</div>
    </div>
	<div class="form-group">
        <label for="password" class="control-label col-xs-2">Password</label>
		<div class="col-xs-3">
        <input type="text" class="form-control" name="password" id="password" placeholder=""/>
		</div>
    </div>
	<div class="form-group">
        <label for="db" class="control-label col-xs-2">Database name</label>
		<div class="col-xs-3">
        <input type="text" class="form-control" name="db" id="db" placeholder=""/>
		</div>
    </div>
 
	<div class="form-group">
        <label for="table" class="control-label col-xs-2">table name</label>
		<div class="col-xs-3">
        <input type="name" class="form-control" name="table" id="table"/>
		</div>
    </div>
	<div class="form-group">
        <label for="csvfile" class="control-label col-xs-2">Name of the file</label>
		<div class="col-xs-3">
        <input type="name" class="form-control" name="csv" id="csv"/>
		</div>
		eg. MYDATA.csv
    </div>
	<div class="form-group">
	<label for="login" class="control-label col-xs-2"></label>
    <div class="col-xs-3">
    <button type="submit" class="btn btn-primary">Upload</button>
	</div>
	</div>
</form>
 
 
</body>
 
< ?php 
 
if(isset($_POST['username'])&&isset($_POST['mysql'])&&isset($_POST['db'])&&isset($_POST['username']))
{
$sqlname=$_POST['mysql'];
$username=$_POST['username'];
$table=$_POST['table'];
if(isset($_POST['password']))
{
$password=$_POST['password'];
}
else
{
$password= '';
}
$db=$_POST['db'];
$file=$_POST['csv'];
$cons= mysqli_connect("$sqlname", "$username","$password","$db") or die(mysql_error());
 
$result1=mysqli_query($cons,"select count(*) count from $table");
$r1=mysqli_fetch_array($result1);
$count1=(int)$r1['count'];
//If the fields in CSV are not seperated by comma(,)  replace comma(,) in the below query with that  delimiting character 
//If each tuple in CSV are not seperated by new line.  replace \n in the below query  the delimiting character which seperates two tuples in csv
// for more information about the query http://dev.mysql.com/doc/refman/5.1/en/load-data.html
mysqli_query($cons, '
    LOAD DATA LOCAL INFILE "'.$file.'"
        INTO TABLE '.$table.'
        FIELDS TERMINATED by \',\'
        LINES TERMINATED BY \'\n\'
')or die(mysql_error());
 
$result2=mysqli_query($cons,"select count(*) count from $table");
$r2=mysqli_fetch_array($result2);
$count2=(int)$r2['count'];
 
$count=$count2-$count1;
if($count>0)
echo "Success";
echo "<b> total $count records have been added to the table $table </b> ";
}
else{
echo "Mysql Server address/Host name ,Username , Database name ,Table name , File name are the Mandatory Fields";
}
 
?>
<h3> Instructions </h3>
1.  Keep this php file and Your csv file in one folder <br />
2.  Create a table in your mysql database to which you want to import <br />
3.  Open the php file from your localhost server <br />
4.  Enter all the fields  <br />
5.  click on upload button  
</html>

[/fruitful_tab]
[fruitful_tab title=”CLI”]

< ?php
# Import CSV with PHP CLI
# Rosdyana Kusuma - rosdyana.kusuma@gmail.com
# effectively for big csv file :) 

class csv_main{
	function __construct($mysql, $username, $pass, $database, $table, $filename){
		if(empty($mysql) || empty($username)
			|| empty($database) || empty($table) || empty($filename)){
			help();
			exit;
		}
		$this->csv2sql($mysql, $username, $pass, $database, $table, $filename);
	}
 
	function csv2sql($mysql, $username, $pass, $database, $table, $filename){
			$time_start = microtime(true);
			$sqlname=$mysql;
			$username=$username;
			$table=$table;
			if(isset($pass)){
				$password=$pass;
			}
			else{
				$password= '';
			}
			$db=$database;
			$file=$filename;
			$cons= mysqli_connect("$sqlname", "$username","$password","$db") or die(mysql_error());
 
			$result1=mysqli_query($cons,"select count(*) count from $table");
			$r1=mysqli_fetch_array($result1);
			$count1=(int)$r1['count'];
			//If the fields in CSV are not seperated by comma(,)  replace comma(,) in the below query with that  delimiting character 
			//If each tuple in CSV are not seperated by new line.  replace \n in the below query  the delimiting character which seperates two tuples in csv
			// for more information about the query http://dev.mysql.com/doc/refman/5.1/en/load-data.html
			mysqli_query($cons, '
				LOAD DATA LOCAL INFILE "'.$file.'"
					INTO TABLE '.$table.'
					FIELDS TERMINATED by \',\'
					LINES TERMINATED BY \'\n\'
			')or die(mysql_error());
 
			$result2=mysqli_query($cons,"select count(*) count from $table");
			$r2=mysqli_fetch_array($result2);
			$count2=(int)$r2['count'];
 
			$count=$count2-$count1;
			if($count>0){
				$time_end = microtime(true);
				$execution_time = ($time_end - $time_start);	
				print "Success\n";
				print "Total $count records from $filename have been added to the table $table\n";
				print 'Total Execution Time: '.round( $execution_time, 1, PHP_ROUND_HALF_UP).' seconds';
			}
	}
}
	function help(){
		echo("
                 ______             _ 
                (_____ \           | |
  ____  ___ _   _ ____) ) ___  ____| |
 / ___)/___) | | /_____/ /___)/ _  | |
( (___|___ |\ V /_______|___ | | | | |
 \____|___/  \_/(_______|___/ \_|| |_|
                                 |_|  
			");
		print "\nHow to use it:\n";
		print "csv2sql.php hostname username pass database table filename  \n";
	}
if (isset($argv[1]) && isset($argv[2]) && isset($argv[3]) && isset($argv[4]) && isset($argv[5]) && isset($argv[6])) {
	$csv_to_sql = new csv_main($argv[1], $argv[2], $argv[3], $argv[4], $argv[5], $argv[6]);
}
else{
	help();
}
 
?>

[/fruitful_tab]
[/fruitful_tabs]

Instructions

[fruitful_tabs type=”vertical” width=”100%” fit=”false”]
[fruitful_tab title=”UI”]

1. Keep this php file and Your csv file in one folder

2. Create a table in your mysql database to which you want to import

3. Open the php file from your localhost server

4. Enter all the fields

5. click on upload button [/fruitful_tab]
[fruitful_tab title=”CLI”]
1. Open your console in folder with this PHP CLI script

2. Make sure PHP binary already registered in your system path

3. Run the PHP CLI script using this command , php csv2sql.php hostname username pass database table filename

4. eg. php csv2sql.php localhost root “” testDB testTable big.csv

5. fiola :v
[/fruitful_tab]
[/fruitful_tabs]

result
csv2sql

Have a nice day !

Leave a Reply

Your email address will not be published. Required fields are marked *