By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,062 Members | 1,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,062 IT Pros & Developers. It's quick & easy.

export arabic data to csv format

P: 52
Dear Sir,

I have a PHP script that export CSV with arabic text, for example I download the CSV on the website then save to desktop of my computer and Open it on NOTEPAD... that works fine I could still see all the arabic text OK with no problem, but if I open the CSV file in Microsoft EXCEL, all the arabic text are busted and no longer readable.
arabic text is inserted and stored in database as UTF-8 format.

Do you know a solution for this, how could I make it work OK on MS Excel, because the user want it to view the CSV file in Excel and this is so much urgent for me . please reply.

Regards,
Fauk Chowdhury.
Expand|Select|Wrap|Line Numbers
  1.  
  2. <?php
  3. // Connect to database
  4. mysql_connect("localhost", "root", "root123");
  5. mysql_select_db("medgulf");
  6.  
  7.  $fname = date("d-m-Y");
  8. // mention the file name to display in download prompt
  9. $file_name = "$fname.csv";
  10.  
  11. header("Content-Disposition: attachment; filename=\"$file_name\"");
  12. header("Cache-Control: cache, must-revalidate");
  13. header("Pragma: public");
  14. header('Content-Type: text/xml,  charset=UTF-8; encoding=UTF-8');
  15.  
  16. $fd1 = $_POST['fd'];
  17. $fm1 = $_POST['fm'];
  18. $fy1 = $_POST['fy']; 
  19. // Output header
  20. //echo "Name, Code"."\n";
  21. $frmday = $fy1."-".$fm1."-".$fd1;
  22. $ld1 = $_POST['ld'];
  23. $lm1 = $_POST['lm'];
  24. $ly1 = $_POST['ly']; 
  25.  
  26. $today = $ly1."-".$lm1."-".$ld1;
  27. //echo $frmday;
  28. $usrname = $_POST['usname'];
  29. $export_date = date("Y-m-d");
  30.  
  31. $result = mysql_query("SELECT product, agent_code, policy_no, DATE_FORMAT(entry_date, '%d/%m/%Y'), DATE_FORMAT(inception_date_eng, '%d/%m/%Y'), DATE_FORMAT(inception_date_hijri, '%d/%m/%Y'), DATE_FORMAT(expiry_date_eng, '%d/%m/%Y'), DATE_FORMAT(expiry_date_hijri, '%d/%m/%Y'), client_info.first_name, client_info.family_name, client_info.father_name, client_info.grand_father_name, client_info.mobile_no, client_info.dob, client_info.p_o_box, client_info.zip_code, client_info.city, client_info.id_type, client_info.id_no, vehicle_type, license_type, net_premium, brandname, model, reg_type, plate_no, model_year, chassis_no, color, user_name, age_limit  FROM vehicle_info, client_info where vehicle_info.policy_no = client_info.sr AND entry_date BETWEEN '$frmday' AND '$today' AND  user_name='$usrname' AND exported='yes' ");
  32. $str = mb_convert_encoding($result, "UTF-8", "ASCII");
  33. echo "encode $str";
  34.  
  35. if($result) {
  36. while($rs = mysql_fetch_array($result))
  37. {    echo $rs[0].", ";
  38.     echo $rs[1].", ";
  39.     echo $rs[2].", ";
  40.     echo $rs[3].", ";
  41.     echo $rs[4].", ";
  42.     echo $rs[5].", ";
  43.     echo $rs[6].", ";
  44.     echo $rs[7].", ";
  45.     echo $rs[8].", ";
  46.     echo $rs[9].", ";
  47.     echo $rs[10].", ";
  48.     echo $rs[11].", ";
  49.     echo $rs[12].", ";
  50.     echo $rs[13].", ";
  51.     echo $rs[14].", ";
  52.     echo $rs[15].", ";
  53.     echo $rs[16].", ";
  54.     echo $rs[17].", ";
  55.     echo $rs[18].", ";
  56.     echo $rs[19].", ";
  57.     echo $rs[20].", ";
  58.     echo $rs[21].", ";
  59.     echo $rs[22].", ";
  60.     echo $rs[23].", ";
  61.     echo $rs[24].", ";
  62.     echo " ".", ";
  63.     echo $rs[25].", ";
  64.     echo $rs[26].", ";
  65.     echo $rs[27].", ";
  66.     echo $rs[28].", ";
  67.     echo " ".", ";
  68.     echo " ".", ";
  69.     echo $rs[29].", ";
  70.     echo $rs[30].", ";
  71. }
  72.  
  73. $result4 = mysql_query("UPDATE vehicle_info SET exported ='yes', exported_date='$export_date'   WHERE user_name ='$usrname'");
  74.  
  75. if (!$result4) {
  76.            die('Query failed: To Vehicle Info Table ');
  77.             }
  78.  
  79.  
  80. }
  81. else {
  82.  
  83. echo "There Is No Data To Export ";
  84.  
  85. }
  86.  
  87.  
  88. ?>
Dec 10 '07 #1
Share this Question
Share on Google+
2 Replies


pbmods
Expert 5K+
P: 5,821
Heya, Fauk.

Make sure you're setting your MySQL connection encoding:
Expand|Select|Wrap|Line Numbers
  1. mysql_query("SET NAMES 'utf-8'", $conn);
  2. mysql_query("SET CHARACTER SET 'utf-8'", $conn);
  3.  
Where $conn is your MySQL connection resource.
Dec 23 '07 #2

P: 1
Dear Faruk,

You can use phpexcel library for this purpose. Its works great. You can download the library from here:
http://code.google.com/p/php-excel/

The sample code is as follows (I used that in joomla):
function export_contact_messages() {
$model = $this->getModel('usercontacts');

$rows = $model->getContactMessages();
global $mosConfig_absolute_path;

if( !file_exists( $mosConfig_absolute_path . '/libraries/phpexcel/PHPExcel.php') ) {
die('Phpexcel library not found');
}
$phpexcel_path = $mosConfig_absolute_path . '/libraries/phpexcel/PHPExcel.php';
include_once($phpexcel_path);

//die($phpexcel_path);

$objPHPExcel = new PHPExcel();

$objPHPExcel->getProperties()->setCreator("Title")
->setLastModifiedBy("Title")
->setTitle("Contact Message")
->setSubject("Contact Message");



$objPHPExcel->setActiveSheetIndex(0)
->setCellValueByColumnAndRow(0, 1, "Name")
->setCellValueByColumnAndRow(1, 1, "Email")
->setCellValueByColumnAndRow(2, 1, "Message")
->setCellValueByColumnAndRow(3, 1, "Posted");

$row_count = 2;
foreach ($rows as $row) {
$objPHPExcel->setActiveSheetIndex(0)
->setCellValueByColumnAndRow(0, $row_count, $row->name)
->setCellValueByColumnAndRow(1, $row_count, $row->email)
->setCellValueByColumnAndRow(2, $row_count, $row->message)
->setCellValueByColumnAndRow(3, $row_count, $row->created_at);
$row_count++;
}

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('contact_message');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

ob_clean();
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="contract_messages.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
Jun 20 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.