时间戳

首页 > 解决方案 > CSV file with non-English (Hebrew etc.)

csv - CSV file with non-English (Hebrew etc.)

问题描述

I have a hosted site with access to cPanel where I have a daily cron job executing a PHP script. This script exports a MySQL table to CSV. I then have a scheduled job on my Windows here at the office that FTPs this CSV to my local machine. So far so good.

But the MySQL table has mixed English and Hebrew data in it. Via the FileManager of the cPanel I see the Hebrew in the created CSV correctly, but after FTPing it to my local machine the Hebrew is unreadable.

EDIT :

When opening the downloaded CSV in Office-2016 the problem persists. When opening it with Notepad++ or MS-Notepad - the Hebrew appears ok.

This means that the file is downloaded correctly and the problem lays in the MS-Office.

The thing is that this CSV is to be used as input to an Excel macro (XLSM) which runs automatically nightly. I found that in Excel I can manually "Import" the CSV to a sheet and the encoding is fine and the Hebrew is ok. I recorded a macro and the VBA now does the job nicely. I then found it has already been mentioned in Opening tsv file via Notepad++ and save it in text format

END OF EDIT

The PHP script (notice the 'SET NAMES utf8') :

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
$username = "XXX";$password ="YYY";$dbname = "ZZZ";
try {
   $conn = new PDO('mysql:host=localhost;dbname='.$dbname, $username, $password);
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   $conn->query('SET NAMES utf8');
   $stmt = $conn->prepare("SELECT comp_id, comp_name FROM companies");
   $stmt->execute();
   $file_export  =  '/home/darushnisayon/public_html/vehadarta/Exported_tables_from_DB/AA_companies.csv';
   $data = fopen($file_export, 'w');
   $csv_fields = array();
   $csv_fields[] = 'comp_id';
   $csv_fields[] = 'comp_name';
   fputcsv($data, $csv_fields);
   while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
       fputcsv($data, $row);
   }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
?>

My Windows FTP job (notice the BINARY option) :

    @Echo Off
    Set _FTPServerName=nn.nn.nnn.nnn
    Set _UserName=XXX
    Set _Password=YYY
    Set _LocalFolder=C:\Dropbox\GADI\Vehadarta\Routine_Tasks\T002_Daily_Check_if_Synced
    Set _RemoteFolder=public_html/vehadarta/Exported_tables_from_DB/
    Set _Filename=AA_companies.csv
    Set _ScriptFile=ftp1
    :: Create script
     >"%_ScriptFile%" Echo verbose
    >>"%_ScriptFile%" Echo open %_FTPServerName%
    >>"%_ScriptFile%" Echo %_UserName%
    >>"%_ScriptFile%" Echo %_Password%
    >>"%_ScriptFile%" Echo lcd %_LocalFolder%
    >>"%_ScriptFile%" Echo cd %_RemoteFolder%
    >>"%_ScriptFile%" Echo prompt
    >>"%_ScriptFile%" Echo binary
    >>"%_ScriptFile%" Echo get %_Filename%
    >>"%_ScriptFile%" Echo quit
    :: Run script
    ftp -s:"%_ScriptFile%"
    Del "%_ScriptFile%"

The CSV file seen at the cPanel :

comp_id,comp_name
1,"קשרי עסקים בע""מ"
2,ASK
3,DCL
4,"אסטרטגיה וליווי עסקי  S.M.C"

The CSV file on my local dir after FTP :

comp_id comp_name
1   ׳§׳©׳¨׳™ ׳¢׳¡׳§׳™׳ ׳‘׳¢"׳
2   ASK
3   DCL
4   ׳׳¡׳˜׳¨׳˜׳’׳™׳” ׳•׳׳™׳•׳•׳™ ׳¢׳¡׳§׳™  S.M.C

Thank you for any idea.

标签： csvencodinghebrew

解决方案

正如我在EDIT中提到的，问题出在 MS-Office 读取 CSV 的方式上。在记事本中打开文件时，编码是正确的。由于要将 CSV 复制到 Excel 电子表格中，我所要做的就是将 CSV 导入 Excel。

非常感谢 Martin Prikryl，他给了我解决方案的第一个提示。

推荐阅读