loop through CSV and update / delete columns

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

LVL 1
helpchrisplzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
PHP function fgetcsv() will read each row of the CSV file into an array.  You can process each row (array) individually.
helpchrisplzAuthor Commented:
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

Ray PaseurCommented:
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.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

helpchrisplzAuthor Commented:
thank you
Ray PaseurCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
helpchrisplzAuthor Commented:
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!
Ray PaseurCommented:
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
helpchrisplzAuthor Commented:
that did it.

thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.