Edit CSV field value for entire column

Question

I have a CSV that is downloaded from the wholesaler everynight with updated prices.

What I need to do is edit the price column (2nd column) and multiply the current value by 1.3 (30%).

My code to read the provided CSV and take just the columns I need is below, however I can't seem to figure out how to edit the price column.

<?php
// open the csv file in write mode
$fp = fopen('var/import/tb_prices.csv', 'w');

// read csv file
if (($handle = fopen("var/import/Cbl_4036_2408.csv", "r")) !== FALSE) {
$targetColumns = array(1, 2, 3); // get data from the 1st, 4th and 15th column

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {   
    $targetData = array();                  // array that hold target data
    foreach($targetColumns as $column){  // loop throught the targeted columns array
        if($column[2]){
            $data[$column] = $data[0] * 1.3;
        }
        $targetData[] = $data[$column]; // get the data from the column

    }
    # Populate the multidimensional array.
    $csvarray[$nn] = $targetData;       // add target data to csvarray
    // write csv file
    fputcsv($fp, $targetData);       
}
fclose($handle);
fclose($fp);

echo "CSV File Written Successfully!";
}


?>

Could somebody point me in the right direction please, explaining how you've worked out the function too so I can learn at the same time.

Could you add some example data from your CSV file? – Repox Aug 24 '14 at 20:15 — Repox, Aug 24 '14 at 20:15

score 0 · Answer 1 · answered Aug 24 '14 at 20:20

You are multiplying your price column always as - $data[0] * 1.3. It may be wrong here.

Other views:

If you are doing it once in a lifetime of this data(csv) handling, try to solve it using mysql itself only. Create the table similar to the database, import the .csv data into that mysql table. And then, SQL operate as you want.

No loops; no coding, no file read/write, and precise control over what you want to do with UPDATE. You just need to be aware of the delimiters (line separators eg. \r\n, column separators (eg. comma or tab or semicolon) and data encoding in double/single-quotes or not)

Once you modify your data, you can export it back to csv again.

If you want to handle the .csv file itself, open it in one connection (read only mode), and write to another file - saving the original data.

score 0 · Answer 2 · answered Aug 24 '14 at 20:28

you say that the column that contains the price is the second but then use that index with zero. anyway the whole thing can be easier

$handle = fopen("test.csv", "r");
if ( $handle !== FALSE) {
    $out = "";
    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
        $data[1] = ((float)$data[1] * 1.3);
        $out .= implode(";",$data) . "\n";
    }
    fclose($handle);
    file_put_contents("test2.csv", $out);
}

this code open a csv file with comma as separator. than read every line and for every line it's multiplies the second coloumn (index 1) for 1.3

this line

$out .= implode(";",$data) . "\n";

generate a line for new csb file. see implode on the officile documentation ...

after I close the connection to the file. and 'useless to have a connection with two files when you can do the writing of the second file in one fell swoop. the thing is true for small files

Edit CSV field value for entire column

2 Answers2