Importing csv file to mysql in php - how to ignore empty rows?

Question

If I try to import a csv file, all the empty rows also get imported.

I have a form which takes a csv file

Following is the form action code

$file = $_FILES['file']['tmp_name'];
$allowedExts = array("csv");
$temp = explode(".", $_FILES["file"]["name"]);
$extension = end($temp);

if (!in_array($extension, $allowedExts)) {
        $this->session->set_flashdata('msg', 'Please upload a csv file format.');
    }
    else {
    $handle = fopen($file, "r");
    $c = 0;
    $flag = true;
    while(($filesop = fgetcsv($handle, 0, ",")) !== false)
    {
        if($flag) { $flag = false; continue; } //to skip the title row

        if($filesop != NULL ) {  //CONDITION TO CHECK EMPTY ROWS (DOES not work..)

       $data[] = array(
                'id'=>0, //uploader user id
                'field1' => (isset($filesop[14]) ? $filesop[14] : "" ),
                'field2' => (isset($filesop[15]) ? $filesop[15] : "" ),
                'field3' => (isset($filesop[1]) ? $filesop[1] : "" ),
                'field4' => (isset($filesop[13]) ? $filesop[13] : "" ),
                'field5' => (isset($filesop[6]) ? $filesop[6] : "" ),
                'field6' => (isset($filesop[2]) ? $filesop[2] : "" ),
                'field7' => (isset($filesop[5]) ? $filesop[5] : "" ),
                'field8' => (isset($filesop[3]) ? $filesop[3] : "" ),
                'field9' => (isset($filesop[4]) ? $filesop[4] : "" ),
            );
        }
    }
           echo "<pre>";
    print_r($data);
    exit;

Please help me fix , what can I do so that the empty rows do not get imported

also tried if(array( null ) != $filesop) condition, does not work What am i doing wrong please tell me i am not getting it — san, Dec 29 '16 at 04:47
I would say to try the if condition directly on one of your key as follow if(!empty($filesop[1])) can u show us a example of your CVS ? — Anyone_ph, Dec 29 '16 at 04:54
use `$empty_filesop = array_filter( array_map( 'trim', $filesop) ); if( !empty( $empty_filesop)){ ... }` — ArtisticPhoenix, Dec 29 '16 at 05:00
See this answer => http://stackoverflow.com/a/8329005/3155987 — Anyone_ph, Dec 29 '16 at 05:03

ArtisticPhoenix · Accepted Answer · 2016-12-29T06:19:26.733

1

I would use array_filter to remove empty elements from the array then check if anything is left.

while(($filesop = fgetcsv($handle, 0, ",")) !== false){

  $empty_filesop = array_filter( array_map('trim', $filesop));

  if( !empty( $empty_filesop ) ){

      .....
  }
}

Array map with trim, will trim the data to remove lines with empty space such as ,{space}, think of it like this:

  foreach( $empty_filesop as &$item ) 
     $item = trim( $item );

Also, be aware PHP treats several things as empty that you may not consider empty and will remove them with the standard array_filter, such as 0 as in ,0, or array( 0, 'blah' ) the element with a 0 would be removed. If you need more accuracy you can do something like this:

 $empty_filesop = array_filter(
        array_map('trim', $filesop),
        function( $item ){
            return strlen( $item ); //return the string length of $item ( 0 is false > 0 is true )
        }
 );

If you rely on the elements positions it will change if there are empty rows, so make a copy of it to check. For example use $empty_filesop that way you don't alter the actual row with the filter...

http://php.net/manual/en/function.array-filter.php

http://php.net/manual/en/function.array-map.php

http://php.net/manual/en/function.trim.php

For headers do it like this( I don't know the orignal keys ):

$headers = false;
$order = ['id'=>0, 'org3'=>'', 'org2'=>'', 'org1'=>''];
while(($filesop = fgetcsv($handle, 0, ",")) !== false)
{
    if(!$headers) { $headers = $filesop; continue; } 

    $empty_filesop = array_filter( array_map('trim', $filesop));

    if( !empty($empty_filesop)){
      //check for empty rows
      if( count( $headers ) == count($filesop) ) {  
       //check for rows with extra or missing delimiters.
        $data[] = array_replace( $order, array_combine( $headers, $filesop)); //combine with original headers, and replace the data in the 'order row' to re-order.
       }else{
           //row is missing or has extra delimiters.
       }
    }
}

edited Dec 29 '16 at 06:19

answered Dec 29 '16 at 05:05

ArtisticPhoenix

21,464
2
24
38

Thanks a lot!! array_filter with array_map worked. One more thing.. I have comma separated values in some of the rows in the csv file so fgetcsv($handle, 0, ",") does not import proper data in the table I Can use "\t" but for that ill have to save file in the text(tab delimited) format , I need a csv file only .. Can you please tell me how that can be done.. – san Dec 29 '16 at 05:27
if they have commas in the text PHP should handle it fine, if they are quoted like `"data with, comma", 1234, ...` . `fputcsv` will do that by default, it actually quotes fields with spaces too like `"field with spave", 1233,` Its the `$enclosure` part in the `fgetcsv` function, default `"` is quote. – ArtisticPhoenix Dec 29 '16 at 05:31
for that ill have to add double quotes in the csv file .. ? – san Dec 29 '16 at 05:34
Oh, and you don't need to do this `fgetcsv($handle, 0, ",")` you can just do `fgetcsv($handle)` those are the defaults. – ArtisticPhoenix Dec 29 '16 at 05:34
Depends what you mean by add, where is the CSV come from.. Both Excel and PHP `fputcsv` should do that by default. If you open it in a text editor like Notepad++ it should look like this `field,"field with space","field with, comma"`. PHP can even handle line returns as long as the field has quotes around it `"data \n foo"`, If the fields are not quoted then there is a problem with how it was written. – ArtisticPhoenix Dec 29 '16 at 05:35
u mean this way (from the above code in question) 'field1' => (isset($filesop[14]) ? fputcsv($handle,$filesop[14]) : "" )); – san Dec 29 '16 at 05:39
I don't see `fputcsv`only `fgetcsv` in the code above, if you want to write it in php you would use the data for the whole row `fputcsv( $row )`, where `$row = array( 0=>'', 1=>'' )` etc. Id suggest merging the data with an empty row when writing. – ArtisticPhoenix Dec 29 '16 at 05:42
can I use it this way 'field1' => (isset($filesop[14]) ? fputcsv($handle,$filesop[14]) : "" )); for each field of a row.. – san Dec 29 '16 at 05:43
no, because `fputcsv` adds a new line after it writes the data, so you wind up with each field on a separate line. – ArtisticPhoenix Dec 29 '16 at 05:46
@san - your over complicating it, use `array_combine` to merge the header row into the following "data" rows. – ArtisticPhoenix Dec 29 '16 at 05:50
Well as long as the headers are the same, otherwise you lost me at `fputcsv` ... lol – ArtisticPhoenix Dec 29 '16 at 05:56
actually I am not getting where to put fputcsv i tried putting before array_filter , then after the !empty condition .. it gives php errors.. – san Dec 29 '16 at 05:58
can you please tell me where i can put the fputcsv – san Dec 29 '16 at 06:04
bcs the example shows $fp = fopen('file.csv', 'w'); foreach ($list as $fields) { fputcsv($fp, $fields); } and I am using fgetcsv .. DO you mean replace fgetcsv with fputcsv .. I am sorry but m not getting it.. – san Dec 29 '16 at 06:06
I don't know what "example" your referring to but there is no `foreach` or `fputcsv` in your original post. I'm not psychic. – ArtisticPhoenix Dec 29 '16 at 06:09
sorry example from http://php.net/manual/en/function.fputcsv.php Please tell me where i can put fputcsv – san Dec 29 '16 at 06:10
`fputcsv` writes, `fgetcsv` reads. I'm still lost as to what you are asking. What I was saying is if the file was created with `fputcsv` it will quote text with commas by default. If the source of the file was something else, I don't know what to say. the comma issue is a problem with the input file, not how PHP reads it. – ArtisticPhoenix Dec 29 '16 at 06:11
That won't help, because PHP will read it wrong to begin with. So re-writing it with data that was read wrong, wont help. See what I am saying. The file should have been written with the correct format, the best bet is open it in Excel, and then save it as a CSV file. That should fix it. – ArtisticPhoenix Dec 29 '16 at 06:16
you mean csv (dos) or csv (comma delimited) – san Dec 29 '16 at 06:20
Open it with Excel, and then save it. Excel will put quotes around the fields it needs to. But it may not read it correct either. There is no easy way to fix that, that I know of. – ArtisticPhoenix Dec 29 '16 at 06:21
how do I resolve it ..? I can put "\t" as delimiter but then how do I save it in that format (tab delimited) – san Dec 29 '16 at 06:23
You can't easly, what are you going to do replace all the commas with tabs, how will you know they are not in one field or another. Say you have 5 fields, `1,2,3,4,5,5,5,5` how do you know its this `1,2,3,4,"5,5,5,5"` and not `1,2,3,"4,5", "5,5,5"`. There is no way to Know that. It could be `"1,2,3,4",5,5,5,5` ... see what I am saying. – ArtisticPhoenix Dec 29 '16 at 06:27
aah not all commas with tabs but separate all fields with tabs.. but how can i do that. I mean using tab as the delimiter for fields – san Dec 29 '16 at 06:39
Same answer, the commas are the fields ( comma delimited ). How can you change the fields without changing the commas? It makes no sense. – ArtisticPhoenix Dec 29 '16 at 06:42

score 0 · Answer 2 · answered Dec 29 '16 at 04:50

0

Change

if ($filesop != null)

To

if ($filesop !== array(null)) // note the triple equals

Look at the return values section of the docs: http://php.net/manual/en/function.fgetcsv.php

answered Dec 29 '16 at 04:50

Matt Altepeter

956
1
8
18

I tried printing $filesop in while loop I did not get null for empty rows instead i got an array as Array ( [0] => [1] => [2] => [3] => [4] => [5] => [6] => [7] => [8] => [9] => [10] => [11] => [12] => [13] => [14] => ) Is there anything which is not proper in the code.. – san Dec 29 '16 at 04:56
Are there any invisible characters in the array? – Matt Altepeter Dec 29 '16 at 04:59
why m i not getting null for empty row.. can you please check my code – san Dec 29 '16 at 04:59
how do i check that bcs if I print the array it gives the array as in the above comment .. – san Dec 29 '16 at 05:02
@san - this is because your file has the delimiters in it for example `,,,,` becomes `array(0=>'', 1=>'',2=>'', ... )`. So you have to remove the empty elements and then check, as in my answer. – ArtisticPhoenix Dec 29 '16 at 05:21

Importing csv file to mysql in php - how to ignore empty rows?

2 Answers2