String conversion from ascii to utf8

Question

I am working on data extraction project using vba,in my code i have have string having data and during text file generation i want to convert whole text to utf-8 encoding string. My code is given below.

Set fso = CreateObject("Scripting.FileSystemObject")
Dim Fileout As Object                                                                                                            
Set Fileout = fso.CreateTextFile("C:\Data\colorsizew" & Format(Now(), "_yyyy-mm-dd_hh-mm-ss") "_" & ".txt", True, True)          

'Set Fileout = StrConv(Fileout, vbUnicode)                                                                                       

Dim dbs As DAO.Database                                                                                                          
Set dbs = CurrentDb                                                                                                              

news = asin_map                                                                                                                  
news = MyClean(news)                                                                                                             
'news = MyClean(news)                                                                                                            
news = Replace(news, "|", vbLf)                                                                                                  
news = Replace(news, "{}", "|")                                                                                                  
'news=iconv -c -t utf8 filename.csv > filename.utf8.csv                                                                          
'---------------------utf-8                                                                                                      
'here i want to convert news string in to utf-8                                                                                   
'------------------ends here                                                                                                     

Fileout.Write news

All strings are UTF 16. When VBA deals with the OS it converts it to ANSI because that's all Windows 95 understood. You have the `StrConv` function. However anyting other than UTF 16 needs to be in a byte array. `Dim A as Byte()` and `A="Cat"`. — , May 13 '20 at 10:07
A valid ASCII string is a valid UTF-8 string. ASCII is a subset of UTF-8. If you truly want to convert ASCII to UTF-8, you need to do absolutely nothing. — Erik A, May 13 '20 at 10:25
But after file generation during import in DB nothing is visible and after manual utf-8 conversion import is proper and data is visible. — imran satti, May 13 '20 at 10:27
Related question: [utf 8 - Save text file UTF-8 encoded with VBA - Stack Overflow](https://stackoverflow.com/questions/2524703/save-text-file-utf-8-encoded-with-vba?noredirect=1&lq=1) — user202729, Oct 05 '21 at 12:06

score 0 · Answer 1 · answered May 13 '20 at 11:21

0

You can use Microsoft ActiveX Data Objects (ADO) to save the file in UTF-8 format. Try the following code...

Early Binding

First set a reference to Microsoft ActiveX Data Objects X.X Library (Visual Basic Editor >> Tools >> Referece).

Dim streamObject As ADODB.Stream
Set streamObject = New ADODB.Stream

Dim news As String
news = "string here. . ."

With streamObject
    .Charset = "utf-8"
    .Mode = adModeReadWrite
    .Type = adTypeText
    .Open
    .WriteText Data:=news
    .SaveToFile Filename:="C:\Data\colorsizew" & Format(Now(), "_yyyy-mm-dd_hh-mm-ss") "_" & ".txt", Options:=adSaveCreateOverWrite
    .Close
End With

Late Binding

Dim streamObject As Object
Set streamObject = CreateObject("ADODB.Stream")

Dim news As String
news = "string here. . ."

With streamObject
    .Charset = "utf-8"
    .Mode = 3 'adModeReadWrite
    .Type = 2 'adTypeText
    .Open
    .WriteText Data:=news
    .SaveToFile Filename:="C:\Data\colorsizew" & Format(Now(), "_yyyy-mm-dd_hh-mm-ss") "_" & ".txt", Options:=2 'adSaveCreateOverWrite
    .Close
End With

answered May 13 '20 at 11:21

Domenic

7,844
2
9
17

That saves UTF-16 as UTF-8. Strings in VBA are UTF-16, as noted in the comments. – Erik A May 13 '20 at 11:26
In above code i think in late binding every time new file will be created as i am using loop in data – imran satti May 14 '20 at 06:07
Have a look at https://stackoverflow.com/questions/10450156/write-text-file-in-appending-utf-8-encoded-in-vb6 where you'll see a workaround for the Stream object, and you'll also find an alternative solution that combines binary I/O and an API call to perform the conversion to UTF-8. – Domenic May 14 '20 at 11:44
no issue in above code,only issue in mutiple file generation after each next loop. – imran satti May 17 '20 at 06:58
Sorry, it's not entirely clear what it is you want to do. In any case, since it looks like this is a new aspect to your original question, I suggest you start a new thread, where you can post your new existing code and question. – Domenic May 17 '20 at 15:02
Only one change required i.e in late binding already create file will be used – imran satti May 19 '20 at 00:10

String conversion from ascii to utf8

1 Answers1