Solved

loop through CSV and update / delete columns

Posted on 2013-12-30
9
1,449 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
  • 4
  • 4
9 Comments
 
LVL 108

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 108

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
 
LVL 1

Author Comment

by:helpchrisplz
ID: 39748439
thank you
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 108

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 108

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now