CiviCRM Community Forums (archive)

I've just been trying to solve an issue with CiviReportMail.php and CSV output - and I thought the solution may be useful for someone else.
When sending (or exporting) a CSV from CiviReport, the resulting file can't be opened in Excel without specifying the encoding used (Data - Import - From Text File etc.....)

Having seen this thread: http://forum.civicrm.org/index.php?topic=21654.0 by Tchiot Galopin, with a couple of minor tweaks, Excel on both a PC and Mac identify the CSV and open without a problem.  As well as the issue of opening, a bigger problem resulted from the GBP locale and using a £ sign (it ended up showing as "£" in Excel)

A solution is to edit /administrator/components/com_civicrm/civicrm/CRM/Report/Utils/Report.php from line 192
// Add the headers.
        $csv .= implode(',', $headers) . "\n";

        $displayRows = array();
        $value       = null;
        foreach ( $rows as $row ) {
            foreach ( $columnHeaders as $k => $v ){
                if ( $value = CRM_Utils_Array::value( $v, $row ) ) {
                    // Remove HTML, unencode entities, and escape quotation marks.


                    // _________________________________________________________________Add the following line
                        $value = mb_convert_encoding ($value , "UTF-16LE", "UTF-8");

                    $value =
                        str_replace('"', '""', html_entity_decode(strip_tags($value)));

                    if ( CRM_Utils_Array::value( 'type', $form->_columnHeaders[$v] ) & 4 ) {
                        if ( CRM_Utils_Array::value( 'group_by', $form->_columnHeaders[$v] ) == 'MONTH' ||
                             CRM_Utils_Array::value( 'group_by', $form->_columnHeaders[$v] ) ==  'QUARTER' ) {
                            $value =  CRM_Utils_Date::customFormat( $value, $config->dateformatPartial );
                        } elseif ( CRM_Utils_Array::value( 'group_by', $form->_columnHeaders[$v] ) == 'YEAR' ) {
                            $value =  CRM_Utils_Date::customFormat( $value, $config->dateformatYear );
                        } else {
                            $value =  CRM_Utils_Date::customFormat( $value,'%Y-%m-%d' );
                        }
                    } else if ( CRM_Utils_Array::value( 'type', $form->_columnHeaders[$v] ) == 1024 ) {
                        $value =  CRM_Utils_Money::format( $value );


                        // _________________________________________________________________Add the following line
                        $value = mb_convert_encoding ($value , "UTF-16LE", "UTF-8");

                    }
                    $displayRows[$v] = '"'. $value .'"';
                } else {
                    $displayRows[$v] = " ";
                }
            }
            // Add the data row.
            $csv .= implode(',', $displayRows) . "\n";
        }

Thanks to Tchiot for the simple fix!