skip the header lines when importing CSV file

[avia767]

Hello, I have CSV data to import, I'm wondering how skip the 5th first lines of the header of my CSV file, I want to start reading my CSV file from line 6.

 

Here is my CSV 

1-  "THE HEADER","THE HEADER","THE HEADER","THE HEADER","THE HEADER",

2-  "THE HEADER","THE HEADER","THE HEADER","THE HEADER","THE HEADER",

3-  "THE HEADER","THE HEADER","THE HEADER","THE HEADER","THE HEADER",

4-  "THE HEADER","THE HEADER","THE HEADER","THE HEADER","THE HEADER",

5-

6- "MY DATA", "MY DATA", "MY DATA", "MY DATA", "MY DATA", 

7- "MY DATA", "MY DATA", "MY DATA", "MY DATA", "MY DATA", 

8- "MY DATA", "MY DATA", "MY DATA", "MY DATA", "MY DATA", 

 

 

My PHP code

<?php
$conn = mysql_connect ("localhost","root","theboss") or die (mysql_error());
mysql_select_db ("big",$conn);
$curr_date = date('d.m');
//echo $curr_date;


if(isset($_POST['submit2']))
{
$file = $_FILES ['file']['tmp_name'];
$handle = fopen ($file, "r");


$fileop = fgetcsv ($handle, 1000, ","); 
while (($fileop = fgetcsv ($handle, 1000, ",")) !==false)
{




$date =  $fileop[0];
$leg =  $fileop[1];
$route =  $fileop[2];
$cars_type =  $fileop[3];
$car_reg =   $fileop[5];
$pax =  $fileop[8];
$std =  $fileop[10];




      
if (stripos($date ,$curr_date) || $date =="") 
{
$date = date('Y/m/d');
$sql = mysql_query ("INSERT INTO mvt_day (date, leg, route, cars_type, car_reg, pax, std) VALUES ('$date' , '$leg', '$route', '$car_type' , '$car_reg' , '$pax', '$std')");
} 
}
fclose($handle);
}




?>

 

[AbraCadaver]

Several ways to do it.  With the existing code it's probably easiest to set a counter:

$c = 0;
while (($fileop = fgetcsv ($handle, 1000, ",")) !==false)
{
   $c++;
   if($c <= 5) { continue; }

[litebearer]

presuming each line in your file terminates with a return, you might look here...

http://www.php.net/manual/en/function.array-slice.php

[avia767]

Perfect, it works !!! now I want to read this CSV without the la last line and without knowing the line number of the file ?

[AbraCadaver]

presuming each line in your file terminates with a return, you might look here...

http://www.php.net/manual/en/function.array-slice.php

Assuming that it's in an array yes.  So to go along with the follow-up post:

 

Perfect, it works !!! now I want to read this CSV without the la last line and without knowing the line number of the file ?

$lines = file($file);
$lines = array_slice($file, 5, count($file)-6);

foreach($lines as $line) {
   $columns = str_getcsv($line);
   // do stuff with $columns array
}

Edited June 8, 2013 by AbraCadaver

[AbraCadaver]

Obviously $lines = array_slice($file, 5, count($file)-6); should be $lines = array_slice($lines, 5, count($lines)-6);

[avia767]

Obviously $lines = array_slice($file, 5, count($file)-6); should be $lines = array_slice($lines, 5, count($lines)-6);

 

Hello your solution is great, actually I am haveing other considerations, I would like to slice my array as follow;

 

from row 6 to skip the header to the last blue line for each day I upload.

 

My CSV file

"dateCSV", " data1", "data2".,,,,,
" ", "data", " data ",,,,
" ", "data", " data ",,,,
" ", "data", " data ",,,,
" ", "data", " data ",,,,                  <----- this is the last blue line
"dateCSV +1 day", " data1", "data2".,,,,, 
" ", "data", " data ",,,,
" ", "data", " data ",,,,
" ", "data", " data ",,,,
" ", "data", " data ",,,,
" ", " ", " ",,,,,

for each day, before uploading, I want for example IF dateCSV == Current Date, consider all the blue part 

 

any suggestion ??

 

Thank you in advance.

Edited June 16, 2013 by avia767

[litebearer]

what have you tried?

[avia767]

what have you tried?

 

I might have a CSV file to upload like this with two or more date (wich is bad file, to avoid..)

 

"Wed 18.06", " data1", "data2".,,,,,
" ", "data", " data ",,,,
" ", "data", " data ",,,,
" ", "data", " data ",,,,
" ", "data", " data ",,,,
"Tue 19.06", " data1", "data2".,,,,,
" ", "data", " data ",,,,

I only need from users file like this 

 

"Wed 18.06", " data1", "data2".,,,,,
" ", "data", " data ",,,,
" ", "data", " data ",,,,
" ", "data", " data ",,,,
" ", "data", " data ",,,,

I mean the first column has to contain only the current date and null (" ")

 

To insure that, I need to set condition before any INSTERT and UPDATE into my DB is  :

 

here is the code

....
$datecsv  = $fileop[0];
$legcsv  = $fileop[1];
$routecsv  = $fileop[2];

if (($datecsv == $curr_date) AND ($datecsv == null))
               {              
                       $sql_insert = mysql_query(" INSERT INTO test (date,leg,route) 
                       VALUES ('$curr_date', '$legcsv', '$routecsv')");                                                                     
               }
               else
               {
                       echo 'Please select a dailly CSV file';
               }

it doesn't INSERT any row ???

Edited June 18, 2013 by avia767

[DavidAM]

 

if (($datecsv == $curr_date) AND ($datecsv == null))

it doesn't INSERT any row ???

 

It can not be equal to ANYTHING and also be NULL at the same time. You need to use OR there -- and I would use empty($datecsv) instead of equal null.

 

However, this is NOT going to solve the problem with the "bad file" since it will skip ONLY the row in the middle with the different date, the rest of the rows will be blank. If you want to process this file, you will have to capture any non-empty date column that you find, then process all the following rows as that date, until you find another non-empty date column (which you will capture and so on).

[avia767]

It can not be equal to ANYTHING and also be NULL at the same time. You need to use OR there -- and I would use empty($datecsv) instead of equal null.

 

However, this is NOT going to solve the problem with the "bad file" since it will skip ONLY the row in the middle with the different date, the rest of the rows will be blank. If you want to process this file, you will have to capture any non-empty date column that you find, then process all the following rows as that date, until you find another non-empty date column (which you will capture and so on).

 

I got it, Thank you, here is the code I tried, it works

if (($datecsv == $curr_date) OR ($datecsv == null))
{ 
$sql_insert = mysql_query ("INSERT INTO test(date, leg, route) 
VALUES ('$curr_date', '$legcsv', '$routecsv') "); 
}
elseif (!($datecsv == $curr_date)) 
{
break; 
}

Now I need to add my last condition wich is ;

 

SQL update if data existe in my DB based on my daily CSV file uploaded, 

or

SQL INSERT if data not exist.

in my case I cannot use ON DUPLICATE KEY UPDATE, because any of my columns are UNIQUE !! no KEY no ODKU !

I did tried ON DUPLICATE KEY UPDATE id=id, it doesn't work for me.

 

I tried the code below but it doesn't work :

 

while (($fileop = fgetcsv ($handle, 1000, ",")) !==false)
{
$datecsv  = $fileop[0];
$legcsv  = $fileop[1];
$routecsv  = $fileop[2]; 
$train_typecsv = $fileop[3]; 

  if (($datecsv == $curr_date) OR ($datecsv == null))
  { 
    $result = mysql_query("SELECT * FROM test WHERE date='$curr_date'"); 
    if (mysql_fetch_row($result) > 0) 
    {
       $sql_update = mysql_query (" UPDATE test SET train_type = '$train_typecsv'        WHERE route = $routecsv ");
    }
    else 
    {
       $sql_insert = mysql_query ("INSERT INTO test(date, leg, route, train_route)       VALUES ('$curr_date', '$legcsv', '$routecsv', '$train_typecsv') ");
    }
  }
  elseif (!($datecsv == $curr_date)) 
  {
   break; 
  }

Edited June 21, 2013 by avia767

[DavidAM]

Now I need to add my last condition wich is ;

 

SQL update if data existe in my DB based on my daily CSV file uploaded, 

or

SQL INSERT if data not exist.

in my case I cannot use ON DUPLICATE KEY UPDATE, because any of my columns are UNIQUE !! no KEY no ODKU !

 

I did tried ON DUPLICATE KEY UPDATE id=id, it doesn't work for me.

There is no condition for the ON DUPLICATE KEY phrase. It will trigger if ANY unique index on the table would be duplicated.

 

If there is no uniqueness in the data, how do you know if the data already exists?

 

 

I tried the code below but it doesn't work :

You are checking if the date exists in the table but then updating based on the route. Without any other condition on the UPDATE, you will be updating ALL records for that route regardless of the date.

 

Your INSERT statement references a column that does not exist (or the UPDATE does) -- train_type vs. train_route.

 

Your test to see if the data already exists is not correct. mysql_fetch_row returns an array. The comparison might work, but you are not always going to get the correct result. The recommended method is to use SELECT COUNT(*) FROM ..., then execute the query, fetch the row, and check the value of the count.

 

 

You need to define what determines if the data already exists. Is there a combination of columns that define the uniqueness? If there is, then you can define a composite index and use the ON DUPLICATE KEY phrase. If not, then we need to know exactly what you are trying to accomplish so we can recommend an appropriate course of action.

[avia767]

ok

Edited June 21, 2013 by avia767

[avia767]

thank you very much DavidAM, I got the right solution, I have set a combination in my DB wich are unique.