How to export multilanguage Unicode records to multiple text files

[World33]

Hi,

I have not been able to find a solution to these two issues for months.

1) My Table "UNIVERSITIESOtherName" is made of two columns ID, UniversityOther (university names in non-english characters):

ID UniversityOther
87 "?????? ""?????????"" ???????????"
190 ????? ?????? ??????
191 ??????? ????????
192 ????? ???????
237 ??????????? ??????????????? ????????????? ???????????

I need to export them to multiple text files each named as ID.txt and containing the UniversityOther cell content.

I tried with the module below but I get ?????????? characters instead of the original ones.
I guess I have to somehow tell the vba script to set/convert the characters to Unicode UTF-8 and/or to use a previously saved Export Specification Schema.

Here the Function code I am using at the moment:


Code:

Function Save_to_file()
Dim rst        As DAO.Recordset
Dim x          As String
  
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM UNIVERSITIESOtherName")

    Do Until rst.EOF
        x = FreeFile
        Open "C:\temp\" & rst!ID & ".txt" For Append As x
        Print #x, rst!UniversityOther
        Close #x
        rst.MoveNext
    Loop
    
    rst.Close
    Set rst = Nothing

Save_to_file_Exit:
    Exit Function

End Function

The question is how to modify the above VBA script function to properly display unicode UTF-8 characters in the exported text files?

2) The second minor problem is that whenever i run the above Export Function module, content is appended to the previous one instead of being replaced
So I get ID.txt files containing several lines of question marks depending on how many times i run the module script:

???????????
???????????
???????????

How can I modify the above script so that it replaces the existing content instead of appending to it?

Thank you very much for your time and suggestions.

 

[jrbarnett]

Combining your code with

http://accessblog.net/2007/06/how-to-write-out-unicode-text-files-in.html

I get:

Function Save_to_file()
Dim rst        As DAO.Recordset
Dim x          As String
  Dim buffer As String
  
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM table1")

    Do Until rst.EOF
        x = FreeFile
        
        Open "C:\users\john\" & rst!ID & ".txt" For Binary As x
        buffer = StrConv(rst!UniversityOther, vbUnicode)
        
        Put #x, , buffer
        Close #x
        rst.MoveNext
    Loop
    
    rst.Close
    Set rst = Nothing

Save_to_file_Exit:
    Exit Function

End Function

which seems to work. This also addresses the appending data issue, by using a binary file type rather than appending to text mode.

John

 

[jrbarnett]

Oh, you will need to change C:\Users\John\ to C:\temp\.

John

 

[World33]

Thanks a lot John,

It works great. The only issue is with Chinese and Japanese characters. Arabic, Armenian, Russian, Israeli and even Korean are properly exported. Not sure if depends on the MS office language installation pack that I might be missing or the code itself. Any idea?
I attach a sample excel table with characters from all languages

Thanks again!!!