Export Mysql to ecxel/csv arabic encoding

[peterman5]

Hello, I want to export a mysql table to excel files and if not to csv, the problem with excel is it gives me data on one line, so I transformed it to csv and it works, the problem is it does not read arabic data

	header( 'Content-Type: text/csv; charset=utf-8; encoding=UTF-8' );
	header("Cache-Control: cache, must-revalidate");
	header("Pragma: public");
  	header( 'Content-Disposition: attachment;filename=export.csv' );

Help appreciated

[bradgrafelman]

What is the name of the collation used on the columns in the table?

Also, can you show us how you connect to the DB and execute the query? It could be that you need to first execute a SET NAMES command to set the connection collation/character set; see the MySQL manual page 9.1.4. Connection Character Sets and Collations for more info.

[peterman5]

bradgrafelman;10994233 wrote:

What is the name of the collation used on the columns in the table?

Also, can you show us how you connect to the DB and execute the query? It could be that you need to first execute a SET NAMES command to set the connection collation/character set; see the MySQL manual page 9.1.4. Connection Character Sets and Collations for more info.

Hello,
Database table fields are uft8, It did not work both as excel and csv

It is opening Fine in Notepad, but not in excell

Thats the complete Code:

<?php
header( 'Content-Type: text/csv; charset=utf-8; encoding=UTF-8' );
header("Cache-Control: cache, must-revalidate");
header("Pragma: public");
header( 'Content-Disposition: attachment;filename=export.csv' );
$conn = mysql_connect( 'localhost', 'root', '' ) or die( mysql_error( ) );
mysql_select_db( 'labora_donnation', $conn ) or die( mysql_error( $conn ) );
mysql_query("SET NAMES 'utf8'");
$query = sprintf( 'SELECT * FROM pr2' );
$result = mysql_query( $query, $conn ) or die( mysql_error( $conn ) );
$row = mysql_fetch_assoc( $result );
if ( $row )
{
echocsv( array_keys( $row ) );
}
while ( $row )
{
echocsv( $row );
$row = mysql_fetch_assoc( $result );
}

function echocsv( $fields )
{
$separator = '';
foreach ( $fields as $field )
{
if ( preg_match( '/\\r|\\n|,|"/', $field ) )
{
$field = '"' . str_replace( '"', '""', $field ) . '"';
}
echo $separator . $field;
$separator = ',';
}
echo "\r\n";
}
?>

when it open the file with
Notepad:

id,name,company,numyear,skill,lang,doc,location,subid,date1,date2,conf,oldnew,fil,selek,shahadat,typx,genderprefer
1,CONT22,aaaaa,6,gfhghgg, Arabic English French ,hgfhgfh,0,0,2011-09-01,2011-09-30,yes,new,,إداري,,إتصل بنا,
2,hgjjhjh11,gfdgfgfgg12,22,jhjhj, Arabic English French ,doc11,0,0,2011-10-19,2011-10-20,yes,new,,إداري,,التسجيل للدورة التدريبية,

With Excell

id name company numyear skill lang doc location subid date1 date2 conf oldnew fil selek shahadat typx genderprefer
1 CONT22 aaaaa 6 gfhghgg Arabic English French hgfhgfh 0 0 9/1/2011 9/30/2011 yes new إداري إتصل بنا
2 hgjjhjh11 gfdgfgfgg12 22 jhjhj Arabic English French doc11 0 0 10/19/2011 10/20/2011 yes new إداري التسجيل للدورة التدريبية

[bradgrafelman]

When posting PHP code, please use the board's [noparse]

..

[/noparse] bbcode tags as they make your code much easier to read and analyze.

As for your issue, I see two potential problems:

1. This:

   header( 'Content-Type: text/csv; charset=utf-8; encoding=UTF-8' );

   outputs an invalid HTTP header; the Content-Type header has no parameter called 'encoding' - it should just be 'charset'.

2. Various Google results lead me to believe that adding a BOM to the data might encourage Excel to parse the data as Unicode data (rather than ASCII or ISO-8895-1).

   For UTF-8, you can output the BOM like so:

   echo "\xEF\xBB\xFF"; // UTF-8 BOM: 0xEF,0xBB,0xBF

[peterman5]

Thank you for your help, What does this mean:

echo "\xEF\xBB\xFF"; // UTF-8 BOM: 0xEF,0xBB,0xBF

bradgrafelman;10994238 wrote:

When posting PHP code, please use the board's [noparse]

..

[/noparse] bbcode tags as they make your code much easier to read and analyze.

As for your issue, I see two potential problems:

1. This:

   header( 'Content-Type: text/csv; charset=utf-8; encoding=UTF-8' );

   outputs an invalid HTTP header; the Content-Type header has no parameter called 'encoding' - it should just be 'charset'.

2. Various Google results lead me to believe that adding a BOM to the data might encourage Excel to parse the data as Unicode data (rather than ASCII or ISO-8895-1).

   For UTF-8, you can output the BOM like so:

   echo "\xEF\xBB\xFF"; // UTF-8 BOM: 0xEF,0xBB,0xBF

[cretaceous]

why not use the PEAR Excel writer class:
http://pear.php.net/package/Spreadsheet_Excel_Writer/redirected

[Derokorian]

BOM means Byte Order Mark

[niroshan]

hI peterman5,

If your CSV`s are in UTF8 format then you can open them in excel by importing them into excel. If you try to double click and open the file in excel it wont give you the correct formatting.

Step 1:
Go to below menu

data -> from text file

Step 2:
select the CSV file

Step 3:
Select file type as Delimited and file origin as 65001: Unicode (UTF8)

Step 4:
Select the Delimiters as Comma

That`s it.

Hope this helps.
Thanks,
Best regards,
Niroshan