Output UTF-8 encoding through VBA

[Albert S.]

Hello,

We need to export a query from Access to a text file using UTF-8 encoding. I
have seen that the export

query wizard has an option to export as UTF-8, but I don't see how to do
this in vba. We need this exact

format because the text file has strict guidelines for use. The query name
is qryMARC and it will contain up

to 18 lines of text for each record. I also saw the object
"System.Text.UTF8", but I'm not sure how to use

that with this output.

Thanks,
Albert

Here is the code:

Private Sub cmdWrite_Click()
Dim FileNumber As Integer
Dim strLine As String
Dim rs As DAO.Recordset
Dim i As Integer
Dim strMakrPath As String
Dim strFullPath As String
Dim strFormatedFile As String

strFormatedFile = "FileName"
FileNumber = FreeFile
strMakrPath = "C:\MARC\marcmakr.exe"

Set rs = CurrentDb.OpenRecordset("qryMARC", dbOpenDynaset, dbSeeChanges)
Open "C:\MARC\TF" & strFormatedFile & ".txt" For Output As #FileNumber

Do While Not rs.EOF
For i = 0 To 18
strLine = "" & Nz(rs.Fields(i))
If Len(strLine) > 0 Then
Print #FileNumber, strLine
End If
Next i
Print #FileNumber,
rs.MoveNext
Loop

strFullPath = strMakrPath & " " & "C:\MARC\TF" & strFormatedFile & ".txt"
& " " & "C:\MARC\TF" &

strFormatedFile & ".mrc" & " " & "C:\MARC\text21.txt"
Shell strFullPath, vbHide

End Sub

 

[Dirk Goldgar]

Hello,

We need to export a query from Access to a text file using UTF-8 encoding.
I
have seen that the export

query wizard has an option to export as UTF-8, but I don't see how to do
this in vba. We need this exact

format because the text file has strict guidelines for use. The query name
is qryMARC and it will contain up

to 18 lines of text for each record. I also saw the object
"System.Text.UTF8", but I'm not sure how to use

that with this output.

Thanks,
Albert

Here is the code:

Private Sub cmdWrite_Click()
Dim FileNumber As Integer
Dim strLine As String
Dim rs As DAO.Recordset
Dim i As Integer
Dim strMakrPath As String
Dim strFullPath As String
Dim strFormatedFile As String

strFormatedFile = "FileName"
FileNumber = FreeFile
strMakrPath = "C:\MARC\marcmakr.exe"

Set rs = CurrentDb.OpenRecordset("qryMARC", dbOpenDynaset, dbSeeChanges)
Open "C:\MARC\TF" & strFormatedFile & ".txt" For Output As #FileNumber

Do While Not rs.EOF
For i = 0 To 18
strLine = "" & Nz(rs.Fields(i))
If Len(strLine) > 0 Then
Print #FileNumber, strLine
End If
Next i
Print #FileNumber,
rs.MoveNext
Loop

strFullPath = strMakrPath & " " & "C:\MARC\TF" & strFormatedFile &
".txt"
& " " & "C:\MARC\TF" &

strFormatedFile & ".mrc" & " " & "C:\MARC\text21.txt"
Shell strFullPath, vbHide

End Sub

I can't help thinking there ought to be a simpler way, but Brendan Reynolds
posted this technique using ADO a while ago:

http://groups.google.com/group/micr..._frm/thread/bb9d59b5e400adf5/450a88f65f90dd59

 

[Albert S.]

Ok, got the objStream running, but everytime a new line is written, it
overwrites everything so I am only getting one line. What I need to do is
print the 18 lines of the first record (each field on one line - so followed
by a carraige return i.e. vbCrLf). Then I need to have a blank line, followed
by the next record. I probably have the loop in the wrong place. Here is an
example of the output:

field1
field2
field3...

field1
field2
field3...

Here is the code:
Set rs = CurrentDb.OpenRecordset("qryMARC", dbOpenDynaset, dbSeeChanges)

Do While Not rs.EOF
For i = 0 To 18
strLine = "" & Nz(rs.Fields(i))
If Len(strLine) > 0 Then
objStream.Open
objStream.Position = 0
objStream.Charset = "UTF-8"
objStream.WriteText strLine
objStream.SaveToFile "C:\MARC\TF" & strFormatedFile & ".txt",
adSaveCreateOverWrite
objStream.Close
End If
Next i
rs.MoveNext
Loop

Thanks for the help!

 

[Dirk Goldgar]

Ok, got the objStream running, but everytime a new line is written, it
overwrites everything so I am only getting one line. What I need to do is
print the 18 lines of the first record (each field on one line - so
followed
by a carraige return i.e. vbCrLf). Then I need to have a blank line,
followed
by the next record. I probably have the loop in the wrong place. Here is
an
example of the output:

field1
field2
field3...

field1
field2
field3...

Here is the code:
Set rs = CurrentDb.OpenRecordset("qryMARC", dbOpenDynaset, dbSeeChanges)

Do While Not rs.EOF
For i = 0 To 18
strLine = "" & Nz(rs.Fields(i))
If Len(strLine) > 0 Then
objStream.Open
objStream.Position = 0
objStream.Charset = "UTF-8"
objStream.WriteText strLine
objStream.SaveToFile "C:\MARC\TF" & strFormatedFile & ".txt",
adSaveCreateOverWrite
objStream.Close
End If
Next i
rs.MoveNext
Loop

I would assum you would have to take the opening, configuring, and closing
of the stream out of the loop. More like this:

'------ start of revised code ------
Set rs = CurrentDb.OpenRecordset("qryMARC", dbOpenDynaset, dbSeeChanges)

objStream.Open
objStream.Position = 0
objStream.Charset = "UTF-8"

Do While Not rs.EOF
For i = 0 To 18
strLine = rs.Fields(i) & ""
If Len(strLine) > 0 Then
objStream.WriteText strLine
End If
Next i
rs.MoveNext
Loop

objStream.SaveToFile _
"C:\MARC\TF" & strFormatedFile & ".txt", _
adSaveCreateOverWrite

objStream.Close
'------ end of revised code ------

I'm not sure but what you might have to append a CR/LF combination on the
end of each line. If so, you would do it like this:

objStream.WriteText strLine & vbCrLf

All of this is purely speculative, since I haven't tried this.

 

[Albert S.]

Excellent! I added one more vbCrLf between each record and they were exported
correctly. But, when I went to read the file, I got an error that it was a
UTF-16? I'm reading it in a program that is not related to vba. Anyway, the
text output looks correct.

Thank you for the help!!

 

[Dirk Goldgar]

Excellent! I added one more vbCrLf between each record and they were
exported
correctly. But, when I went to read the file, I got an error that it was a
UTF-16?

That I can't explain.

I'm reading it in a program that is not related to vba. Anyway, the text
output looks correct.

So far, so good, I guess. I don't know if you are able to use this output
or not, though.

Thank you for the help!!

You're welcome.

 

[Albert S.]

Yes, quite funny about the error. I changed the output to "ascii" and had no
problems reading the file...

Thanks again!