FROM ARRAY TO RECORDSET

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

FROM ARRAY TO RECORDSET

Post by sal21 »

Code: Select all

Sub READ_LINES()

    Dim FSO As New FileSystemObject
    Dim LineValues() As String
    Dim ReadLine As String
    Dim TS As TextStream

    Set TS = FSO.OpenTextFile(STRFILECSV & NOME_FILE)

    ReadLine = TS.ReadLine

    Do Until TS.AtEndOfStream

        ReadLine = TS.ReadLine

        LineValues = Split(ReadLine, ",")

        For K = LBound(LineValues) To UBound(LineValues)
            Debug.Print LineValues(K)
        Next

    Loop

End Sub

Now i need to insert into the field of myTable with the same order of soplitted value into LineValues(K)

for example:

k=0
valore=LineValues(K)
rs.fields(k).value=valore
...
k=7
valore=LineValues(K)
rs.fields(k).value=valore

ecc...

i hope I was clear :evilgrin:

note:
i'm on access and ado

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

Re: FROM ARRAY TO RECORDSET

Post by HansV »

Your code skips the first line of the text file. Is that intentional? (For example because the first line contains headers/field names?)
Regards,
Hans

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

Re: FROM ARRAY TO RECORDSET

Post by sal21 »

HansV wrote:
29 Oct 2022, 19:16
Your code skips the first line of the text file. Is that intentional? (For example because the first line contains headers/field names?)
yes! exsctlly
you navigate in my mind!

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

Re: FROM ARRAY TO RECORDSET

Post by HansV »

Perhaps:

Code: Select all

Sub READ_LINES()

    Dim FSO As New FileSystemObject
    Dim LineValues() As String
    Dim ReadLine As String
    Dim TS As TextStream
    Dim CNN As New ADODB.Connection
    Dim RS As New ADODB.Recordset

    CNN.Open ConnectionString:="..."
    RS.Open Source:="...", ActiveConnection:=CNN, Options:=adCmdTableDirect
    Set TS = FSO.OpenTextFile(STRFILECSV & NOME_FILE)

    ReadLine = TS.ReadLine

    Do Until TS.AtEndOfStream
        ReadLine = TS.ReadLine
        LineValues = Split(ReadLine, ",")
        RS.AddNew
        For K = LBound(LineValues) To UBound(LineValues)
            RS.Fields(K) = LineValues(K)
        Next
        RS.Update
    Loop

    RST.Close
    CNN.Close

End Sub
Regards,
Hans

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

Re: FROM ARRAY TO RECORDSET

Post by sal21 »

HansV wrote:
29 Oct 2022, 19:45
Perhaps:

Code: Select all

Sub READ_LINES()

    Dim FSO As New FileSystemObject
    Dim LineValues() As String
    Dim ReadLine As String
    Dim TS As TextStream
    Dim CNN As New ADODB.Connection
    Dim RS As New ADODB.Recordset

    CNN.Open ConnectionString:="..."
    RS.Open Source:="...", ActiveConnection:=CNN, Options:=adCmdTableDirect
    Set TS = FSO.OpenTextFile(STRFILECSV & NOME_FILE)

    ReadLine = TS.ReadLine

    Do Until TS.AtEndOfStream
        ReadLine = TS.ReadLine
        LineValues = Split(ReadLine, ",")
        RS.AddNew
        For K = LBound(LineValues) To UBound(LineValues)
            RS.Fields(K) = LineValues(K)
        Next
        RS.Update
    Loop

    RST.Close
    CNN.Close

End Sub
tks!
but i cannot test now.

User avatar
SpeakEasy
3StarLounger
Posts: 319
Joined: 27 Jun 2021, 10:46

Re: FROM ARRAY TO RECORDSET

Post by SpeakEasy »

Is this related to your attempts to deal with the JSON returned from comuni.openapi.it?

If so, whilst VB itself doesn't offer a reasonable native JSON parser, and tricks with vbscript can only directly handle certain subsets of JSON, there might be better methods than writing the response string out to a file and then trying to read it back in.

Perhaps if you explain exactly what you are trying to achieve (rather than starting with a solution that may not be optimal)