PHP script to create new csv from another csv after rearranging the data

Question

I have a csv with following structure:

And I need the output csv as follows:

That means taking the faetures from each column and put it in the single row.

I am using php office to fetch and write the csv. I have written the following:

if ( false === $handle = fopen('../../../3b.csv', 'r') )
    throw new Exception('File open failed.');  
$headers = fgetcsv($handle);
$row =  '';
$row = array();
$data = '';
$data = array();
while ( false !== $fields = fgetcsv($handle) ) {
    $fields = array_combine($headers, $fields);
        foreach($fields as $key=>$value){
            if($key!='sku'){
                if($value==''){
                    continue;
                }
            }
            $row[$key] = $value;
}
if(sizeof($row)==1){
    unset($row['sku']);
}
    $row = array_filter( $row );
    $data[] = $row;    
}
$data = array_filter($data);
$use_keys = ['sku','AC Rating','color','Feature','Finish','Grade','Installation Location','Installation Method','Plank Style','Size','Specie','Tile Format','Warranty','Wear Layer','Width','LifeStyle',
'Material','Style','Thickness','Appearance','PEIRating','ProtectionRating'];  
foreach($data as $key=>$value){   
    $new_arr = [];
    foreach($use_keys as $apk_item) {  
        $new_value = '';
        if (isset($data[$key][$apk_item])) {
            $new_value = str_replace(",","|",$data[$key][$apk_item]);
        }            
        $new_arr[$apk_item] = $new_value;                
    } 
    $data[$key] = $new_arr;
}
$data = array_filter($data, 'array_filter');
$final_array = array();
foreach ($data as $features) {
    $product = array('feature' => '');
    foreach ($features as $key => $feature) {
        if ($key == 'sku') {
            $product['sku'] = $feature;
        }
        else {

            if($feature!=''){
            $product['feature'] .= $key;
            $product['value'] .= $feature;

            }
        }
    }
   
    $final_array[] = $product;
}
$final_array = array_filter($final_array);  
$table  = '<table border="1" id="csvtable">
<thead><tr><th>sku</th><th>feature</th><th>value</th></tr></thead>
<tbody>';   
foreach($final_array as $value){
    $sku = $value["sku"];
    $combinedfeature = explode(",", $value['feature']);   
            foreach($combinedfeature as $single){   
                $table .=   '<tr><td width="20%">'.$sku.'</td><td width="40%">'.$single['feature'].'</td><td width="40%">'.$single['value'].'</td></tr>';  
            }       
    
 }
 $table .= '</tbody></table>';   
print_r($table);

It's giving wrong output. How can I do this? Anyone can help, please?

If you wanna create another csv, why are you writing html? Just use fputcsv — ahendwh2
– ahendwh2, Commented Sep 24, 2020 at 14:26

Nigel Ren · Accepted Answer · 2020-09-24 14:56:53Z

1

A much more compact method would be to read the input and write out the target file in one loop.

This code reads in each line, combines it with the header and then extracts the sku (and removes it from the details). Then loops over the remaining details, and if there is a value to output it writes the output to the result file.

As each value may also be a comma separated list, this uses explode() to split them into individual items and writes them out as separate parts...

$inputFile = "a.csv";
$outputFile = "a1.csv";
$inputHandle = fopen($inputFile, 'r');
$outputHandle = fopen($outputFile, 'w');
$headers = fgetcsv($inputHandle);
fputcsv($outputHandle, ["sku", "feature", "value" ]);

while ( false !== $fields = fgetcsv($inputHandle) ) {
    $fields = array_combine($headers, $fields);
    $sku = $fields['sku'];
    unset($fields['sku']);
    foreach ( $fields as $name => $field )  {
        if (!empty(trim($field)))   {
            $subFields = explode(",", $field );
            foreach ( $subFields as $value )    {
                fputcsv($outputHandle, [$sku, $name, $value]);
            }
        }
    }
}
fclose($inputHandle);
fclose($outputHandle);

edited Sep 24, 2020 at 14:56

answered Sep 24, 2020 at 14:40

Nigel Ren

57.3k11 gold badges49 silver badges59 bronze badges

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

1 Comment

Muntashir Are Rahi Over a year ago

Hi, Its very close. But i need to explode the values having comma-separated (multiple values of the features) and put them in rows. Now its showing 117-1002 Feature Square Edge,Unfinished this would be 117-1002 Feature Square Edge and 117-1002 Feature Unfinished . Can u please update the code. Thanks a million.

Collectives™ on Stack Overflow

PHP script to create new csv from another csv after rearranging the data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related