How to place HTML tags in a CSV?

Question

I have an excel sheet. In this excel sheet it contains Parts Number, Summary, Brand, Status. I would like to be able to import CSV data which contains text that already has html so I don’t have to apply all the formatting after import.

In this new csv, it should only contain 3 columns, Parts Number, Summary and html table. In this html table, it contains Parts Number, Summary, Brand, Status.

I have already created a table using php and it worked, now I am having difficulty trying to bring the table to csv.

Here is my code:

<?php
$file = fopen("Book1.csv","r");
$file2 = fopen("Book2.csv","w");
$data = [];
$description = '<table class="table">';
while($row = fgetcsv($file)) {
$data[] = $row; //Get all the data
}
if($data){  
    $n_columns = count($data[0]); //Get number of columns 
}
$description .= '<table border="1">'; 
for ($col = 0; $col < $n_columns; $col++) {
    $description .= '<tr>';
    foreach ($data as $row_k => $row) {
        if ($row_k == 0) {
            $description .= '<th>';
        } else {
            $description .= '<td>';
        }

        $description .= $row[$col] ?? '';
        if ($row_k == 0) {
            $description .= '</th>';
        } else {
            $description .= '</td>';
        }
    }
    $description .= '</tr>';
    fputcsv($file2, $data); // line 50
}
$description .= '</table>';
fclose($file);
fclose($file2);
?>

I am desperate, any help is appreciated.

Answer 1

If I understood you correctly, this should accomplish the task. It uses 3 functions, array_to_csv, csv_to_array and build_table.

<?php

function csv_to_array($filename, $header_row=true)
{
    $handle = fopen($filename, 'r');
        
    if ($header_row === true)
        $header = fgetcsv($handle);

    $data = [];
    while ($row = fgetcsv($handle)) {
        if ($header_row)
            $data[] = array_combine($header, $row);
        else
            $data[] = $row;
    }
    return $data;
}

function array_to_csv($data, $filename, $header_row=true)
{
    $handle = fopen($filename, 'w');

    if ($header_row == true) {
        $header = array_keys($data[0]);
        fputcsv($handle, $header);
    }

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

function transpose_array($array)
{
    $headers = array_keys($array[0]);
    $transposed = [];

    foreach ($array as $row) {
        foreach ($headers as $header) {
            if (!isset($transposed[$header]))
                $transposed[$header] = [];
         
            $transposed[$header][] = $row[$header];
        }
    }

    return $transposed;
}

function build_table($array)
{
    $transposed = transpose_array($array);

    $table = '<table><tbody>';
    foreach ($transposed as $heading => $row) {
        $table .= '<tr><th>' . $heading .'</th>';
        foreach ($row as $element) {
            $table .= '<td>' . $element . '</td>';
        }
        $table .= '</tr>';
    }
    $table .= '</tbody></table>';
    return $table;
}

$book1 = csv_to_array('Book1.csv');
$book2 = [];
foreach ($book1 as $row) {
    $key = $row['Parts Number'];
    if (!isset($book2[$key])) {
        $book2[$key] = [
            'Parts Number' => $key,
            'Summary' => $row['Summary']
        ];
    }
    $book2[$key]['rows'][] = $row;
}

foreach ($book2 as &$product) {
    $product['html table'] = build_table($product['rows']);
    unset($product['rows']);
}
unset($product);
$book2 = array_values($book2);

array_to_csv($book2, 'Book2.csv');

Book1.csv contains:

Parts Number,Summary,Brand,Status,Country
6SE1200-1EA70-1,SIMOVERT P 6,Siemens,Discontinued,Germany
6SE1200-1EA70-1,SIMOVERT P 6,Siemens,Discontinued,France
6SE1200-1EA70-1,SIMOVERT P 6,Siemens,In Stock,England
6SE3012-0BA00,SIMOVERT P M,Siemens,Discontinued,-

Book2.csv output:

"Parts Number",Summary,"html table"
6SE1200-1EA70-1,"SIMOVERT P 6","<table><tbody><tr><th>Parts Number</th><td>6SE1200-1EA70-1</td><td>6SE1200-1EA70-1</td><td>6SE1200-1EA70-1</td></tr><tr><th>Summary</th><td>SIMOVERT P 6</td><td>SIMOVERT P 6</td><td>SIMOVERT P 6</td></tr><tr><th>Brand</th><td>Siemens</td><td>Siemens</td><td>Siemens</td></tr><tr><th>Status</th><td>Discontinued</td><td>Discontinued</td><td>In Stock</td></tr><tr><th>Country</th><td>Germany</td><td>France</td><td>England</td></tr></tbody></table>"
6SE3012-0BA00,"SIMOVERT P M","<table><tbody><tr><th>Parts Number</th><td>6SE3012-0BA00</td></tr><tr><th>Summary</th><td>SIMOVERT P M</td></tr><tr><th>Brand</th><td>Siemens</td></tr><tr><th>Status</th><td>Discontinued</td></tr><tr><th>Country</th><td>-</td></tr></tbody></table>"