Unconfigured data when exporting to csv file

Question

I'm trying to export shopware 6 products in a csv file using php, but the data is coming unconfigured, I can't read it. the main idea is to export just 4 columns and relate the data, initially I'm trying to export everything, and then check the product with its variables and price. below the code and the result:

<?php


// Create headers:
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=produtos.csv');
header('Pragma: no-cache');

// Create file out produtos.csv:
$saida = fopen('php://output', 'w');

// Placing columns in the file produtos.csv:
$colunas = 'id, product_number, tax_id, product_translation_name';
fputcsv($saida, array($colunas));

// Getting the data from the database:
$servername = "";
$username = "";
$password = "";
$dbname = "";
$table = "";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

$conn->query("SET NAMES 'utf8'");
$conn->query('SET character_set_connection=utf8');
$conn->query('SET character_set_client=utf8');
$conn->query('SET character_set_results=utf8');

// Searching all columns
$linhas = "select" .
"       x.product_id" .
"       , x.product_number" .
"       , x.tax_id" .
"       , x.price_netto" .
"       , x.product_translation_name" .
"       , x.product_property_name" .
" from" .
"   (" .
"       select" .
"           b.product_id" .
"           , a.product_number" .
"           , a.tax_id" .
"           , a.price as price_netto" .
"           , b.name as product_translation_name" .
"           , null as product_property_name" .
"           , 1 as ordem" .
"       from" .
"           dev_ashop_1.product a" .
"           inner join dev_ashop_1.product_translation b on b.product_id = a.id" .
"       union all" .
"       select" .
"           b.product_id" .
"           , a.product_number" .
"           , a.tax_id" .
"           , a.price as price_netto" .
"           , b.name as product_translation_name" .
"           , d.name as product_property_name" .
"           , 2 as ordem" .
"       from" .
"           dev_ashop_1.product a" .
"           inner join dev_ashop_1.product_translation b on b.product_id = a.id" .
"           inner join dev_ashop_1.product_option c on c.product_id = a.id" .
"           inner join dev_ashop_1.property_group_option_translation d on d.property_group_option_id = c.property_group_option_id" .
"   ) x" .
" order by" .
"   x.product_id" .
"   , x.product_number" .
"   , x.ordem";



$result = $conn->query($linhas);

// Checking for data:
if ($result->num_rows > 0) {
  // Saida de dados por um array:
  while($linha = $result->fetch_assoc()) {
    // Inserindo os dados no arquivo produtos.csv:
    fputcsv($saida, $linha);
  }
} else {
  echo "";
}
$conn->close();

// close
fclose($saida);

?>

Answer 1

You seem to have multiple problems here:

1. The CSV header you write with fputcsv($saida, array($colunas)); does not correspond to the actual columns you are printing. In output you have a price JSON, in the header you don't mention the price. And it would be better if you make $colunas an array with the columns as items, not the full string.
2. The price is in JSON format. You might want to use json_decode() to extract the price data to single columns before writing it to the CSV.
3. You are opening the CSV in excel in the wrong way, check this: https://superuser.com/questions/407082/easiest-way-to-open-csv-with-commas-in-excel, or better use LibreOffice which is better to read CSV files.