Text File Encode to UTF-8_Unicode specific
 

Dear colleagues,
Is there any way to change/specify text file encoding to
UTF-8(Unicode) specific from VBA. I export some data table from Excel
to text file and manage to specify encode for such file 'just' as
Unicode, but the problem is that I need the encode to be exactly
UTF-8_Unicode. I have done this (export and encode setting) through
Excel_VBA embeded method 'WorkSheet.SaveAs' 'Path_FileName.Extension',
FileFormat:=xlUnicodeText. Excel_VBA offers some 45 xlFileFormat
constants but among them there is no such as 'UTF-8_Unicode'. I suppose
that 'SaveAs' method is a wraper for some WinAPI function which actualy
perfom such encode change, by aplying the value of FileFormat
argument(in case =xlUnicodeText the value is 42). In Windows XP there
is an option to perform manual encode change via general(not
Office/Excel) dialog box 'SaveAs' which contain 'Encode' ComboBox with
option 'UTF-8' specific. So I have try to do the same through VBA code,
but WinAPI function 'SetFileAtributes' doesn't offer such option. Also
through VBA- 'CreateObject(Scripting.FileSystemObject)' I can't find
the way to specify/change File_Object encoding to UTF-8 specific.
Wider view: I need the exact UTF-8_Unicode Encode, because the data
from such text file, further on, has to be imported into MySQL Server
table, by using 'LOAD DATA INFILE', and MySQL, on the Client side,
accepts(from all Uncode formats) only the UTF-8_Unicode. The text file
exported from Excel(FileFormat:=xlUnicodeText), s.c. 'Plane_Unicode'
isn't correctly readable in MySQL(doesn read correctly 'FIELDS
TERMINATED BY' and 'LINES TERMINATED BY' i.e. fields-lines delimiter).
But, when I change file encode MANUALY(via Windows general 'SaveAs'
dialog box) from 'Plane'(xlUnicodeText)_Unicode to Unicode_'UTF-8',
then MySQL loads data to table correctly. I have try to determine
fields-lines delimiter in 'Plane'(xlUnicodeText)_Unicode text file, by
re-import such file back to Excel and Excel DataImport Wizard see the
'Tab' as fields delimiter but MySQL doesn't or doesn't recognize it.
So please, I will be very thankfull for any solution or advice
regarding this problem, Thank You in advance.

Text File Encode to UTF-8_Unicode specific
 

You can try:

Sub Encode(ByVal sPath$, Optional SetChar$ = "UTF-8")
With CreateObject("ADODB.Stream")
..Open
..LoadFromFile sPath ' Loads a File
..Charset = SetChar ' sets stream encoding (UTF-8)
..SaveToFile sPath, 2 ' adSaveCreateOverWrite
..Close
End With
End Sub

Regards,
MP

a écrit dans le message de news:
...
Dear colleagues,
Is there any way to change/specify text file encoding to
UTF-8(Unicode) specific from VBA. I export some data table from Excel
to text file and manage to specify encode for such file 'just' as
Unicode, but the problem is that I need the encode to be exactly
UTF-8_Unicode. I have done this (export and encode setting) through
Excel_VBA embeded method 'WorkSheet.SaveAs' 'Path_FileName.Extension',
FileFormat:=xlUnicodeText. Excel_VBA offers some 45 xlFileFormat
constants but among them there is no such as 'UTF-8_Unicode'. I suppose
that 'SaveAs' method is a wraper for some WinAPI function which actualy
perfom such encode change, by aplying the value of FileFormat
argument(in case =xlUnicodeText the value is 42). In Windows XP there
is an option to perform manual encode change via general(not
Office/Excel) dialog box 'SaveAs' which contain 'Encode' ComboBox with
option 'UTF-8' specific. So I have try to do the same through VBA code,
but WinAPI function 'SetFileAtributes' doesn't offer such option. Also
through VBA- 'CreateObject(Scripting.FileSystemObject)' I can't find
the way to specify/change File_Object encoding to UTF-8 specific.
Wider view: I need the exact UTF-8_Unicode Encode, because the data
from such text file, further on, has to be imported into MySQL Server
table, by using 'LOAD DATA INFILE', and MySQL, on the Client side,
accepts(from all Uncode formats) only the UTF-8_Unicode. The text file
exported from Excel(FileFormat:=xlUnicodeText), s.c. 'Plane_Unicode'
isn't correctly readable in MySQL(doesn read correctly 'FIELDS
TERMINATED BY' and 'LINES TERMINATED BY' i.e. fields-lines delimiter).
But, when I change file encode MANUALY(via Windows general 'SaveAs'
dialog box) from 'Plane'(xlUnicodeText)_Unicode to Unicode_'UTF-8',
then MySQL loads data to table correctly. I have try to determine
fields-lines delimiter in 'Plane'(xlUnicodeText)_Unicode text file, by
re-import such file back to Excel and Excel DataImport Wizard see the
'Tab' as fields delimiter but MySQL doesn't or doesn't recognize it.
So please, I will be very thankfull for any solution or advice
regarding this problem, Thank You in advance.