Alex Alex - 1 year ago 95
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.

//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.
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 );
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>
<button type="submit" class="btn btn-default" name="Import" value="Import">Upload</button>

this is the data


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

Answer Source

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));
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download