Alex Alex - 1 month ago 12
MySQL Question

Cannot import CSV to Mysql properly

I am importing data from CSV file into Mysql database. The code works fine but it doesn't insert first line of csv file.

<?php
if(isset($_POST["Import"]))
{
//First we need to make a connection with the database
$host='localhost:3311'; // Host Name.
$db_user= 'root'; //User Name
$db_password= 'root';
$db= 'compared'; // Database Name.
$connect=mysqli_connect($host,$db_user,$db_password,$db);
echo $filename=$_FILES["file"]["tmp_name"];
if($_FILES["file"]["size"] > 0)
{
$csv_file = $filename; // Name of your CSV file
$csvfile = fopen($csv_file, 'r');
$theData = fgets($csvfile);
$i = 0;
while (!feof($csvfile))
{
$csv_data[] = fgets($csvfile, 1024);
$csv_array = explode(",", $csv_data[$i]);
$insert_csv = array();
$insert_csv['0'] = $csv_array[0];
$insert_csv['1'] = $csv_array[1];
$insert_csv['2'] = $csv_array[2];
$insert_csv['3'] = $csv_array[2];
$insert_csv['4'] = $csv_array[2];
$query = "INSERT INTO reviews(id,userid,title, review, rating)
VALUES('".$insert_csv['0']."','".$insert_csv['1']."','".$insert_csv['2']."','". $insert_csv['3']."','".$insert_csv['4']."')";
$n=mysqli_query($connect,$query );
$i++;
}
fclose($csvfile);
echo "File data successfully imported to database!!";
}
}

?>


<form enctype="multipart/form-data" method="post" role="form">
<div class="form-group">
<label for="exampleInputFile">File Upload</label>
<input type="file" name="file" id="file" size="150">
<p class="help-block">Only Excel/CSV File Import.</p>
</div>
<button type="submit" class="btn btn-default" name="Import" value="Import">Upload</button>
</form>


this is the data

1,2,abc,abc,3
2,3,abc,abcd,5
11,2,abc,abc,3
22,3,abc,abcd,5


It inserts from second line and skips the first line. Can anyone fix it?

Answer

You perform the first fgets outside the loop, and then immediately afterwards another one in the beginning of the loop, thus skipping the first result.

A do-while construct should solve your problem:

$theData = fgets($csvfile);
do {
   // Extract data from $theData

   // Insert into the database

   // Get the next line:
   $theData = fgets($csvfile);
} while (!feof($csvfile));
Comments