Suitecrm: [PATCH] Bad CSV export on Reports module

Created on 6 Mar 2014 · 15Comments · Source: salesagility/SuiteCRM

The csv export do not escapes quotes ("), do not support new lines on field content, and can't be oppened without problems on excel. Also the http headers do not set the filename ok.

Here is a patch for the build_report_csv function on modules/AOR_Reports/AOR_Report.php

    function build_report_csv(){

        ini_set('zlib.output_compression', 'Off');

        ob_start();
        require_once('include/export_utils.php');

        $delimiter = getDelimiter();
        //text/comma-separated-values

        $user_agent = isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : '';
        if ($GLOBALS['locale']->getExportCharset() == 'UTF-8' &&
            ! preg_match('/macintosh|mac os x|mac_powerpc/i', $user_agent)) // Bug 60377 - Mac Excel doesn't support UTF-8
        {
            //Bug 55520 - add BOM to the exporting CSV so any symbols are displayed correctly in Excel
            $BOM = "\xEF\xBB\xBF";
            $csv = $BOM;
        }
        else
        {
            $csv = '';
        }

        $sql = "SELECT id FROM aor_fields WHERE aor_report_id = '".$this->id."' AND deleted = 0 ORDER BY field_order ASC";
        $result = $this->db->query($sql);

        $fields = array();
        $i = 0;
        $first_item = true;
        while ($row = $this->db->fetchByAssoc($result)) {
            $field = new AOR_Field();
            $field->retrieve($row['id']);

            $path = unserialize(base64_decode($field->module_path));

            $field_module = $this->report_module;
            if($path[0] != $this->report_module){
                foreach($path as $rel){
                    $field_module = getRelatedModule($field_module,$rel);
                }
            }
            $label = str_replace(' ','_',$field->label).$i;
            $fields[$label]['field'] = $field->field;
            $fields[$label]['display'] = $field->display;
            $fields[$label]['function'] = $field->field_function;
            $fields[$label]['module'] = $field_module;


            if($field->display){
                if(!$first_item)
                    $csv .= $delimiter;
                else
                    $first_item = false;

                $csv.= '"'.preg_replace("/\"/","\"\"", $field->label).'"';
            }
            ++$i;
        }

        $sql = $this->build_report_query();
        $result = $this->db->query($sql);

        while ($row = $this->db->fetchByAssoc($result)) {
            $csv .= "\r\n";
            $first_item = true;
            foreach($fields as $name => $att){
                if($att['display']){
                    if(!$first_item)
                        $csv .= $delimiter;
                    else
                        $first_item = false;

                    if($att['function'] != '' )
                        $csv .= '"'.preg_replace("/\"/","\"\"", $row[$name]).'"';
                    else
                        $csv .= '"'.preg_replace("/\"/","\"\"", trim(strip_tags(getModuleField($att['module'], $att['field'], $att['field'], 'DetailView',$row[$name])))).'"';
                }
            }
        }

        $csv= $GLOBALS['locale']->translateCharset($csv, 'UTF-8', $GLOBALS['locale']->getExportCharset());

        ob_clean();
        header("Pragma: cache");
        header("Content-type: text/comma-separated-values; charset=".$GLOBALS['locale']->getExportCharset());
        header("Content-Disposition: attachment; filename=\"{$this->name}.csv\"");
        header("Content-transfer-encoding: binary");
        header("Expires: Mon, 26 Jul 1997 05:00:00 GMT" );
        header("Last-Modified: " . TimeDate::httpTime() );
        header("Cache-Control: post-check=0, pre-check=0", false );
        header("Content-Length: ".mb_strlen($csv, '8bit'));

        print $csv;

        sugar_cleanup(true);
    }

Reports Moderate Bug

Source

lsmonki

👍1

Most helpful comment

Guys, I know you are working hard, but a three (* yes, 3! *) years old BUG/issue should have been taken care of by now... I am expriencing the same problem for years and my users complained again about this today...

we know that themes are important but not as important as normal functionalities to my opinion...

bmwtourer on 27 Apr 2017

👍3

All 15 comments

Thank you for this patch! It worked flawlessly to fix my issue with fields "shifting" on my CSV exports.

ghost on 19 May 2014

Is this included in the latest release of SuiteCRM?

iYianni on 2 Jul 2015

Sorry. This patch hasnt been added ye,

However, i have replaced the old function with this new patch function and it still doesn't seem to work.

I am using a new version of suite 7.2.2, and considering this patch was long ago, maybe something has broken in the process of a new version being used

@iYianni , have you also tried to replace the old function with the new function?

darthtang on 30 Jul 2015

@darthtang Yes, I replaced the code from version 7.1.2 and it works fine. Having looked at the code in the latest release, an attempt has been made to resolve the issues. Though, I haven't actually tested it.

What issues are you experiencing?

iYianni on 30 Jul 2015

Hi @iYianni

So the latest release has the new change? i am looking at the master branch now of suite and cannot see the change.

The issues i am experiencing are that the quotes are still not escaped i get """ in the csv file when i export my report

darthtang on 30 Jul 2015

It's not the same code as above, but the latest release looks like it is attempting to deal with (at least some of) these issues. As we're not using it yet, and without testing it, I won't be able to tell for sure, sorry. Have you check with @willrennie or @lsmonki for help with the latest release?

iYianni on 30 Jul 2015

@lsmonki

What were the errors before you added the changes into Suite? I know it was a long time ago when you posted this so no worries if you cant remember!

Cheers

Darren

darthtang on 30 Jul 2015

Some problems are described here: http://stackoverflow.com/questions/1241220/generating-csv-file-for-excel-how-to-have-a-newline-inside-a-value

First, you need to add the BOM to allow excell open it and get new lines on fields:

        $user_agent = isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : '';
        if ($GLOBALS['locale']->getExportCharset() == 'UTF-8' &&
            ! preg_match('/macintosh|mac os x|mac_powerpc/i', $user_agent)) // Bug 60377 - Mac Excel doesn't support UTF-8
        {
            //Bug 55520 - add BOM to the exporting CSV so any symbols are displayed correctly in Excel
            $BOM = "\xEF\xBB\xBF";
            $csv = $BOM;
        }
        else
        {
            $csv = '';
        }

NOTE: The code was copied from SugarCRM, the bug numbers are from their issues list.

Now there is a problem, the last field names and values for each row has an end delimiter and it shouldn't (do it on field names and field values):

        $first_item = true;
        while ($row = $this->db->fetchByAssoc($result)) {
            ...
            ...
            if($field->display){
                if(!$first_item)
                    $csv .= $delimiter;
                else
                    $first_item = false;

                $csv.= $this->encloseForCSV($field->label);
            }
            ...
            ...
        while ($row = $this->db->fetchByAssoc($result)) {
            $csv .= "\r\n";
            $first_item = true;
            foreach($fields as $name => $att){
                if($att['display']){
                    if(!$first_item)
                        $csv .= $delimiter;
                    else
                        $first_item = false;

                    if($att['function'] != '' )
                        $csv .= $this->encloseForCSV($row[$name]);
                    else
                        $csv .= $this->encloseForCSV(trim(strip_tags(getModuleField($att['module'], $att['field'], $att['field'], 'DetailView',$row[$name]))));
                }
            }
        }
        ...
        ...

Now you need to scape quotes and fix new lines, in the new code you can modify the encloseForCSV function

    private function encloseForCSV($field){
        //Replace \r\n with \n only, \r\n is the row delimiter
        $field = str_replace("\r\n", "\n", $field);
        return '"'.preg_replace("/\"/","\"\"", $field).'"';
    }

And in the new code I see an error, if you are going to modify the csv content, modify it before setting the headers, the length could change:

        // Modify before set headers
        if (!empty($sugar_config['export_excel_compatible'])) {
            $csv==chr(255) . chr(254) . mb_convert_encoding($csv, 'UTF-16LE', 'UTF-8');
        }

       ob_clean();
        header("Pragma: cache");
        header("Content-type: text/comma-separated-values; charset=".$GLOBALS['locale']->getExportCharset());
        header("Content-Disposition: attachment; filename={$this->name}.csv");
        header("Content-transfer-encoding: binary");
        header("Expires: Mon, 26 Jul 1997 05:00:00 GMT" );
        header("Last-Modified: " . TimeDate::httpTime() );
        header("Cache-Control: post-check=0, pre-check=0", false );
        header("Content-Length: ".mb_strlen($csv, '8bit'));


        print $csv;

NOTE: I have not tested it :)

lsmonki on 30 Jul 2015

Other error, the double equal sign must be a single equal:

        // Modify before set headers
        if (!empty($sugar_config['export_excel_compatible'])) {
            $csv=/*<--- SINGLE EQUAL HERE*/ chr(255) . chr(254) . mb_convert_encoding($csv, 'UTF-16LE', 'UTF-8');
        }

lsmonki on 30 Jul 2015

Ok @lsmonki Revisiting the past.

Looking at the now 7.8.x code I can see the following points.

Content adjusted before length header set:
This has been resolved in the later versions.
https://github.com/salesagility/SuiteCRM/blob/master/export.php#L92

Double equal sign must be a single equal: https://github.com/salesagility/SuiteCRM/issues/18#issuecomment-126304976
Not quite sure what this is referring to as it has always been a single equal sign.
https://github.com/salesagility/SuiteCRM/blob/master/export.php#L92

The delimiter at the end of each column has been raised as a separate bug:
https://github.com/salesagility/SuiteCRM/issues/3275

Seems the only remaining bit is that the build_report_csv does not contain the $BOM reference though calling the export_utils.php
I would deem this as a bug as if it already contains this on the export of other CSV it really should in Reports.