469,287 Members | 2,764 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,287 developers. It's quick & easy.

exporting unicode strings from Access with VBA

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?
Jun 7 '11 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private lngFilePos As Long
  5.  
  6. Public Function ExportUTF()
  7.     Dim strFileName As String, strOut As String
  8.     Dim intFileNo As Integer
  9.     Dim cdb As DAO.Database
  10.     Dim cField As DAO.Field
  11.  
  12.     Set cdb = CurrentDb()
  13.     strFileName = "C:\output.txt"
  14.     intFileNo = FreeFile()
  15.     Open strFileName For Binary Access Read Write Lock Write As #intFileNo
  16.     With cdb.OpenRecordset("TextIdSubLand")
  17.         ' loop thorugh each record in the table
  18.         Call .MoveFirst
  19.         lngFilePos = 1
  20.         Do Until .EOF
  21.             ' loop through each field in each record
  22.             varOut = ""
  23.             For Each cField In .Fields
  24.                 varOut = varOut & ";" & cField
  25.             Next cField
  26.             varOut = Mid(varOut, 2) & vbNewLine
  27.             Call WriteString(intFileNo, varOut)
  28.             Call .MoveNext
  29.         Loop
  30.         Call .Close
  31.     End With
  32.     Close #intFileNo
  33. End Function
  34.  
  35. Private Sub WriteString(intFileNo As Integer, varData As Variant)
  36.     Dim intIx As Integer, intData As Integer
  37.  
  38.     For intIx = 1 To Len(varData)
  39.         intData = Asc(Mid(varData, intIx))
  40.         Put #intFileNo, lngFilePos, intData
  41.         lngFilePos = lngFilePos + 2
  42.     Next intIx
  43. 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.

15 6367
NeoPa
32,173 Expert Mod 16PB
Excuse my ignorance, but doesn't ?????????? indicate that the data is in Unicode but the application or system doesn't recognise it?
Jun 13 '11 #2
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.
Jun 14 '11 #3
NeoPa
32,173 Expert Mod 16PB
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?
Jun 14 '11 #4
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.
Jun 14 '11 #5
NeoPa
32,173 Expert Mod 16PB
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.

Attached Images
File Type: jpg ANSI.jpg (36.2 KB, 2523 views)
Jun 14 '11 #6
NeoPa
32,173 Expert Mod 16PB
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.
Jun 14 '11 #7
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.
Attached Files
File Type: txt ttt.txt (32.1 KB, 457 views)
Jun 14 '11 #8
NeoPa
32,173 Expert Mod 16PB
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.
Jun 14 '11 #9
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function exportutf()
  2.  
  3.    Dim ctablename As String
  4.    Dim cfieldix As Integer, cfieldname As String
  5.    Dim fieldlst As String, sqlcode As String
  6.    Dim crs As DAO.Recordset
  7.    Dim cdb As database
  8.    Dim Output As String
  9.  
  10.    Set cdb = CurrentDb()
  11.    Open "C:\output.txt" For Output As #1
  12.    Set crs = cdb.OpenRecordset("TextIdSubLand")
  13.    ' loop thorugh each record in the table
  14.     crs.MoveFirst
  15.     Do Until crs.EOF
  16.         ' loop through each field in each record
  17.         For cfieldix = 0 To crs.Fields.Count - 1
  18.             Output = Output & crs.Fields(cfieldix).Value & ";"
  19.       Next cfieldix
  20.  
  21.         Output = Output & "\n"
  22.  
  23.         Print #1, Output
  24.       crs.MoveNext
  25.       Output = ""
  26.     Loop
  27.     Close #1
  28.     cdb.Close
  29. End Function
  30.  
  31.         Output = Output & "\n"
  32.  
  33.         Print #1, Output
  34.       crs.MoveNext
  35.       Output = ""
  36.    Loop
  37.  
  38.     Close #1
  39.  
  40.     cdb.Close
  41.  
  42. End Function
Attached Files
File Type: txt output.txt (55.6 KB, 547 views)
File Type: zip texte.zip (65.3 KB, 149 views)
Jun 14 '11 #10
NeoPa
32,173 Expert Mod 16PB
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.
Jun 14 '11 #11
NeoPa
32,173 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private lngFilePos As Long
  5.  
  6. Public Function ExportUTF()
  7.     Dim strFileName As String, strOut As String
  8.     Dim intFileNo As Integer
  9.     Dim cdb As DAO.Database
  10.     Dim cField As DAO.Field
  11.  
  12.     Set cdb = CurrentDb()
  13.     strFileName = "C:\output.txt"
  14.     intFileNo = FreeFile()
  15.     Open strFileName For Binary Access Read Write Lock Write As #intFileNo
  16.     With cdb.OpenRecordset("TextIdSubLand")
  17.         ' loop thorugh each record in the table
  18.         Call .MoveFirst
  19.         lngFilePos = 1
  20.         Do Until .EOF
  21.             ' loop through each field in each record
  22.             varOut = ""
  23.             For Each cField In .Fields
  24.                 varOut = varOut & ";" & cField
  25.             Next cField
  26.             varOut = Mid(varOut, 2) & vbNewLine
  27.             Call WriteString(intFileNo, varOut)
  28.             Call .MoveNext
  29.         Loop
  30.         Call .Close
  31.     End With
  32.     Close #intFileNo
  33. End Function
  34.  
  35. Private Sub WriteString(intFileNo As Integer, varData As Variant)
  36.     Dim intIx As Integer, intData As Integer
  37.  
  38.     For intIx = 1 To Len(varData)
  39.         intData = Asc(Mid(varData, intIx))
  40.         Put #intFileNo, lngFilePos, intData
  41.         lngFilePos = lngFilePos + 2
  42.     Next intIx
  43. 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.
Jun 15 '11 #12
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 :-)
Jun 15 '11 #13
NeoPa
32,173 Expert Mod 16PB
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.
Jun 15 '11 #14
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.
Jun 16 '11 #15
NeoPa
32,173 Expert Mod 16PB
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.
Jun 16 '11 #16

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Jonathon Blake | last post: by
10 posts views Thread by Andrew L | last post: by
2 posts views Thread by Neil Schemenauer | last post: by
5 posts views Thread by Jamie | last post: by
2 posts views Thread by Fuzzyman | last post: by
14 posts views Thread by Dennis Benzinger | last post: by
2 posts views Thread by Tom Plunket | last post: by
4 posts views Thread by Alexey Moskvin | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.