Incorrect currency symbol in stats export CSV files

[juraj1000]

Describe the bug

We use currency symbol € and there is incorrect symbol in CSV stats files. This issue is in these stats:

• Best categories
• Best vouchers
• Best-selling products

We use Win 10 with MS Excel or Libre office and the result is incorrect in both programs. It seems there are missing BOM characters (EF BB BF) at the beginning of the file.

Expected behavior

Correct displaying of currency symbol €.

Steps to Reproduce

BO > Stats > Best-selling products > CSV Export

Screenshots BO stats

Excel

Additional information

• PrestaShop version: 1.7.6.5
• PHP version: 7.1.13

[prestashop-issue-bot[bot]]

Thanks for opening this issue! We will help you to keep its state consistent

[florine2623]

Hi @juraj1000 , I can't reproduce the issue : This is not a PrestaShop issue, you need to check the parameters of your CSV reading software.

Thanks!

[colinegin]

I just discussed with @florine2623 and actually I also reproduce it when using Excel on Windows. I’ll add this to the debug backlog so that it’s fixed. If you have already fixed it on your end or if you think you can do it, please do send us a pull request!

Thank you!

[colinegin]

Apparently you can configure it in excel :

• in main menu go to Data -> Get External Data -> Import Text File,
• follow steps in wizard - choose the encoding until you will see the correct preview and on the next step choose columns delimiter (delimiters differ from csv to csv files).

Can you try and tell us if this is working ?

[juraj1000]

Hello, I did it with Data > Get External Data > Import Text File and the result was correct. I had to manually select:

• code page UTF-8
• semicolon as a separator

It means that CSV file is generated without UTF-8 code page information (BOM characters EF BB BF - see bug description). These characters must be at the beginning of the file if there is used UTF-8 code page. There is necessary to generate file from PS also with 3 specific characters and it should work correctly.

The issue is that after pressing button CSV Export in PS an user can open the file directly with MS Excel. He will not use menu Data > Get External Data > ... . So the user doesn't have a possibility to choose correct code page and correct separator for data import.

[florine2623]

Hi @PrestaShop/prestashop-product-team ,

Do you think something should be done so that a merchant can see the csv file without changing parameters in Excel ? Normally when you first open a csv file in Excel, there's a window that let's you choose the encoding of the file like so :

Thanks!

[juraj1000]

Hello, it is necessary to generate CSV file from PS automatically with correct code page information. In my case the file was generated with code page UTF-8 and it seems it is defined in BO > Advanced Parameters > Database > SQL Manager > Settings > Select your default file encoding = utf-8. It is required to open output CSV file without any additional actions by a merchant where he should define the code page or column separator. Configuration

[filippodicostanzo]

I have the same issue. For fix it just add this line in this file: src\Core\Export\FileWriter\ExportCsvFileWriter.php

$exportFile->fwrite($bom=( chr(0xEF) . chr(0xBB) . chr(0xBF) ));

This is the complete class:

<?php
/**
 * Copyright since 2007 PrestaShop SA and Contributors
 * PrestaShop is an International Registered Trademark & Property of PrestaShop SA
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * that is bundled with this package in the file LICENSE.md.
 * It is also available through the world-wide-web at this URL:
 * https://opensource.org/licenses/OSL-3.0
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@prestashop.com so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade PrestaShop to newer
 * versions in the future. If you wish to customize PrestaShop for your
 * needs please refer to https://devdocs.prestashop.com/ for more information.
 *
 * @author    PrestaShop SA and Contributors <contact@prestashop.com>
 * @copyright Since 2007 PrestaShop SA and Contributors
 * @license   https://opensource.org/licenses/OSL-3.0 Open Software License (OSL 3.0)
 */

namespace PrestaShop\PrestaShop\Core\Export\FileWriter;

use Exception;
use PrestaShop\PrestaShop\Core\Export\Data\ExportableDataInterface;
use PrestaShop\PrestaShop\Core\Export\Exception\FileWritingException;
use PrestaShop\PrestaShop\Core\Export\ExportDirectory;
use SplFileObject;

/**
 * Class ExportCsvFileWriter writes provided data into CSV file and saves it in export directory.
 */
final class ExportCsvFileWriter implements FileWriterInterface
{
    /**
     * @var ExportDirectory
     */
    private $exportDirectory;

    /**
     * @param ExportDirectory $exportDirectory
     */
    public function __construct(ExportDirectory $exportDirectory)
    {
        $this->exportDirectory = $exportDirectory;
    }

    /**
     * {@inheritdoc}
     *
     * @throws FileWritingException
     */
    public function write($fileName, ExportableDataInterface $data)
    {
        $filePath = $this->exportDirectory . $fileName;

        try {
            $exportFile = new SplFileObject($filePath, 'w');
        } catch (Exception $e) {
            throw new FileWritingException(sprintf('Cannot open export file for writing'), FileWritingException::CANNOT_OPEN_FILE_FOR_WRITING);
        }

        $exportFile->fwrite($bom=( chr(0xEF) . chr(0xBB) . chr(0xBF) ));
        $exportFile->fputcsv($data->getTitles(), ';');

        foreach ($data->getRows() as $row) {
            $exportFile->fputcsv($row, ';');
        }

        return $exportFile;
    }
}

[MatShir]

@filippodicostanzo if you have the technical knowledge don't hesitate to propose a pull request (find more information here). We will be happy to review your contribution to the PrestaShop core ;)

[Blackmamba83]

I have the same issue. For fix it just add this line in this file: src\Core\Export\FileWriter\ExportCsvFileWriter.php

$exportFile->fwrite($bom=( chr(0xEF) . chr(0xBB) . chr(0xBF) ));

This is the complete class:

<?php
/**
 * Copyright since 2007 PrestaShop SA and Contributors
 * PrestaShop is an International Registered Trademark & Property of PrestaShop SA
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * that is bundled with this package in the file LICENSE.md.
 * It is also available through the world-wide-web at this URL:
 * https://opensource.org/licenses/OSL-3.0
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@prestashop.com so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade PrestaShop to newer
 * versions in the future. If you wish to customize PrestaShop for your
 * needs please refer to https://devdocs.prestashop.com/ for more information.
 *
 * @author    PrestaShop SA and Contributors <contact@prestashop.com>
 * @copyright Since 2007 PrestaShop SA and Contributors
 * @license   https://opensource.org/licenses/OSL-3.0 Open Software License (OSL 3.0)
 */

namespace PrestaShop\PrestaShop\Core\Export\FileWriter;

use Exception;
use PrestaShop\PrestaShop\Core\Export\Data\ExportableDataInterface;
use PrestaShop\PrestaShop\Core\Export\Exception\FileWritingException;
use PrestaShop\PrestaShop\Core\Export\ExportDirectory;
use SplFileObject;

/**
 * Class ExportCsvFileWriter writes provided data into CSV file and saves it in export directory.
 */
final class ExportCsvFileWriter implements FileWriterInterface
{
    /**
     * @var ExportDirectory
     */
    private $exportDirectory;

    /**
     * @param ExportDirectory $exportDirectory
     */
    public function __construct(ExportDirectory $exportDirectory)
    {
        $this->exportDirectory = $exportDirectory;
    }

    /**
     * {@inheritdoc}
     *
     * @throws FileWritingException
     */
    public function write($fileName, ExportableDataInterface $data)
    {
        $filePath = $this->exportDirectory . $fileName;

        try {
            $exportFile = new SplFileObject($filePath, 'w');
        } catch (Exception $e) {
            throw new FileWritingException(sprintf('Cannot open export file for writing'), FileWritingException::CANNOT_OPEN_FILE_FOR_WRITING);
        }

        $exportFile->fwrite($bom=( chr(0xEF) . chr(0xBB) . chr(0xBF) ));
        $exportFile->fputcsv($data->getTitles(), ';');

        foreach ($data->getRows() as $row) {
            $exportFile->fputcsv($row, ';');
        }

        return $exportFile;
    }
}

ciao filippo io ho inserito il codice in prestashop ho anche aggiornato la cache e forzato la compilazione da parametri avanzati ma cmq non va. Hai qualche altra info da dare? io uso prestashop 1.7.7.0 Grazie

[ghoussay]

Hi, that is indeed a problem with Excel and no-BOM. But there are several places in Prestashop where CSV are generated. I'm currently using Prestashop 1.7.7.8.

For statistics I've found here : classes\module\ModuleGrid.php

    protected function _displayCsv()
    {
        if (ob_get_level() && ob_get_length() > 0) {
            ob_end_clean();
        }
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . $this->displayName . ' - ' . time() . '.csv"');
        echo $this->_csv;
        exit;
    }

Just need to modify "echo ..." with echo chr(0xEF).chr(0xBB).chr(0xBF).$this->_csv; It is even possible to override this class/function.

Same has to be done in : classes\module\ModuleGraph.php to export graph statistics into csv.

Other classes exports other csv files, if it does not comply your need check in source code for ".csv" or "fputcsv" (I've found it in AdminController and module psgdpr for example) and add the BOM caracters at the beginning of export string.