exporting unicode strings from Access with VBA

[havanna]

I have some russian strings in Access (2000 Version and no way to upgrade, sorry, and VBA isn't my biggest strength)
Exporting those tables to excel works fine, but I can't manage to export in a script to a flat text file, instead I get the dreaded ??????????.

Is there a way to do it?

[NeoPa]

Excuse my ignorance, but doesn't ?????????? indicate that the data is in Unicode but the application or system doesn't recognise it?

[havanna]

Yes, I can export my data to xls and get the right characters, so data is unicode.
It's just exporting to a text file with VBA that causes trouble.

[NeoPa]

Let me try to put this in simpler terms as you don't seem to be following what I'm trying to say :

What is it that makes you think the trouble is with the outputting of the data from VBA and not the interpreting of the data in the file by whatever you are using to view the data afterwards?

[havanna]

Ah, I get your point.
in the current minimum script I just read out the contents of all fields in all rows of a table and write that to a text file.
The result is ANSI, with multibyte characters as hexvalue 3F.

[NeoPa]

havanna:
The result is ANSI, with multibyte characters as hexvalue 3F.

The result is actually a file with a sequence of bits. How those bits are interpreted depends on the application you choose to view them with. Are you using NotePad?

I haven't really used Unicode much I must admit, but I did have a look and found that when I saved the same data ("22 ") as both ANSI and Unicode, the only difference was the data saved. When I took out the extra Unicode-specific data with a hex editor the file was recognised as simple ANSI.

[NeoPa]

Maybe we need to have a look at your code if it's not too large. Please post what you have for the Excel export as well as that for the Text export and we'll see if we can spot any problems.

[havanna]

I'm using scite for viewing, that can handle both codings and checked with a hex editor.
Access exports tables as excel directly, there are no options to set.
For text export I use the Cinergy Script attached.
There are several conversions made here wich may have extra effects, but I have tried with the simplest possible (just pseudocode)
Then Open testfile.txt For Output As #5
with mytable
loop through rows
loop trhough fields
print #5, table.row.field.value
endloop
endloop
close #5

It just writes standard ANSI, ? (0x32) , one byte, no multibite characters.

[NeoPa]

I'm sorry, but as an Access expert I don't provide support for third party software (I'm not able to as my expertise is in Access). If you want help using Cynergi code then you may be better advised to look for a specific Cynergi forum somewhere. I do not propose to debug and test 680 lines of code for you without even the benefit of your original environment.

As for the actual VBA code you are referring to (in your pseudo-code I think), I can't see it as you posted something else instead (I have no idea why as I asked to see your code). Please refer back to post #7 as that still stands. I can check for problems in VBA code, but I couldn't possibly do that with pseudo-code could I?

By the way, it may be a good idea for you to put your current database aside for now and create a new (very small/basic) one solely for the purpose of getting a good grip on the Unicode issue. That way, instead of needing to send hundreds of lines of code if we need to share code, you can send only the relevant code and save loads of time and effort.

[havanna]

Sorry, didn't mean to ask 3rd party support - and thanks for your being willing to help at all!
I've done that, mdb with just one table containing minimum data including some udf strings (russian).
I've reduced the script to just read fields and put contents into a file.

The mdb is attached as zip, also the output.txt file made by the vba module

maybe you know how to make the script deal with unicode:

Public Function exportutf()
 
   Dim ctablename As String
   Dim cfieldix As Integer, cfieldname As String
   Dim fieldlst As String, sqlcode As String
   Dim crs As DAO.Recordset
   Dim cdb As database
   Dim Output As String
 
   Set cdb = CurrentDb()
   Open "C:\output.txt" For Output As #1
   Set crs = cdb.OpenRecordset("TextIdSubLand")
   ' loop thorugh each record in the table
    crs.MoveFirst
    Do Until crs.EOF
        ' loop through each field in each record
        For cfieldix = 0 To crs.Fields.Count - 1
            Output = Output & crs.Fields(cfieldix).Value & ";"
      Next cfieldix
 
        Output = Output & "\n"
 
        Print #1, Output
      crs.MoveNext
      Output = ""
    Loop
    Close #1
    cdb.Close
End Function
 
        Output = Output & "\n"
 
        Print #1, Output
      crs.MoveNext
      Output = ""
   Loop
 
    Close #1
 
    cdb.Close
 
End Function

[NeoPa]

That's a good response Havanna. There appears to be some strange extra code at the end of (after) your procedure, but I expect that was an oversight. CODE tags are required here and they may have made that more obvious when you posted but never mind this time.

Actually, if you are trying to create C:\output.txt as a Unicode file, then I think I can help you. It may not be today as you just missed the window (I need to go and play football shortly and get prepared before that), but this approach is perfectly viable, although will probably require some advanced and detailed understanding of working directly with Binary files from VBA (Luckily I have that understanding and some experience in that area). We won't be using Print#, but Put# instead. More to follow.

[NeoPa]

Although I developed the following code, I could not get it to work as required for the simple reason that VBA doesn't handle storing the data - even though it can be stored in a Memo field as your database shows :

Option Compare Database
Option Explicit
 
Private lngFilePos As Long
 
Public Function ExportUTF()
    Dim strFileName As String, strOut As String
    Dim intFileNo As Integer
    Dim cdb As DAO.Database
    Dim cField As DAO.Field
 
    Set cdb = CurrentDb()
    strFileName = "C:\output.txt"
    intFileNo = FreeFile()
    Open strFileName For Binary Access Read Write Lock Write As #intFileNo
    With cdb.OpenRecordset("TextIdSubLand")
        ' loop thorugh each record in the table
        Call .MoveFirst
        lngFilePos = 1
        Do Until .EOF
            ' loop through each field in each record
            varOut = ""
            For Each cField In .Fields
                varOut = varOut & ";" & cField
            Next cField
            varOut = Mid(varOut, 2) & vbNewLine
            Call WriteString(intFileNo, varOut)
            Call .MoveNext
        Loop
        Call .Close
    End With
    Close #intFileNo
End Function
 
Private Sub WriteString(intFileNo As Integer, varData As Variant)
    Dim intIx As Integer, intData As Integer
 
    For intIx = 1 To Len(varData)
        intData = Asc(Mid(varData, intIx))
        Put #intFileNo, lngFilePos, intData
        lngFilePos = lngFilePos + 2
    Next intIx
End Sub

Other than the fact that it doesn't have the data to work with though, the code works perfectly. It produces a correctly formatted Unicode file as long as no characters requiring Unicode are included in the output - LoL.

All that said, the job is quite easily accomplished by doing a simple export to text. Simply select the ";" as a delimiter char and set the Code Page to Unicode in the Advanced section. The file it creates appears to be exactly what you want.

[havanna]

That indeed produced a unicode file (I don't understand your statemend: I could not get it to work as required for the simple reason that VBA doesn't handle storing the data).
It works perfectly with my data - and delivers unicode-formatted "?"
But of course the last hint, text export with correct codepage is what really does it, thanks a lot :-)

[NeoPa]

Havanna:
It works perfectly with my data - and delivers unicode-formatted "?"

Indeed. That's what I got too.

The code produces a correctly formatted file, but the unicode data that is stored in the Memo field is translated to non-unicode compatible characters as soon as it's assigned to a string variable (I also tried with a Variant variable by the way which gave the same results). The code sort of works, but the results are useless because VBA can't store the data.

I figured the Export to text would do the trick though. I assume you know all about how to automate that? If not then let me know. It's easily handled.

[havanna]

Yes, thank you. Automating my export with DoCmd.TransferText is no problem. Further Processing I can handle at the receiving end, ti's just some extra code I hoped to avoid.

[NeoPa]

Havanna:
Further Processing I can handle at the receiving end, it's just some extra code I hoped to avoid.

I don't follow. I was only aware of wanting to produce the file. If there is more required for the whole process why not post another question for it and we'll see if we can't help with that too.