looping array

[sal21]

Code: Select all

Private Sub Command1_Click()
    
    With New ADODB.Stream
        .Type = adTypeText
        .Charset = "utf-8"
        .Open
        .LoadFromFile "C:\Lavori_Vb6\LEGGI_CSV_COMUNI\FILES\COMUNI_CF_SIC.csv" '(substitute the location and name of your .csv file)
        Debug.Print .ReadText(adReadAll)
        .Close
    End With
    
End Sub

how to loop this array:

.ReadText(adReadAll)


============================

for test i have used this code but googling suggest to use adostream

Code: Select all

Sub ReadTextFile()

  Dim strLine As String
  Dim FSO As Object
  Dim TSO As Object
  Set FSO = CreateObject("Scripting.FileSystemObject")
  Set TSO = FSO.OpenTextFile("C:\Lavori_Vb6\LEGGI_CSV_COMUNI\FILES\COMUNI_CF_SIC.csv")
  
  Do While Not TSO.AtEndOfStream
     strLine = TSO.ReadLine
     Debug.Print strLine
  Loop
  
  TSO.Close
  Set TSO = Nothing
  Set FSO = Nothing
  
End Sub

because the code in the second line return:

001001,A074,Agliè

instead:

001001,A074,Agliè

[HansV]

Code: Select all

Private Sub Command1_Click()
    Dim sText As String
    Dim aLines() As String
    Dim i As Long

    With New ADODB.Stream
        .Type = adTypeText
        .Charset = "utf-8"
        .Open
        .LoadFromFile "C:\Lavori_Vb6\LEGGI_CSV_COMUNI\FILES\COMUNI_CF_SIC.csv" '(substitute the location and name of your .csv file)
        sText = .ReadText(adReadAll)
        .Close
    End With

    aLines = Split(sText, vbLf)
    For i = 0 To UBound(aLines)
        Debug.Print aLines(i)
    Next i
End Sub

[SpeakEasy]

Ok, so the reason you have been advised to use the ADODB stream is that the fiilesystemobject does not support UTF-8, which the CSV is using.

Having got that out of the way, it is also worth observing that the 'text file' opened by the FSO is in fact a stream, and that the ADODB stream therefore has similar methods, albeit invoked slightly differently. So we can rewrite your ADODB solution to look a little more like your FSO solution, looping through the lines rather than loading them all in one chunk.

Code: Select all

Private Sub Command1_Click()
    Dim strLine As String
    
    With New ADODB.Stream
        .Type = adTypeText
        .Charset = "utf-8"
        .Open
        .LoadFromFile "D:\DOWNLOADS\DELETEME\COMUNI_CF_SIC.csv" '(substitute the location and name of your .csv file)
        Do While Not .EOS
            strLine = .ReadText(adReadLine)
            Debug.Print strLine
        Loop
        .Close
    End With
    
End Sub

[sal21]

Ok, so the reason you have been advised to use the ADODB stream is that the fiilesystemobject does not support UTF-8, which the CSV is using.

Having got that out of the way, it is also worth observing that the 'text file' opened by the FSO is in fact a stream, and that the ADODB stream therefore has similar methods, albeit invoked slightly differently. So we can rewrite your ADODB solution to look a little more like your FSO solution, looping through the lines rather than loading them all in one chunk.

Code: Select all

Private Sub Command1_Click()
    Dim strLine As String
    
    With New ADODB.Stream
        .Type = adTypeText
        .Charset = "utf-8"
        .Open
        .LoadFromFile "D:\DOWNLOADS\DELETEME\COMUNI_CF_SIC.csv" '(substitute the location and name of your .csv file)
        Do While Not .EOS
            strLine = .ReadText(adReadLine)
            Debug.Print strLine
        Loop
        .Close
    End With
    
End Sub

tks for code.
work!

[sal21]

Code: Select all

Private Sub Command1_Click()
    Dim sText As String
    Dim aLines() As String
    Dim i As Long

    With New ADODB.Stream
        .Type = adTypeText
        .Charset = "utf-8"
        .Open
        .LoadFromFile "C:\Lavori_Vb6\LEGGI_CSV_COMUNI\FILES\COMUNI_CF_SIC.csv" '(substitute the location and name of your .csv file)
        sText = .ReadText(adReadAll)
        .Close
    End With

    aLines = Split(sText, vbLf)
    For i = 0 To UBound(aLines)
        Debug.Print aLines(i)
    Next i
End Sub

work perfect.

[sal21]

Code: Select all

Private Sub Command1_Click()
    Dim sText As String
    Dim aLines() As String
    Dim i As Long

    With New ADODB.Stream
        .Type = adTypeText
        .Charset = "utf-8"
        .Open
        .LoadFromFile "C:\Lavori_Vb6\LEGGI_CSV_COMUNI\FILES\COMUNI_CF_SIC.csv" '(substitute the location and name of your .csv file)
        sText = .ReadText(adReadAll)
        .Close
    End With

    aLines = Split(sText, vbLf)
    For i = 0 To UBound(aLines)
        Debug.Print aLines(i)
    Next i
End Sub

Is this correct to assign to a var the related splitted item from each lines? or exists other way?

...
For I = 1 To UBound(aLines)
ISTAT = Split(aLines(I), ",")(0)
CF = Split(aLines(I), ",")(1)
COMUNE = UCase(Split(aLines(I), ",")(2))
Next I
...

[HansV]

Instead of splitting aLines(i) 3 times, I'd create a variable:

Code: Select all

Private Sub Command1_Click()
    Dim sText As String
    Dim aLines() As String
    Dim i As Long
    Dim aParts() As String
    Dim ISTAT As String
    Dim CF As String
    Dim COMUNE As String

    With New ADODB.Stream
        .Type = adTypeText
        .Charset = "utf-8"
        .Open
        .LoadFromFile "C:\Lavori_Vb6\LEGGI_CSV_COMUNI\FILES\COMUNI_CF_SIC.csv"
        sText = .ReadText(adReadAll)
        .Close
    End With

    aLines = Split(sText, vbLf)
    For i = 0 To UBound(aLines)
        aParts = Split(aLines(i), ",")
        ISTAT = aParts(0)
        CF = aParts(1)
        COMUNE = aParts(2)
    Next i
End Sub

[sal21]

Code: Select all

Private Sub Command1_Click()
    Dim sText As String
    Dim aLines() As String
    Dim i As Long

    With New ADODB.Stream
        .Type = adTypeText
        .Charset = "utf-8"
        .Open
        .LoadFromFile "C:\Lavori_Vb6\LEGGI_CSV_COMUNI\FILES\COMUNI_CF_SIC.csv" '(substitute the location and name of your .csv file)
        sText = .ReadText(adReadAll)
        .Close
    End With

    aLines = Split(sText, vbLf)
    For i = 0 To UBound(aLines)
        Debug.Print aLines(i)
    Next i
End Sub

hi bro...
wath you think about this code to append value from txt to Access table?
the lines are approx 8.000, and i skip the first line with

For I = 1 To UBound(ALINES) - 1 (not important the first line)

but i need a fasted way during the loop, this is my goal!

...
Public Sub IMPORT_CF_SIC()

Dim I As Long

SQL = "DELETE * FROM COMUNI_CF"
CNN.Execute SQL, , adExecuteNoRecords

CNN.BeginTrans
For I = 1 To UBound(ALINES) - 1
DoEvents
ISTAT = Split(ALINES(I), ",")(0)
CF = Split(ALINES(I), ",")(1)
COMUNE = UCase(Split(ALINES(I), ",")(2))
SQL = "INSERT INTO COMUNI_CF (ISTAT, CF, COMUNE) VALUES ('" & ISTAT & "', '" & CF & "', '" & Replace(COMUNE, "'", "") & "')"
CNN.Execute SQL, , adExecuteNoRecords
Next I
CNN.CommitTrans

End Sub
.....

[SpeakEasy]

>this is my goal!

Is your goal simply to replace the existing COMUNI_CF table with the contents of the CSV?

[sal21]

>this is my goal!

Is your goal simply to replace the existing COMUNI_CF table with the contents of the CSV?

Yes!