Products csv file preprocessor

[bigal_nz2]

Hi Guys,

Hope someone here could help point me in the right direction for this little project.

I want to get a online computer store up and running.

I get files once weekly from about 12 different distributors. I might get ASUS video cards from one distributor and Albatron video cards from another. The problem I have when importing these files to oscommerce with easy populate is that one distributor might call video cards "VGA Cards" and another might call them "Graphics Card".

When I import the list of video cards I end up with two catagories, when in reality there should of course only be one. This happens on a variety of things, not just video cards.

I need to do some preprocessing to get the files having uniform groups before they are imported.

I think I can do this with php and some arrays, but its been a while since i have done php and a outline of the structure or even a few lines of code would be a great help.

All the files that I get from the distributors are in csv format.

Thanks heaps in advance,

-Al

[sid]

thats somewhat hard to answer without knowing more about what you are trying to do...
a switch statement comes to mind possibly...

switch ($category) {
    case "VGA cards":
    case "Graphics Card":
        $newCat = "Video Cards";
        break;

case "...":
    //...
    break;

default:
    $newCat = $category;
    break;
}

[bigal_nz2]

Ok, let me be more specific. A few lines from a distributor file might look like:

Make, Model, Price, Catagory,
Asus, Mdl123, 12.99, Video Card,
Asus, Mdl124, 14.99, Video Card,
Albatron, Mdl117, 124.99, Video Card,
Intel, Xeon, 17.99, Processor,
AMD, X64, 15.99, Processor,
Seagate, ST123A, 14.99, IDE Disk Drive
Seagate, ST124A, 15.99, IDE Disk Drive

Now I might use catagories Graphics Card, Hard Disk and CPU instead of Video Card, IDE Disk Drive and Processor. So I need to go through the file and on each line see if any of "Video Card" "Processor" "IDE Disk Drive" exist in column 4.

If they do then it needs to be replaced with my catagories: Graphics Card, CPU or Hard Disk.

If I did the import from all the distributors without this standardization I would end up with more than one catagory for the same product.

Cheers

-Al

[bigal_nz2]

It probably wants to read in from the source file and write the output to a new file, writing only lines where it finds a match?

HTH

-Al

[sid]

a way would be to could read the csv with [man]file/man or [man]file_get_contents/man.

heres a rough outline of what you could do then:

lets say $line holds a line of item-info:

$items = explode(",", $line);   //you can also accomplish that with a csv function

switch (trim($items[3])) {    //this element holds the category info
    case "VGA cards":
    case "Video Card":
        $items[3] = "Graphics Card";
        break;

case "Hard Drive":
case "HDD":
    $items[3] = "Hard Disk";
    break;

case "Processor":
    $items[3] = "CPU";
    break;

default:
//if a category is "Graphics Card", "Hard Disk" or "CPU" it gets "assigned" here
//just like any categories that weren't caught by any of the cases above.
    $items[3] = trim($items[3]);
    break;
} 

[NogDog]

<?php
// search phrase => replacement phrase
$categories = array(
   'Video Card' => 'Graphics Card',
   'Processor' => 'CPU',
   'CPU Chip' => 'CPU',
   'IDE Disk Drive' => 'Hard Drive',
   'Fixed Disk' => 'Hard Drive'
);
// open up source CSV file
$csv = "test.csv";   // CSV file to be read and modified
$fh = fopen($csv, 'r') or die("Could not open file");
// initialize data array
$data = array();
// loop through each line in source file
while(($line = fgetcsv($fh)) !== FALSE)
{
   if(isset($line[3]))
   {
      // look for search values and replace them when found:
      $line[3] = str_replace(array_keys($categories), $categories, $line[3]);
   }
   $data[] = $line;  // add to data array
}
fclose($fh);
// write data array to new CSV file:
$newfile = 'data_fixed.csv';
$fh = fopen($newfile, 'w') or die("Could not open new file");
foreach($data as $line)
{
   // use implode to create comma-separated line:
   fwrite($fh, implode(',', $line)."\n");
}
fclose($fh);
echo "<p>Revised data saved as $newfile.</p>";
?>

NOTE: you could use fputcsv() instead of fwrite() with implode() if you are running PHP 5.1 or later.

[bigal_nz2]

NogDog,

I like the way you have done it. Would you mind adding comments to your code?

Thanks

-Al

[NogDog]

bigal.nz wrote:

NogDog,

I like the way you have done it. Would you mind adding comments to your code?

Thanks

-Al

Done

[bigal_nz2]

Hmm, still getting my head around the code.

If it doesnt find the search string on the line does it still add the line to the new data array?

It should only add the line to the new data array if it finds the search term on the new line.

-Al

[as22607]

if it is doing the replacement but still have slight problems maybe set it up to re output the correct name back ot the correct name but not sure what it is or isnt doing as you never really spefic if oyu did apogolise for misunderstanding

[NogDog]

bigal.nz wrote:

Hmm, still getting my head around the code.

If it doesnt find the search string on the line does it still add the line to the new data array?

yes

It should only add the line to the new data array if it finds the search term on the new line.

That was not originally stated as a requirement, so I coded under the assumption that you wanted to replicate the entire source CSV file, but with the category changed for those with certain values. If you only want the changed rows, then you could add the count parameter to the preg_replace, check to see if it is non-zero, and if so write the line to the new data.

while(($line = fgetcsv($fh)) !== FALSE)
{
   if(isset($line[3]))
   {
      // look for search values and replace them when found:
      $line[3] = str_replace(array_keys($categories), $categories, $line[3], -1, $count);
      if($count)
      {
         $data[] = $line;  // add to data array
      }
   }
} 

[bigal_nz2]

Thanks for all your help.

Another thing I need to do is read in the headers in first row like:

price,desc,group

and replace with things like:

v_price_1, v_description_1, v_catagory_1

I was gonna use a loop like:

for($i=0; $i < count($line); $i++) { $line[$i] = $headers[$line[$i]]; } would be one way

where $headers is an array like:

$headers = array(
'Desc' => 'v_categories_name_1',
'Desc' => 'v_products_description_1'
etc
);

Is there a better way?

Cheers

-Al

[bigal_nz2]

sorry :

$headers = array(
'group' => 'v_categories_name_1',
'Desc' => 'v_products_description_1'
etc
);

[include_me]

I have used your code and it doesnt seem to be making any of the changes. It just outputs another CSV which is exactly the same..

http://www.carose.co.uk/csv-test/phones.csv
http://www.carose.co.uk/csv-test/csv-convert.php5

im using:

<?php 
// search phrase => replacement phrase 
$make = array( 
   'Nokia Mobile Phones' => 'Nokia', 
   'Motorola Mobile Phones' => 'Motorola', 
   'Sony Ericsson Mobile Phones' => 'Sony Ericsson', 
   'Samsung Mobile Phones' => 'Samsung', 
   'Qtek Mobile Phones' => 'Qtek', 
   'LG Mobile Phones' => 'LG', 
   'I-Mate Mobile Phones' => 'I-Mate', 
   'Blackberry Mobile Phones' => 'Blackberry', 
   'Handspring / PalmOne Mobile Phones' => 'Handspring / PalmOne', 
); 
// open up source CSV file 
$csv = "phones.csv";   // CSV file to be read and modified 
$fh = fopen($csv, 'r') or die("Could not open file"); 
// initialize data array 
$data = array(); 
// loop through each line in source file 
while(($line = fgetcsv($fh)) !== FALSE) 
{ 
   if(isset($line[1])) 
   { 
      // look for search values and replace them when found: 
      $line[1] = str_replace(array_keys($make), $make, $line[1]); 
   } 
   $data[] = $line;  // add to data array 
} 
fclose($fh); 
// write data array to new CSV file: 
$newfile = 'data_fixed.csv'; 
$fh = fopen($newfile, 'w') or die("Could not open new file"); 
foreach($data as $line) 
{ 
   // use implode to create comma-separated line: 
   fwrite($fh, implode(',', $line)."\n"); 
} 
fclose($fh); 
echo "<p>Revised data saved as <a href='$newfile'>$newfile</a>.</p>"; 
?> 

If someone could point me in the right direction as to why its not working i would be so grateful!:queasy:

Thanks,
Chris