In csv export set delimiter to semicolon instead of comma in non English speaking countries

[Patrick Reinhard]

Hi.

In addition to the problems addressed with the existing 'excel-friendly' format, there is another pesky problem with csv and Excel and it is related with the country where Excel is being used.

In English speaking countries, or more precisely in all the countries using 'decimal point', Excel assumes ',' (comma) as default delimiter, while in the countries where 'decimal comma' is used, it assumes ';' (semicolon).
See:

• https://en.wikipedia.org/wiki/Decimal_mark#Countries_using_Arabic_numerals_with_decimal_point
• https://en.wikipedia.org/wiki/Decimal_mark#Countries_using_Arabic_numerals_with_decimal_comma

for the lists of the countries following the two conventions.

To make things worse, Excel does not ask for the delimiter to use when opening a csv file (i.e. with extension .csv), while Open Office and Libre Office do that, and changing the default delimiter in Excel requires to change the language/locale settings.

So, to export your found set to a csv file, you have to change the locales, do the export and then revert to the previous locales when done.
Definitely something you can't ask to - and expect from - the average customer.

I am living in a non English speaking country, and so far I have been circumventing the problem by manually changing the literals ',' into ';' at line 20 and 46 of xataface/actions/export_csv.php.

Now I added some lines to xataface/actions/export_csv.php in order to optionally get the delimiter from conf.ini instead of having it as a literal in the code.

Thanks to this, the [export_csv] section of conf.ini has 1 more key, as follows:

[export_csv]
  delimiter='<column delimiter char>'

Examples:

[export_csv]
delimiter=';'

[export_csv]
delimiter='|'

[export_csv]
delimiter='\t'

Notes:

• Tab is represented as '\t'.
• No strings longer than 1 should be used (except for \t).
• The delimiter cannot be quotation mark.
• The default delimiter is comma, as before.
• If format=excel is specified, the default delimiter is tab, as before.
• Enclosure and escape characters are unchanged.

The modified export_csv.php is given below.

I know that an optional native (i.e. directly to .xls files) Excel export module is available (https://github.com/shannah/xataface-module-excel).
Nevertheless I think that plain csv export should work everywhere.

Hope this helps developers using this awesome framework in non English speaking countries, and it would be great if Steve could include something similar in some next release.

Patrick

<?php

global $csv_delimiter;  //declare global var

$app = Dataface_Application::getInstance();
$csv_delimiter     = @$app->_conf['export_csv']['delimiter'];       //get keys from conf.ini

if (!$csv_delimiter || $csv_delimiter==''){                         //if no delimiter specified
    if (@$app->_conf['export_csv']['format']=='excel'){
         $csv_delimiter ="\t";                                           //set default to tab value when using 'excel friendly' format
    }else{
         $csv_delimiter =",";                                            //set default to comma when using plain csv format
    }
}elseif (strtolower($csv_delimiter)=='\\t'){
    $csv_delimiter ="\t";                                           // \t is not recognized as tab in ini parsing and so we have to check for the string '\t'
}elseif (strlen($csv_delimiter)!=1){
    die('Error in conf.ini: length of csv_delimiter cannot be greater than 1');
}elseif ($csv_delimiter=='"'){
    die('Error in conf.ini: csv_delimiter cannot be set to double quote (")');
}        

if ( !function_exists('prepare_csv') ){
    /**
     * This function is necessary to prepare data for inclusion in a
     * CSV cell.  Originally I thought we could just use 'addslashes',
     * but apparently quotes should just be doubled rather than
     * escpaed (e.g. "").
     */
    function prepare_csv($str){
        return str_replace('"','""',$str);
    }
}

if ( !function_exists('fputcsv') ){
    /**
     * putcsv was not included in PHP until version 5.  Provide alternative
     * implementation here.
     * Taken from http://ca3.php.net/manual/en/function.fputcsv.php#56827
     */
     function fputcsv($filePointer,$dataArray,$delimiter,$enclosure='"')
    {
        // Write a line to a file
        // $filePointer = the file resource to write to
        // $dataArray = the data to write out
        // $delimeter = the field separator
       
       
        // Build the string
        $dataArray = array_map('prepare_csv', $dataArray);
        $string = $enclosure.implode($enclosure.$delimiter.$enclosure, $dataArray).$enclosure;
       
       
        // Append new line
        $string .= "\n";
       
        // Write the string to the file
        fwrite($filePointer,$string);
    }
}



class dataface_actions_export_csv {
   
        function writeRow($fh, $data, $query){

            global $csv_delimiter;

            fputcsv($fh, $data, $csv_delimiter,'"');
        }
       
        function startFile($fh, $query){
           
        }
       
        function endFile($fh, $query){
           
        }
       
        function writeOutput($fh, $query){
            $app = Dataface_Application::getInstance();
            if ( @$app->_conf['export_csv'] and @$app->_conf['export_csv']['format'] == 'excel' ){
                   
                $this->outputExcelcsv($fh, $query, $app);
            } else {
                $this->outputStandardCsv($fh, $query, $app);
            }
        }
   
    function handle(&$params){
        set_time_limit(0);
        import('Dataface/RecordReader.php');
        $app =& Dataface_Application::getInstance();
        $query = $app->getQuery();
        $query['-limit'] = 9999999;
        $table =& Dataface_Table::loadTable($query['-table']);
        if ( isset($query['-relationship']) and @$query['--related'] ){
            $query['-related:start'] = 0;
            $query['-related:limit'] = 9999999;
            $record =& $app->getRecord();
            $relationship =& $table->getRelationship($query['-relationship']);
           
            $records =& df_get_related_records($query); //$record->getRelatedRecordObjects($query['-relationship']);
           
            $data = array(/*$relationship->_schema['short_columns']*/);
            $headings = array();
            foreach ( $relationship->_schema['short_columns'] as $colhead ){
                $f =& $relationship->getField($colhead);
                if ( @$f['visibility']['csv']  == 'hidden' ){
                    unset($f);
                    continue;
                }
                $headings[] = $colhead;
                unset($f);
            }
            $data[] = $headings;
            foreach ($records as $record){
                if ( !$record->checkPermission('view') ) continue;
                $data[] = $this->related_rec2data($record);
            }
            $temp = tmpfile();
                        $this->startFile($temp, $query);
            foreach ($data as $row){
                $this->writeRow($temp, $row, $query);//, $recordfputcsv($temp, $row,",",'"');
            }
        } else {
            $temp = tmpfile();
                       
                        $query['-skip'] = 0;
            $query['-limit'] = null;
            $records = new Dataface_RecordReader($query, 30, false);
            //$records =& df_get_records_array($query['-table'], $query,null,null,false);
            //$data = array();
            $headings = array();
            //foreach (array_merge(array_keys($table->fields()), array_keys($table->graftedFields())) as $colhead){
            foreach (array_keys($table->fields(false, true)) as $colhead){
                $f =& $table->getField($colhead);
                if ( @$f['visibility']['csv'] == 'hidden' ){
                    unset($f);
                    continue;
                }
                $headings[] = $colhead;
                unset($f);
           
            }
            //$data[] = $headings;
                        $this->startFile($temp, $query);
                        $this->writeRow($temp, $headings, $query);
            //fputcsv($temp, $headings,",",'"');
            foreach ($records as $record){
                if ( !$record->checkPermission('view') ) continue;
                $data = $this->rec2data($record);
                //fputcsv($temp, $data,",",'"');
                                $this->writeRow($temp, $data, $query);
            }
        }
        $this->endFile($temp, $query);
       
        fseek($temp,0);
        $this->writeOutput($temp, $query);
                   
        exit;
       
       
       
    }
       
        function outputStandardCsv($fh, $query, $app){
            header("Content-type: text/csv; charset={$app->_conf['oe']}");
            header('Content-disposition: attachment; filename="'.$query['-table'].'_results_'.date('Y_m_d_H_i_s').'.csv"');

            //$fstats = fstat($fh);
            while ( @ob_end_clean() );
            //echo fread($temp, $fstats['size']);
            fpassthru($fh);
            fclose($fh);
        }
   
        function outputExcelCsv($fh, $query, $app){

            global $csv_delimiter;

            //$sep  = "\t";
            $sep=$csv_delimiter;
            $eol  = "\n";
            $stdout = fopen('php://output', 'w');
            header('Content-Description: File Transfer');
            header('Content-Type: application/vnd.ms-excel');
            header('Content-disposition: attachment; filename="'.$query['-table'].'_results_'.date('Y_m_d_H_i_s').'.csv"');
            header('Content-Transfer-Encoding: binary');
            fwrite($stdout, chr(255) . chr(254));
            while ( ($row = fgetcsv($fh, 0, $csv_delimiter, '"')) !== false ){
                //print_r($row);
                ob_start();
                fputcsv($stdout, $row, $sep);
                $rowText = ob_get_contents();
                //echo $rowText.'now';exit;
                ob_end_clean();
               
                $rowText =  mb_convert_encoding($rowText, 'UTF-16LE', 'UTF-8');
                fwrite($stdout, $rowText);
            }
            fclose($fh);
            fclose($stdout);
        }
       
    function rec2data(&$record){
        $out = array();
        //$columns = array_merge(array_keys($record->_table->fields()), array_keys($record->_table->graftedFields()));
        $columns = array_keys($record->_table->fields(false, true));
       
        foreach ($columns as $key){
            $f =& $record->_table->getField($key);
                        $del = $record->_table->getDelegate();
            if ( @$f['visibility']['csv'] == 'hidden' ){
                unset($f);
                continue;
            }
                        $csvMethod = $key.'__csvValue';
                        if ( isset($del) and method_exists($del, $csvMethod)){
                            $out[] = $del->$csvMethod($record);
                        } else {
                            $out[] = $record->display($key);
                        }
            unset($f);
        }
        return $out;
    }
   
    function related_rec2data(&$record){
        $out = array();
        $r =& $record->_relationship;
        foreach ($r->_schema['short_columns'] as $col){
            $f =& $r->getField($col);
                        if ( @$f['visibility']['csv'] == 'hidden' ){
                unset($f);
                continue;
            }
                        $del = $r->getTable($col)->getDelegate();
            $csvMethod = $col.'__csvValue';
                        if ( isset($del) and method_exists($del, $csvMethod) ){
                            $out[] = $del->csvMethod($record->toRecord($col));
                        } else {
                            $out[] = $record->display($col);
                        }
            unset($f);
        }
        return $out;
       
    }

}

[Steve Hannah]

Thanks, you're right this should be configurable.   I would like to include this in Xataface, but if you could make some changes first:

1. Don't use a global variable.  Use a member variable in the dataface_actions_export_csv class.  Set the variable at the beginning of the handle() method.  Then use it in the writeRow() method.

2. Create a pull request in github.

Then I'll include it.

Best regards

Steve

--
You received this message because you are subscribed to the Google Groups "Xataface" group.
Visit this group at https://groups.google.com/group/xataface.
To view this discussion on the web visit https://groups.google.com/d/msgid/xataface/064c8007-1888-4c52-ae14-eca7e37af230%40googlegroups.com.

--

Steve Hannah

Web Lite Solutions Corp.

[Patrick Reinhard]

Will do.
Thank you.
Patrick

[Patrick Reinhard]

Pull request submitted, sorry for the delay
Patrick

[Patrick Reinhard]

As far as I know, my pull request is still pending.

Please resubmit your comments with a 'post reply' so that your question becomes visible to the maintainers.

Patrick

On Thu, Mar 21, 2019 at 11:11 PM 'tom wible' via Xataface <xata...@googlegroups.com> wrote:

i see that this has been implemented for export...how about import?
many times i want to import text containing commas, and apparently the csv parser ignores field quoting:

******ERROR: invalid field name: "firstName"*****
******ERROR: invalid field name: "lastName"*****
******ERROR: invalid field name: "phone"*****
******ERROR: invalid field name: "email"*****
******ERROR: invalid field name: "website"*****
******ERROR: invalid field name: "address"*****
******ERROR: invalid field name: "city"*****
******ERROR: invalid field name: "state"*****
******ERROR: invalid field name: "zip"*****
******ERROR: invalid field name: "country"*****
******ERROR: invalid field name: "bio"*****
******ERROR: required field missing: firstName*****
******ERROR: required field missing: lastName*****
******ERROR: required field missing: country*****
******ERROR: required field missing: phone*****
******ERROR: required field missing: email*****

******ERROR: expecting 11 fields but found 12 in record# 1: "Guofan","Ma",9407358434,"x...@outlook.com",,,,,,"China","Guofan Ma was born in 1937 and graduated from Xi`an Academy of Fine Art in 1961. He painted massive political and movie posters for more than 30 years. After retirement from government, he painted another 30 years to follow his heart: he prefers drawing Chinese landscape painting and Chinese flowers and birds. He is very popular for people to collect or decorate their homes."

--
You received this message because you are subscribed to a topic in the Google Groups "Xataface" group.

Visit this group at https://groups.google.com/group/xataface.

To view this discussion on the web visit https://groups.google.com/d/msgid/xataface/8fa99266-a2e4-45d3-9454-df63b882ddd3%40googlegroups.com.

[tom wible]

On Friday, March 22, 2019 at 5:24:18 AM UTC-4, Patrick Reinhard wrote:

As far as I know, my pull request is still pending.

Please resubmit your comments with a 'post reply' so that your question becomes visible to the maintainers.

yes, it is still pending https://github.com/shannah/xataface/pull/79/commits

 

On Thu, Mar 21, 2019 at 11:11 PM 'tom wible' via Xataface <xata...@googlegroups.com> wrote:

i see that this has been implemented for export...how about import?

i deleted my post b/c on further digging i found that the cvs import code is in _my_ tables/table.php
so i just split on tab instead of comma...d'oh!

[Patrick Reinhard]

A last post just to inform future visitors of this discussion.

The pull request was merged by Steve on 2019-03-22, and so the new parameters for csv export described above should be generally available

Thank you