Pull JSON values and add to new column in existing CSV

Question

I am trying to take values from JSON (below) and insert them into a new column of an existing CSV. With my current code (also below), all that's being added to the new row is the word "Array" on each line. How can I adjust my code so that it works properly? Also, is there a way for me to set a header name for the new column I'm creating?

Thank you, please look at my code below and let me know if you have any questions.

JSON blob:

{
 "test":{"12345":"98765","56789":"54321"},
 "control":{"99999":"987651","88888":"987652","22222":"987653","27644":"987655"}
}

Current CSV:

userid,foo_date,bar,baz,qux,country
"12345","2013-03-14","1.72500","1","1055.74","UK"
"38726","2013-03-14",\N,"1","3430.07","UK"
"85127","2013-03-14",\N,"0","635.25","US"
"16984","2013-03-14",\N,"1","5233.09","US"

Current PHP (as per How to add columns to CSV using PHP):

$json = json_decode($s, true);
$json_values = array_values($json['control']);

$newCsvData = array();

if (($handle = fopen("testgroup.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $data[] = $json_values;
        $newCsvData[] = $data;
    }
    fclose($handle);
}

$handle = fopen('testgroup.csv', 'w');

foreach ($newCsvData as $line) {
   fputcsv($handle, $line);
}

fclose($handle);

$s equals the JSON blob.

Resulting CSV should look like:

userid,foo_date,bar,baz,qux,country,extra
"12345","2013-03-14","1.72500","1","1055.74","UK","987651"
"38726","2013-03-14",\N,"1","3430.07","UK","987652"
"85127","2013-03-14",\N,"0","635.25","US","987653"
"16984","2013-03-14",\N,"1","5233.09","US","987655"

How should the expected result .csv file look like? Btw, you have posted this question twice. Why not just enhancing the old post? — hek2mgl
– hek2mgl, Commented May 8, 2013 at 1:12
I just added how the resulting CSV file should look into my question. I decided not to enhance the last post because it was marked as a duplicate (I think unfairly). This post has much more info. — conbask
– conbask, Commented May 8, 2013 at 1:20

hek2mgl · Accepted Answer · 2013-05-08 01:45:25Z

2

Try this (tested):

$data = <<<EOF
{
 "test":{"12345":"98765","56789":"54321"},
 "control":{"99999":"987651","88888":"987652","22222":"987653","27644":"987655"}
}
EOF;

$json = json_decode($data, true);
$json_values = array_values($json['control']);
// add column header to the start of the array
array_unshift($json_values, 'extra');

// open the file in read write mode.
$fd = fopen('testgroup.csv', 'r+');
// add the last field for each record
$records = array();
while($record = fgetcsv($fd)) {
    $record []= array_shift($json_values);
    $records []= $record;
}

// clear file and set seek to start
ftruncate($fd, 0); 
fseek($fd, 0); 

// rewrite file
foreach($records as $record) {
   fputcsv($fd, $record);
}

fclose($fd);

edited May 8, 2013 at 1:45

answered May 8, 2013 at 1:37

hek2mgl

159k31 gold badges263 silver badges279 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

conbask Over a year ago

I'm getting a parse error on this line $json_values = array_values($json['control']);. Error is "Parse error: parse error, expecting 'T_STRING' or 'T_VARIABLE' or 'T_NUM_STRING' in foo.php on line 8"

hek2mgl Over a year ago

Maybe you failed to copy / paste it correctly. The code works (now tested twice)

conbask Over a year ago

That's weird. I guess I did copy/paste incorrectly. Thank you for your help!

hek2mgl Over a year ago

np.. Sometimes this can happen because you select/copy hidden chars or whatever.....

Dinesh · Accepted Answer · 2013-05-08 02:14:31Z

another quick and naive way to do this is:

$json = json_decode($s, true);
$json_values = array_values($json['control']);

$i = 0;
if (($handle = fopen("csvFile.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
       //seperate your header data
        $i++;
        if($i==1)
            $header = $data;
        else
            $datas[] = $data; //all the data
        $i++;
    }
    fclose($handle);
}

//adding the json now to the data based on the test value
// assuming that your json data length will be same as number of rows in csv
//you can modify the code easily to make it dynamic
$j=0;
foreach($datas as &$d){
    array_push($d, $json_values[$j]);
    $j++;       
}

//than append data in the file
$handle = fopen('testgroup.csv', 'r+');
//put the header inside the csv
fputcsv($handle, $header);

foreach ($datas as $line) {
   fputcsv($handle, $line);
}

fclose($handle);

this is first way you should implement once you are able to figure out a better way to do this like the one @hek2mgl suggested.. Just wanted to throw it out there....

Dins

Collectives™ on Stack Overflow

Pull JSON values and add to new column in existing CSV

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related