CSV file with non-English (Hebrew etc.)

Question

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.

How do you open/check the file in your local directory? Can you show us a hex-dump of the file? Can you show us a hex dump of the file downloaded from cPanel? — Martin Prikryl, Jan 21 '19 at 10:03
@Martin Prikryl - I open the downloaded CSV file with MS-Office-2016. But now that you have asked, I opened it with Notepad++ and lo and behold - the Hebrew is alive and well... So please hold your thought while I further investigate. Will be back here soon to update. Thanks a bunch! — gadi, Jan 21 '19 at 10:40
@Martin Prikryl - please look at my edit. Thanks again, you paved my way to the solution. — gadi, Jan 21 '19 at 12:20
I'm glad that you have found a solution. Though your question is actually invalid. Consider deleting it, and asking a new question on your real problem with VBA. — Martin Prikryl, Jan 21 '19 at 12:36
@Martin Prikryl - Well, I think it will still be very useful for others who'll come across the whole process I went through. I'll leave it for the forum's moderators to decide whether to delete it or not. Thanks. — gadi, Jan 21 '19 at 12:52
So at least edit out all references to FTP from your question, as the question has nothing to do with FTP. — Martin Prikryl, Jan 22 '19 at 14:18

score 1 · Accepted Answer · answered Jan 21 '19 at 12:59

As I mentioned in my EDIT, the problem was in the way MS-Office reads the CSV. When opening the file in Notepad the encoding is correct. Since the CSV is to be copied into am Excel spreadsheet, all I had to do was to 'Import' the CSV into the Excel.

Many thanks to Martin Prikryl who gave me the first hint for the solution.

CSV file with non-English (Hebrew etc.)

1 Answers1