Character set wrong

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Character set wrong

Post by sal21 »

i save a text file with:

Code: Select all


Option Explicit
Sub ESPORTA_CSV()

    Dim F As Long, R As Long
    Dim CNN As ADODB.Connection
    Dim RST As ADODB.Recordset
    Dim I As Long
    Dim FLD As String
    Dim TXT As String
    F = FreeFile

    Open "C:\DIEGO_VB6\Text.csv" For Output As #F

    Set CNN = New ADODB.Connection
    CNN.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=C:\DIEGO_VB6\riggi2-COUPON.az;" & _
                           "Jet OLEDB:Database Password=dgo17bru29"
    CNN.Open

    Set RST = New ADODB.Recordset
    RST.Open Source:="SELECT CODART, DESCRIZIONE, DESCRIZIONE2, DESCRIZIONE3, DESCRITTIVO, " & _
                     "CHIUSURA FROM ARTICOLI_NOTETECNICHE", ActiveConnection:=CNN, Options:=adCmdText

    R = 0

    Do While Not RST.EOF
        TXT = ""
        For I = 0 To RST.Fields.Count - 1
            FLD = RST(I)
            FLD = Replace(FLD, Chr(34), Chr(39))
            FLD = Replace(FLD, vbCr, " ")
            FLD = Replace(FLD, vbLf, " ")
            TXT = TXT & ";" & Chr(34) & FLD & Chr(34)
        Next I

        Print #F, Mid(TXT, 2)

        RST.MoveNext

        Me.LAZIONI.Caption = R
        DoEvents

    Loop

    Me.LAZIONI.Caption = "FINE EXPORT!"
    DoEvents

    Close #F
    RST.Close
    Set RST = Nothing
    CNN.Close
    Set CNN = Nothing

    Me.Text1.SetFocus

End Sub
but in a filed have:

riduce la quota di carboidrati ed è molto appetibile.

if i import in Google document have:

riduce la quota di carboidrati ed č molto appetibile.

see attached img

note:
a friend tell me to use utf character set when save file, wath is?
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Character set wrong

Post by HansV »

Try this:

Code: Select all

Sub ESPORTA_CSV()
    Dim STRM As ADODB.Stream
    Dim R As Long
    Dim CNN As ADODB.Connection
    Dim RST As ADODB.Recordset
    Dim I As Long
    Dim FLD As String
    Dim TXT As String

    Set STRM = New ADODB.Stream
    STRM.Type = adTypeText
    STRM.Charset = "utf-8"
    STRM.Open

    Set CNN = New ADODB.Connection
    CNN.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\DIEGO_VB6\riggi2-COUPON.az;" & _
        "Jet OLEDB:Database Password=dgo17bru29"
    CNN.Open

    Set RST = New ADODB.Recordset
    RST.Open Source:="SELECT CODART, DESCRIZIONE, DESCRIZIONE2, DESCRIZIONE3, DESCRITTIVO, " & _
                     "CHIUSURA FROM ARTICOLI_NOTETECNICHE", ActiveConnection:=CNN, Options:=adCmdText

    R = 0

    Do While Not RST.EOF
        TXT = ""
        For I = 0 To RST.Fields.Count - 1
            FLD = RST(I)
            FLD = Replace(FLD, Chr(34), Chr(39))
            FLD = Replace(FLD, vbCr, " ")
            FLD = Replace(FLD, vbLf, " ")
            TXT = TXT & ";" & Chr(34) & FLD & Chr(34)
        Next I

        STRM.WriteText TXT

        RST.MoveNext

        Me.LAZIONI.Caption = R
        DoEvents
    Loop

    STRM.SaveToFile "C:\DIEGO_VB6\Text.csv"

    Me.LAZIONI.Caption = "FINE EXPORT!"
    DoEvents

    RST.Close
    Set RST = Nothing
    CNN.Close
    Set CNN = Nothing

    Me.Text1.SetFocus
End Sub
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: Character set wrong

Post by sal21 »

HansV wrote:
16 Mar 2022, 21:46
Try this:

Code: Select all

Sub ESPORTA_CSV()
    Dim STRM As ADODB.Stream
    Dim R As Long
    Dim CNN As ADODB.Connection
    Dim RST As ADODB.Recordset
    Dim I As Long
    Dim FLD As String
    Dim TXT As String

    Set STRM = New ADODB.Stream
    STRM.Type = adTypeText
    STRM.Charset = "utf-8"
    STRM.Open

    Set CNN = New ADODB.Connection
    CNN.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\DIEGO_VB6\riggi2-COUPON.az;" & _
        "Jet OLEDB:Database Password=dgo17bru29"
    CNN.Open

    Set RST = New ADODB.Recordset
    RST.Open Source:="SELECT CODART, DESCRIZIONE, DESCRIZIONE2, DESCRIZIONE3, DESCRITTIVO, " & _
                     "CHIUSURA FROM ARTICOLI_NOTETECNICHE", ActiveConnection:=CNN, Options:=adCmdText

    R = 0

    Do While Not RST.EOF
        TXT = ""
        For I = 0 To RST.Fields.Count - 1
            FLD = RST(I)
            FLD = Replace(FLD, Chr(34), Chr(39))
            FLD = Replace(FLD, vbCr, " ")
            FLD = Replace(FLD, vbLf, " ")
            TXT = TXT & ";" & Chr(34) & FLD & Chr(34)
        Next I

        STRM.WriteText TXT

        RST.MoveNext

        Me.LAZIONI.Caption = R
        DoEvents
    Loop

    STRM.SaveToFile "C:\DIEGO_VB6\Text.csv"

    Me.LAZIONI.Caption = "FINE EXPORT!"
    DoEvents

    RST.Close
    Set RST = Nothing
    CNN.Close
    Set CNN = Nothing

    Me.Text1.SetFocus
End Sub
OK WORK.
but the code write the txt in one line...
not line by line

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Character set wrong

Post by HansV »

Change

Code: Select all

        STRM.WriteText TXT
to

Code: Select all

        STRM.WriteText TXT & vbCrLf
Best wishes,
Hans