Filter a CSV file using php

Question

I've got a csv file being delivered via ftp twice a day. I'm trying to write a php file to open this file, filter it by the category column, delete rows which don't have a certain term/terms in then save the file.

I've tried lots of variants of snippets of code found on the internet but can't for the life of me figure out how to get it to work.

I tried using a variant of the code found in this question but couldn't make it work in my scenario

Filter a csv file by a word or text using php

If it helps the csv file I am trying to edit is located here: http://accurateav.co.uk/sahara/saharafeed.csv

<?php
$file  = fopen('saharafeed.csv', 'r');

// You can use an array to store your search words, makes things more flexible.
// Supports any number of search words.
$words = array('casio');    
// Make the search words safe to use in regex (escapes special characters)
$words = array_map('preg_quote', $words);
$regex = '/'.implode('|', $words).'/i';

while (($line = fgetcsv($file)) !== FALSE) {  
    list($ExportDate, $ItemCode, $Manufacturer, $Model, $ProductName, $DealerPrice, $Stock, $RRP, $Category, $ShortDesc, $LongDesc, $Weightkg, $EAN, $NonStock, $LargePictureURL, $SpecSheet, $HiResPhoto1, $HiResPhoto2) = $line;

    if(preg_match($regex, $Category)) {
        echo "$ExportDate, $ItemCode, $Manufacturer, $Model, $ProductName, $DealerPrice, $Stock, $RRP, $Category, $ShortDesc, $LongDesc, $Weightkg, $EAN, $NonStock, $LargePictureURL, $SpecSheet, $HiResPhoto1, $HiResPhoto2<br />\n";
    }
}

?>

This is the code I have used so far to filter the category to only show products in the Casio category however when i run the script no results are shown even though there are numerous products under the casio category.

Answer 1

I think your searching the wrong column. casio is inside the manufacturer column. You must search there instead. Consider this example:

$i = 0;
$manufacturer_key = null;
$needles = array('casio', 'nec', 'hitachi');
$results = array();
$columns = array();
if(($handle = fopen('saharafeed.csv', 'r')) !== false) {
    while(($data = fgetcsv($handle, 4096, ',')) !== false) {
        if($i == 0)  {
            // sets the key where column to search
            $columns = $data;
            $i++; $manufacturer_key = array_search('Manufacturer', $data);
        } else {
            foreach($needles as $needle) {
                if(stripos($data[$manufacturer_key], $needle) !== false) {
                    $results[] = $data;
                } 
            }   
        }
    }
    fclose($handle);
}

array_unshift($results, $columns);

echo '<pre>';
print_r($results);
echo '</pre>';

Sample Output for casio:

Array
(
    [0] => Array
        (
            [0] => ExportDate
            [1] => ItemCode
            [2] => Manufacturer
            [3] => Model
            [4] => ProductName
            [5] => DealerPrice
            [6] => Stock
            [7] => RRP
            [8] => Category
            [9] => ShortDesc
            [10] => LongDesc
            [11] => Weightkg
            [12] => EAN
            [13] => NonStock
            [14] => LargePictureURL
            [15] => SpecSheet
            [16] => HiResPhoto1
            [17] => HiResPhoto2
        )

    [1] => Array
        (
            [0] => 11/06/2014
            [1] => 1610044
            [2] => NEC
            [3] => 60003221
            [4] => NEC  NP14ZL
            [5] => 999
            [6] => 0
            [7] => 1348.65
            [8] => Projectors Accessories
            [9] => 2.97-4.79:1 Zoom Lens
            [10] => Replacement 2.97–4.79:1 Zoom Lens compatible with the following products:

... and many more

Put the results back into csv format:

$fp = fopen('file.csv', 'w');

foreach ($results as $row) {
    fputcsv($fp, $row);
}

fclose($fp);

Answer 2

Remove the line matching by a regular expression (by eliminating one containing digital   characters, at least 1 digit, located at the end of the line):

sed '/[0-9/][0-9]*$/d' filename.txt