Solved

loop through CSV and update / delete columns

Posted on 2013-12-30
9
1,590 Views
Last Modified: 2016-03-02
hi all.

i get a csv every day from a 3rd party stock control system and i need to edit this csv to make the headings and cells match what magento requires to import each row as a valid product.

so i need to loop through this csv with a php while loop and some how target the columns with bad headings and delete those columns.

other columns might just need renaming

some cells might need updating to make their content valid.

the csv gets put to the hosting daily so i have already started to gather some code to get the thing working but am a bit lost now.

i think i just need to get the CSV in an array and then i can do stuff to the array. just not sure how i can edit the array and make it save as a new .CSV

here is what i have: if any one can help it would be appreciated.

p.s the CSV i get from the stock control system is very scruffy: some cols don't have headings when opening in open office but they seem to become new rows when i check the output of my code (from the array dump),

here is a sample of my csv (first 8 rows): only8rows.CSV


///////////////////////////////////////////////////
// this is how we get the latest CSV file name from the directory.
///////////////////////////////////////////////////
$dir = "./csv";         
$pattern = '/\.(csv|CSV)$/'; // check only file with these ext.          
$newstamp = 0;            
$latestFile = "";

if ($handle = opendir($dir)) {               
       while (false !== ($fname = readdir($handle)))  {            
         // Eliminate current directory, parent directory            
         if (preg_match('/^\.{1,2}$/',$fname)) continue;            
         // Eliminate other pages not in pattern            
         if (! preg_match($pattern,$fname)) continue;            
         $timedat = filemtime("$dir/$fname");            
         if ($timedat > $newstamp) {
            $newstamp = $timedat;
            $latestFile = $fname;
          }
         }
        }
closedir ($handle);



///////////////////////////////////////////////////
// get the csv in an array so we can edit the array
///////////////////////////////////////////////////

$array = $fields = array(); $i = 0;
$handle = @fopen("csv/$latestFile", "r");
if ($handle) {
    while (($row = fgetcsv($handle)) !== false) {
        if (empty($fields)) {
            $fields = $row;
            continue;
        }
        foreach ($row as $k=>$value) {
            $array[$i][$k] = $value;
        }
        $i++;
    }
    if (!feof($handle)) {
        echo "Error: unexpected fgets() fail\n";
    }
    fclose($handle);
}

var_dump($array);

Open in new window

0
Comment
Question by:helpchrisplz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39747514
PHP function fgetcsv() will read each row of the CSV file into an array.  You can process each row (array) individually.
0
 
LVL 1

Author Comment

by:helpchrisplz
ID: 39748399
hi ray, yes i was attempting to use fgetcsv()

but my poor knowledge of arrays lets me down:
i need to be able to get the csv in an array and then loop the array to get back the cols i need and then save them down to a new csv.

need to query the array for col heading of "Stock Number" and get back the cells that relate to that column and then put this column to a new csv

as am not good with arrays i have found some code and played with it:

this is what i have now:

 # Open the File.
    if (($handle = fopen("csv/$latestFile", "r")) !== FALSE) {
        # Set the parent multidimensional array key to 0.
        $nn = 0;
        while (($data = fgetcsv($handle, ",")) !== FALSE) {
            # Count the total keys in the row.
            $c = count($data);
            # Populate the multidimensional array.
            for ($x=0;$x<$c;$x++)
            {
                $csvarray[$nn][$x] = $data[$x];

                
            }

            $colB[] = $data[1];
            $nn++;
        }
        # Close the File.
        fclose($handle);
    }
    # Print the contents of the multidimensional array.
  
echo "<br><br><br> <h3>foreach (csvarray[0] as child)</h3> this outputs row 0 of the CSV. row 0 contains the col headings<br><br>";

foreach($csvarray[0] as $child) {
    //this prints out the col headings
   echo $child . "\n";
}


echo "<br><br><br> <h3>foreach (csvarray as key => value)</h3> seems to just count?<br><br>";
foreach ($csvarray as $key => $value) {
    // just counts?
 echo $key;
}


echo "<br><br><br> <h3>var_dump colB</h3> this gives us just cells from column B<br><br>";
var_dump($colB);


echo "<br><br><br> <h3>foreach(csvarray as innerArray) {</h3> this seems to output everything inside the CSV<br><br>";
foreach($csvarray as $innerArray) {
 foreach($innerArray as $value) {
    // seems to output everything
  echo $value;
}
}

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39748435
This data set is a mess, and it may take a lot of programming to tease it apart into something useful.  For example, the top row of the CSV has 309 columns, but the next row has 590 columns.  Not sure what to make of that!

I'll try to give you a code sample that shows the first few columns in a way that will let you address them by name.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 1

Author Comment

by:helpchrisplz
ID: 39748439
thank you
0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39748501
Please see http://www.laprbass.com/RAY_temp_helpchrisplz.php

<?php // RAY_temp_helpchrisplz.php
error_reporting(E_ALL);
echo '<pre>';

// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28327984.html

// THE CSV DATA IS AVAILABLE HERE - OPEN THE FILE
$url = 'http://laprbass.com/RAY_temp_helpchrisplz.csv';
$fpr = fopen($url, 'r');
if (!$fpr) trigger_error("UNABLE TO READ $url", E_USER_ERROR);

// WITH THE FILE OPENED, READ THE FIRST RECORD
$top = fgetcsv($fpr);

// KEEP ONLY THE 15 LEFTMOST POSITIONS
$top = array_slice($top,0,15);

// DATA ANOMALY: ARRAY POSITION 9 APPEARS TO BE UNCAPTIONED AND UNUSED
$top[9] = 'empty';

// NOW READ THE OTHER RECORDS AND BUILD AN ARRAY OF ASSOCIATIVE ARRAYS
while (!feof($fpr))
{
    // READ THE ROW INTO AN ARRAY
    $cur = fgetcsv($fpr);

    // SKIP EMPTY ROWS
    if (!$cur) continue;

    // COMBINE KEYS AND VALUES
    $cur = array_slice($cur,0,15);
    $out[] = array_combine($top, $cur);
}

// ACTIVATE THIS TO SEE THE ARRAYS
// print_r($out);

// SHOW HOW TO ACCESS THE DATA IN EACH ARRAY
foreach ($out as $cur)
{
    $sn = $cur['Stock Number'];
    $nn = $cur['Supp Name'];
    $de = $cur['Description'];
    echo PHP_EOL . "Stock Number: $sn";
    echo PHP_EOL . "Supplier Name: $nn";
    echo PHP_EOL . "Description: $de";
    echo PHP_EOL;
}

Open in new window

HTH, ~Ray
0
 
LVL 1

Author Closing Comment

by:helpchrisplz
ID: 39750198
thanks.

I am now getting all the cols together and making sure they are valid for magento.

i will make another question if i can't figure out the save down to CSV part but i found a bit of code for that hopefully i can include it some how with your code.

Thanks!
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39750258
Thanks for the points and thanks for using EE.  See the part near the end for the way to transform the arrays into a CSV file.

<?php // RAY_temp_helpchrisplz.php
error_reporting(E_ALL);
echo '<pre>';

// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28327984.html

// THE CSV DATA IS AVAILABLE HERE - OPEN THE FILE
$url = 'http://laprbass.com/RAY_temp_helpchrisplz.csv';
$fpr = fopen($url, 'r');
if (!$fpr) trigger_error("UNABLE TO READ $url", E_USER_ERROR);

// WITH THE FILE OPENED, READ THE FIRST RECORD
$top = fgetcsv($fpr);

// KEEP ONLY THE 15 LEFTMOST POSITIONS
$top = array_slice($top,0,15);

// DATA ANOMALY: ARRAY POSITION 9 APPEARS TO BE UNCAPTIONED AND UNUSED
$top[9] = 'empty';

// NOW READ THE OTHER RECORDS AND BUILD AN ARRAY OF ASSOCIATIVE ARRAYS
while (!feof($fpr))
{
    // READ THE ROW INTO AN ARRAY
    $cur = fgetcsv($fpr);

    // SKIP EMPTY ROWS
    if (!$cur) continue;

    // COMBINE KEYS AND VALUES
    $cur = array_slice($cur,0,15);
    $out[] = array_combine($top, $cur);
}

// ACTIVATE THIS TO SEE THE ARRAYS
// print_r($out);

// WRITE A CSV FILE FROM THE ARRAYS
$fpo = fopen('RAY_temp_helpchrisplz.txt', 'w');
if (!$fpo) trigger_error("UNABLE TO WRITE CSV", E_USER_ERROR);
foreach ($out as $cur)
{
    fputcsv($fpo, $cur);
}

Open in new window

Best regards, ~Ray
0
 
LVL 1

Author Comment

by:helpchrisplz
ID: 39750911
that did it.

thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This article discusses how to implement server side field validation and display customized error messages to the client.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question