Concatenate string with ";"

[luca90]

I looping recordset....
I need to create a string deleimited from ";" for each row in table, but the code dont work correct!

NEWSQL1 = ""
NEWSQL = ""
I = 2
Do While Not RST.EOF = True
For J = 0 To RST.Fields.Count - 1
NEWSQL = Trim(RST.Fields(J).Value)
NEWSQL1 = NEWSQL & ";"
Debug.Print NEWSQL1
Next J
Print #1, NEWSQL
RST.MoveNext
I = I + 1
Loop

Note:
In other case is required the last ";" if i want to import in access datatable the txt file?

[dilettante]

No, the last field should not have a trailing ";" after it and before the newline.

Did you MoveFirst before this loop?

You can use the Recordset's GetString() method to do this, one line at a time or for the entire Recordset.

[luca90]

No, the last field should not have a trailing ";" after it and before the newline.

Did you MoveFirst before this loop?

You can use the Recordset's GetString() method to do this, one line at a time or for the entire Recordset.

Example; possible about getstring()?

[dilettante]

Example; possible about getstring()?

Seriously? This is pretty clear in the documentation.

Private Function DumpRecordset() As String
    With rsData
        .MoveFirst
        DumpRecordset = .GetString(adClipString, , ";", vbNewLine, "*null*")
    End With
End Function

[luca90]

Seriously? This is pretty clear in the documentation.

Private Function DumpRecordset() As String
    With rsData
        .MoveFirst
        DumpRecordset = .GetString(adClipString, , ";", vbNewLine, "*null*")
    End With
End Function

good code ... but i work arround a few records.
Really have 3/4 millions of records and the code go in error "not enauhght memory"

[VeenaMG]

Hi,

You need to Clear NewSQL1 for each record...
Try This :

Dim FN As Integer
FN = FreeFile
Open "C:\Temp1.txt" For Output As FN
Do While Not RST.EOF = True
   NewSQL1 = ""
   For J = 0 To RST.Fields.Count - 1
      NEWSQL1 = NEWSQL1 & ";" & Trim(RST.Fields(J).Value)
   Next J
   Print #FN, NEWSQL1
   RST.MoveNext
Loop
Close FN

Regards
Veena

[luca90]

Hi,

You need to Clear NewSQL1 for each record...
Try This :

Dim FN As Integer
FN = FreeFile
Open "C:\Temp1.txt" For Output As FN
Do While Not RST.EOF = True
   NewSQL1 = ""
   For J = 0 To RST.Fields.Count - 1
      NEWSQL1 = NEWSQL1 & ";" & Trim(RST.Fields(J).Value)
   Next J
   Print #FN, NEWSQL1
   RST.MoveNext
Loop
Close FN

Regards
Veena

hI friend, sorry for delay... you tips work fine! Tks.
But for you, have sense instead to use only the ; to devide the records in txt file, or i need to use ";", similar:

this:
;eeeeee;sdfdsfsdfsdf;dsfdsf;.........

or:
"eeeeee";"sdfdsfsdfsdf";"dsfdsf";.........

[dilettante]

good code ... but i work arround a few records.
Really have 3/4 millions of records and the code go in error "not enauhght memory"

If you just look at the documentation you'll find that GetString() has an optional parameter NumRows telling it how many records to "get."

You can use a loop "getting" 1000 or 10000 records at a time.