View Issue Details

IDProjectCategoryView StatusLast Update
0009034mantisbtcsvpublic2014-09-23 18:05
ReporterBuga Assigned Torombert  
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
OSWindows XP, Office 2003 
Product Version1.1.1 
Target Version1.2.9Fixed in Version1.2.9 
Summary0009034: CSV Export with special chars dont work properly
Description

Mantis provides 2 ways to export the data. If I use the csv export function I have the problem that excel dont display german special chars like ä,ö,ü or ß properly. Before we used Mantis 1.0.8 with a latin1 db and didnt have these problems. With the new UTF8 DB this dont work.
If I use the second option Print Reports and click on the excel icon everything works fine. (all special chars are printed correctly)

I think the difference here is that the second option is simple html while the first creates a csv file.

TagsNo tags attached.
Attached Files
csv_export.php (4,549 bytes)   
<?php
# Mantis - a php based bugtracking system

# Copyright (C) 2000 - 2002  Kenzaburo Ito - kenito@300baud.org
# Copyright (C) 2002 - 2007  Mantis Team   - mantisbt-dev@lists.sourceforge.net

# Mantis is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 2 of the License, or
# (at your option) any later version.
#
# Mantis is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with Mantis.  If not, see <http://www.gnu.org/licenses/>.

	# --------------------------------------------------------
	# $Id: csv_export.php,v 1.26.2.1 2007-10-13 22:33:14 giallu Exp $
	# --------------------------------------------------------
?>
<?php
	require_once( 'core.php' );

	$t_core_path = config_get( 'core_path' );

	require_once( $t_core_path . 'filter_api.php' );
	require_once( $t_core_path . 'csv_api.php' );
	require_once( $t_core_path . 'columns_api.php' );
?>
<?php auth_ensure_user_authenticated() ?>
<?php
	helper_begin_long_process();

	$t_page_number = 1;
	$t_per_page = -1;
	$t_bug_count = null;
	$t_page_count = null;

	$t_nl = csv_get_newline();
 	$t_sep = csv_get_separator();

	# Get bug rows according to the current filter
	$t_rows = filter_get_bug_rows( $t_page_number, $t_per_page, $t_page_count, $t_bug_count );
	if ( $t_rows === false ) {
		print_header_redirect( 'view_all_set.php?type=0' );
	}

	$t_filename = csv_get_default_filename();

	# Send headers to browser to activate mime loading

	# Make sure that IE can download the attachments under https.
	header( 'Pragma: public' );

//	header( 'Content-Type: text/plain; name=' . urlencode( $t_filename ) );
	header( 'Content-Type: text/plain; charset=ISO-8859-2; name=' . urlencode( $t_filename ) );

	header( 'Content-Transfer-Encoding: BASE64;' );

	# Added Quotes (") around file name.
	header( 'Content-Disposition: attachment; filename="' . urlencode( $t_filename ) . '"' );

	# Get columns to be exported
	$t_columns = csv_get_columns();

	# export the titles
	$t_first_column = true;
	ob_start();
	$t_titles = array();
	foreach ( $t_columns as $t_column ) {
		if ( !$t_first_column ) {
			//echo $t_sep;
			echo iconv("UTF-8", "ISO-8859-2", $t_sep);

		} else {
			$t_first_column = false;
		}

		if ( strpos( $t_column, 'custom_' ) === 0 ) {
			$t_column_title_function = 'print_column_title';
			helper_call_custom_function( $t_column_title_function, array( $t_column, COLUMNS_TARGET_CSV_PAGE ) );
		} else {
			$t_function = 'print_column_title_' . $t_column;
			$t_function( '', 'ASC', COLUMNS_TARGET_CSV_PAGE );
		}
	}

	//echo $t_nl;
	echo iconv("UTF-8", "ISO-8859-2", $t_nl);


	$t_header = ob_get_clean();

	# Fixed for a problem in Excel where it prompts error message "SYLK: File Format Is Not Valid"
	# See Microsoft Knowledge Base Article - 323626
	# http://support.microsoft.com/default.aspx?scid=kb;en-us;323626&Product=xlw
	$t_first_three_chars = substr( $t_header, 0, 3 );
	if ( strcmp( $t_first_three_chars, 'ID' . $t_sep ) == 0 ) {
		$t_header = str_replace( 'ID' . $t_sep, 'Id' . $t_sep, $t_header );
	}
	# end of fix

	//echo $t_header;
	echo iconv("UTF-8", "ISO-8859-2", $t_header);
	

	# export the rows
	foreach ( $t_rows as $t_row ) {
		$t_first_column = true;

		foreach ( $t_columns as $t_column ) {
			if ( !$t_first_column ) {
				//echo $t_sep;
				echo iconv("UTF-8", "ISO-8859-2", $t_sep);
			} else {
				$t_first_column = false;
			}

			if ( strpos( $t_column, 'custom_' ) === 0 ) {
				ob_start();
				$t_column_value_function = 'print_column_value';
				helper_call_custom_function( $t_column_value_function, array( $t_column, $t_row, COLUMNS_TARGET_CSV_PAGE ) );
				$t_value = ob_get_clean();

				if ( strstr( $t_value, $t_sep ) !== false ) {
					$t_value = '"' . $t_value . '"';
				}

				//echo $t_value;
				echo iconv("UTF-8", "ISO-8859-2", $t_value);
			} else {
				$t_function = 'csv_format_' . $t_column;
				//echo $t_function( $t_row[ $t_column ] );
				echo iconv("UTF-8", "ISO-8859-2", $t_function( $t_row[ $t_column ] ));

			}
		}

		//echo $t_nl;
		echo iconv("UTF-8", "ISO-8859-2", $t_nl);

	}
?>
csv_export.php (4,549 bytes)   

Relationships

related to 0015721 closedgrangeway Functionality to consider porting to master-2.0.x 
has duplicate 0009234 closedgiallu Norwegian characters not visiable in CSV export 
has duplicate 0011360 closedatrol When i use Chinese language,the exported CSV file can not be displayed correctly in the Microsoft Excel 
has duplicate 0013366 closeddregad CSV file doesn't include BOM 
has duplicate 0012499 closedatrol 匯出CSV檔,中文顯示有問題,是亂碼。 
has duplicate 0013668 closedrombert using EXCEL open .csv file, got un-readable-characters(simple-chinese) 

Activities

dhaddadi

dhaddadi

2008-04-28 08:17

reporter   ~0017708

I have the same problem, when I did a csv export , excel doesn't interpret correctly the caracters.
Thanks for all

ViperNet

ViperNet

2008-05-21 12:34

reporter   ~0017892

Modify csv_export.php

for ISO-8859-2 charset:

header( 'Content-Type: text/plain; charset=ISO-8859-2; name=' . urlencode( $t_filename ) );

use 'iconv' in all 'echo' command.
example:
echo iconv("UTF-8", "ISO-8859-2", $t_header);

echo iconv("UTF-8", "ISO-8859-2", $t_value);

Buga

Buga

2008-05-23 08:52

reporter   ~0017908

Hi
Thanks for that tip.
I tried to modify the csv_export.php like described but now the export prints only the headline of the first column and thats it. I uploaded my edited file here.
Any idea what i did wrong?

fischerhh

fischerhh

2008-10-23 06:37

reporter   ~0019647

ViperNet's advice works fine in my environment (Mantis 1.2.0a1, Windows XP). Thanks! One can proceed analogously for excel_export.php, but in excel_prepare_string() [excel_api.php] I had to substitute utf_encode($t_value) with iconv() of $t_value to ISO-8859-2 to avoid broken text (cf. note 19399 in 0008972).

squarebox

squarebox

2010-03-03 20:30

reporter   ~0024617

is it possible to get this applied to the trunk, or does changing the encoding to utf-8 break other language exports?

ViperNet

ViperNet

2010-03-04 06:41

reporter   ~0024631

Excel doesn't support UTF-8 character encoded CSV files (this is why special national characters are not displayed correctly).
But "sometimes" Excel 2007 opens fine UTF-8 encoded CSV files, so it is up to Excel installataion, if it can handle UTF-8.

It would be better, if Mantis could create XLSX (Excel2007, OpenOffice) worksheets instead of CSV files.

VeMag

VeMag

2011-09-29 09:53

reporter   ~0029886

Excel can read UTF-8 if the "BOM" is present in the file.
(see http://en.wikipedia.org/wiki/Byte_order_mark )

adding the line :
echo "\xEF\xBB\xBF";
just before starting writing columns headers make it works.
(found on http://www.php.net/manual/en/function.fwrite.php#55054 )

https://github.com/mantisbt/mantisbt/blob/master/csv_export.php#L77

rombert

rombert

2011-12-23 05:05

reporter   ~0030673

I've verified that fix proposed at

30: Fix 0009034: CSV Export with special chars dont work properly
http://github.com/mantisbt/mantisbt/pull/30

with LibreOffice 3.4 and MS Office 2007 . Without the BOM only LibreOffice displays the UTF-8 characters correctly, while with the BOM both programs display UTF-8. Due to that reason, I will merge the pull request with the default set to off, to address @dhx's concerns regarding backwards compatibility.

grangeway

grangeway

2013-04-05 17:57

reporter   ~0036347

Marking as 'acknowledged' not resolved/closed to track that change gets ported to master-2.0.x branch

Related Changesets

MantisBT: master 9d0eb8eb

2011-12-22 20:49

VeMag


Details Diff
Fix 0009034: CSV Export with special chars dont work properly

Adding "Byte Order Mark" at the begining of the file resolves the issue.

$g_csv_add_bom parameter with default = OFF, because of remarks on pull request 27

Signed-off-by: Robert Munteanu <robert.munteanu@gmail.com>
Affected Issues
0009034
mod - config_defaults_inc.php Diff File
mod - csv_export.php Diff File
mod - docbook/Admin_Guide/en-US/Configuration.xml Diff File

MantisBT: master-1.2.x 4fd53568

2011-12-22 20:49

VeMag


Details Diff
Fix 0009034: CSV Export with special chars dont work properly

Adding "Byte Order Mark" at the begining of the file resolves the issue.

$g_csv_add_bom parameter with default = OFF, because of remarks on pull request 27

Signed-off-by: Robert Munteanu <robert.munteanu@gmail.com>
Affected Issues
0009034
mod - config_defaults_inc.php Diff File
mod - csv_export.php Diff File
mod - docbook/adminguide/en/configuration.sgml Diff File