Link to home
Start Free TrialLog in
Avatar of Panos
PanosFlag for Germany

asked on

LOAD DATA LOCAL INFILE problem

Hello experts.
I need help to import a large csv file with 2 milion rows.
I m using the Mysql Workbench and i realized that i had to wait more than 15 hours for a file with 1 milion rows using the Table Data Import Wizard. After googling i found following link that should do the work very fast:
 Import Large CSV files to MySQL In a Minute.
The result is not as expected. Strings have the ' ' marks , integers are getting 0 value, primary key values are wrong.
Below is sample data and sql , php code (i m sorry but i have cfm knowledge and not php )
Create table sql function:
CREATE TABLE IF NOT EXISTS `car_specification_value` (
  `id_car_specification_value` int(8) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `value` varchar(255) NOT NULL,
  `unit` varchar(255) DEFAULT NULL COMMENT 'Unit',
  `id_car_specification` int(8) NOT NULL COMMENT 'Specification',
  `id_car_trim` int(8) NOT NULL COMMENT 'Trim',
  `date_create` int(10) unsigned DEFAULT NULL,
  `date_update` int(10) unsigned DEFAULT NULL,
  `id_car_type` int(8) NOT NULL,
  PRIMARY KEY (`id_car_specification_value`),
  KEY `id_car_type` (`id_car_type`)
) ENGINE=MyISAM AUTO_INCREMENT=35379476 DEFAULT CHARSET=utf8 COMMENT='Specification value';

Open in new window

csv sample data:
'id_car_specification_value','value','unit','id_car_specification','id_car_trim','date_create','date_update','id_car_type'
'7278893','10','litre','52','12743','1400481796',NULL,'1'
'7278894','1582','kg','34','12743','1400481796',NULL,'1'
'7420802','4',NULL,'4','18896','1402348875','1464379335','1'
'7420803','1825','mm','6','18896','1402348876',NULL,'1'
'7420804','4535','mm','5','18896','1402348875','1464379335','1'
'7420805','1370','mm','7','18896','1402348876',NULL,'1'
'7420806','2620','mm','8','18896','1402348877',NULL,'1'

Open in new window

php file:
Skip to content
Personal
Open source
Business
Explore
Pricing
Blog
Support
This repository

2
16

    13

sanathp/largeCSV2mySQL
Code
Issues 0
Pull requests 1
Projects 0
Pulse
Graphs
largeCSV2mySQL/csv2sql.php
6a29279 on 7 Jun 2014
@sanathp sanathp Php Script to Import CSV to Mysql
124 lines (109 sloc) 4.11 KB

<html>
<head>
<title> csv2 sql</title>
<link rel="stylesheet" href="//netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap.min.css">
</head>
<body>
<br>
<h1> CSV to Mysql </h1>
<p> This Php Script Will Import very large CSV files to MYSQL database in a minute</p>

</br>
<form class="form-horizontal"action="http://carbay.selfip.com/helpfiles/upload%20large%20csv/csv2sql.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>
</div>

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

<h3> Facing Problems ? Some of the reasons can be the ones shown below </h3>
1) Check if the table to which you want to import is created and the datatype of each column matches with the data in csv<br>
2) If fields in your csv are not separated by commas go to Line 117 of php file and change the query<br>
3) If each tuple in your csv are not one below other(i.e not seperated by a new line) got line 117 of php file and change the query<br>

</html>

    Contact GitHub API Training Shop Blog About 

    © 2016 GitHub, Inc. Terms Privacy Security Status Help 

Open in new window

Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Try this..

LOAD DATA LOCAL INFILE 'YourFileName.csv'
INTO TABLE YourtableName
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

Open in new window

Avatar of Panos

ASKER

Hi Pawan Kumar Khowal
Can you write the complete query like in the php file because i m getting a syntax error if i copy paste only the lines 3 and 4 from your code
mysqli_query($cons, '
        LOAD DATA LOCAL INFILE "'.$file.'"
        INTO TABLE '.$table.'
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\n'
')or die(mysql_error());

Open in new window

Try like..

$filename = $_FILES['csv']['YourFileName'];
$import = "LOAD DATA LOCAL INFILE '" . $filename . "' INTO TABLE YourtableName FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ";
mysql_query($import) or die(mysql_error());

Open in new window

Avatar of Panos

ASKER

I m sorry but i m unable to use your code because i don t know php language to make the changes for values like pw table db a.s.o.
Can you please either use the code in the posted php file in the question or include the form data in the php query?
Avatar of Julian Hansen
Try using the HEREDOC notation instead. The single / double quotes are tripping you up

$query = <<< QUERY
LOAD DATA LOCAL INFILE "{$file}"
INTO TABLE {$table}
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
QUERY;
mysqli_query($cons, $query) or die(mysql_error());

Open in new window

Avatar of Panos

ASKER

Hi expert Julian Hansen
I deleted the first row in the csv file and  tested your code.
Attached a screenshot with the imported data in the db table
User generated image
I must also say that using the standard Mysql Import Table Wizard from Mysql Workbench i have no problems but i m shure that i have to wait more than 25 hours.
Take a look at the default settings o the wizard
User generated image
Are you doing this to save time or because you need the code to do this often?

I use SQLYog which has a Load Local function - I use for all my data imports - haven't tried with Workbench - as far as I know it uses the same Mysql functionality as you are using in the LOAD LOCAL query.
Avatar of Panos

ASKER

I m doing this to save time. I will not use it often.
I downloaded SQLYog and i m testing it.
The result is the same. I have googled a little more and i think i m near to the solution.
As you can see the csv data row is like:
'7278893','10','litre','52','12743','1400481796',NULL,'1'
All values except NULL have marks left and right.
On this link mysql-Load data the user has a similar problem.
I altered the last posted query to:
$query = <<< QUERY
LOAD DATA LOCAL INFILE "{$file}"
INTO TABLE {$table}
FIELDS 
 TERMINATED BY ','
 ENCLOSED BY "'"
 ESCAPED BY ''
LINES TERMINATED BY '\n'
QUERY;

Open in new window

Now the inserted data are ok but the new problem is that instead of 1.907.347 records i get only 953674 records (The half of them).
I think the query isn t 100% right.
SOLUTION
Avatar of Panos
Panos
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Panos

ASKER

I did use the default settings that s why i could not solve my Problem. It is  really a greate and very helpful tool.