[RESOLVED] read from text file and write in list view

[kayatri]

Hi i have text file like this
Date Test,Time Test,Test Time,Varient,SN,Model,Both Hand - DMM,STATUS
5/14/2010,11:05:00 PM,35,20367,SAB34N10051009252,X134,21.000,PASS,

I want write this to the list view. This is my code so far

Private Sub ReadFile()

Set fso = New FileSystemObject
sFile = data_name2 & name1 & ".txt"
       
Set oTSRead = fso.OpenTextFile(sFile, ForReading, False)


If oTSRead.Line > 0 Then

    Do While Not oTSRead.AtEndOfStream
        'Get the next line in our file
        sLine = oTSRead.ReadLine
        strField() = Split(sLine, ",")
        
        If strField(0) = "Date" Then GoTo skip1:

If Left((cboSpec.Text), 4) = "X134" Then
                
                lv5.ListItems.Add 1, , ""
               lv5.ListItems(1).Text = strField(0)
                 
                For q = 1 To 33
                    lv5.ListItems(1).SubItems(q) = strField(q)                Next
                
                
                
            End If
        
        counter = counter + 1
skip1:
    Loop
    
End If
   
oTSRead.Close
lblno.Caption = counter
txtSN.SetFocus

End Sub

but i got invalid property value error at line that i highlighted. Can any one help me to solve this problem?

[baja_yu]

The "Next" is supposed to be in a new line.

Then, here: lv5.ListItems(1).SubItems(q) = strField(q)

ListItems(1)? It would mean that all the data would be added to the first item in the list. Should it maybe be (counter)

Also, you only added the first column, add the subitems with (this also resolves the upper issue by adding to the last item)

lv5.ListItems.Item(lv5.ListItems.Count).ListSubItems.Add, , strField(q)

[kayatri]

Thanx for the reply
now i change the code like this

If Left((cboSpec.Text), 4) = "X134" Then
                
                              For q = 1 To 33
                lv5.ListItems.item(lv5.ListItems.Count).ListSubItems.Add , , strField(q)

               Next

but now i get index outside of bound error

this is how i read data from database to list view and write to text file

If Left((cboSpec.Text), 4) = "X134" Then
        sql2 = "select Test_Type from tb_TestResult Group By Test_Type "
        sql = "select distinct * from tb_TestResult where serial_no = '" & Trim$(txtSN.Text) & "'"
    End If
     
        
    Set rs = New ADODB.Recordset
    rs.Open sql, adbcon, adOpenKeyset, adLockOptimistic
     Set rs2 = New ADODB.Recordset
    rs2.Open sql2, adbcon, adOpenKeyset, adLockOptimistic

If Left((cboSpec.Text), 4) = "X134" Then
    

        With lv5

        .ListItems.Add 1, Trim$(rs!serial_no)
        .ListItems(1).Text = Trim$(rs!date_test)
        .ListItems(1).SubItems(1) = Right(Trim$(rs!time_test), 11)
        .ListItems(1).SubItems(2) = Trim$(rs!test_time)
        .ListItems(1).SubItems(3) = Trim$(rs!varient)
        .ListItems(1).SubItems(4) = Trim$(rs!serial_no)
        .ListItems(1).SubItems(5) = Trim$(rs!model)
    
       rs.MoveFirst
in_data = Trim$(rs!date_test) & "," & Right(Trim$(rs!time_test), 11) & "," & Trim$(rs!test_time) & "," & Trim$(rs!varient) & "," & Trim$(rs!serial_no) & "," & Trim$(rs!model)
 Do While rs.EOF = False
       
            For j = 1 To lv5.ColumnHeaders.Count
                If Trim(lv5.ColumnHeaders(j).Text) = Trim(rs!Test_Type) Then

                    .ListItems(1).SubItems(j - 1) = Trim$(rs!Status)
                    
                   in_data = in_data & "," & Trim$(rs!Status)
                    Exit For
                End If
            Next j
            rs.MoveNext
        Loop
   Set fso = New FileSystemObject


sFile = data_name2 & name1 & ".txt"

Set oTSRead = fso.OpenTextFile(sFile, ForAppending, False)
oTSRead.WriteLine in_data
oTSRead.Close
      End With

Private Sub CreateFile()    'This function will create file if file is not present

Call CreateFolder

Set fso = New FileSystemObject

name1 = Trim$(cboSpec.Text) + (txtDate.Text) + (txtEmp.Text) & "-" & (txtRun.Text)
name2 = Trim$(cboSpec.Text) + (txtDate.Text) + (txtEmp.Text) & "-" & (txtRun.Text) & "-OQA"

If fso.FileExists(data_name2 & name1 & ".txt") = False Then
    temp = 0
    fso.CreateTextFile (data_name2 & name1 & ".txt")
    
    Open (data_name2 & name1 & ".txt") For Output As #2


If Left((cboSpec.Text), 4) = "X134" Then
    
        sql2 = "select Test_Type from tb_TestResult Group By Test_Type "
    
        Set rs = New ADODB.Recordset
        rs.Open sql2, adbcon, adOpenKeyset, adLockOptimistic
    
        If rs.RecordCount > 0 Then
    
            With lv5
     
                Set colHeader = lv5.ColumnHeaders.Add(, , "Date Test")
                Set colHeader = lv5.ColumnHeaders.Add(, , "Time Test")
                Set colHeader = lv5.ColumnHeaders.Add(, , "Test Time")
                Set colHeader = lv5.ColumnHeaders.Add(, , "Varient")
                Set colHeader = lv5.ColumnHeaders.Add(, , "SN")
                Set colHeader = lv5.ColumnHeaders.Add(, , "Model")
      
                rs.MoveFirst

                Do While rs.EOF = False
                    lv5.ColumnHeaders.Add , , rs.Fields(0).Value
                                     rs.MoveNext
                Loop
                Print #2, ""
                For j = 1 To lv5.ColumnHeaders.Count
                    If j < lv5.ColumnHeaders.Count Then
                        Print #2, lv5.ColumnHeaders(j).Text & ",";
                    Else
                        Print #2, lv5.ColumnHeaders(j).Text
                    End If
                Next j
                    
        End With
     
    End If
End If
Close #2
Else
    temp = 1
    Call ReadFile
End If

So from text file i want to write back to list view.
How to solve this problem

[baja_yu]

How many columns do you have added to your listview? This line: For q = 1 To 33
would require you to have 34 columns.

[kayatri]

i have 35 columns.

but can we do this by ignoring the number of column and just write back to the listview accoring to column header?

[Siddharth Rout]

Try this Kayatri... I have created a function for you so that you can reuse it for any other file as well...

You don't need to create column headers at Designtime. It will automatically create Column Headers for you

Also I have commented the code for you. If you still have a problem understanding then feel free to ask

Private Sub Command1_Click()
    '~~> Replace this with your file name
    LoadTextFile lv5, "C:\MyFile.Txt"
End Sub

Public Function LoadTextFile(Lw As ListView, Fname As String)
    Dim FileId As Integer, LVI As ListItem, MyArray() As String
    Dim colX As ColumnHeader, itmX As ListItem
    Dim Buffer As String, i As Integer
    
    Lw.View = lvwReport
    
    i = 0
    j = 1
    
    FileId = FreeFile
    
    Open Fname For Input As #FileId
    
    Do While Not EOF(FileId)
        i = i + 1
        '~~> Check for 1st line to decide on how many Col Headers
        '~~> are required
        If i = 1 Then
            Line Input #FileId, Buffer
            MyArray = Split(Buffer, ",")
            '~~> Create Column Headers
            For i = LBound(MyArray) To UBound(MyArray)
                Set colX = Lw.ColumnHeaders.Add(, , MyArray(i))
            Next i
        Else '<~~ Creating Listitems/Listsubitems
            Line Input #FileId, Buffer
            MyArray = Split(Buffer, ",")
            
            For i = LBound(MyArray) To UBound(MyArray)
                If i = 0 Then '<~~ Creating Listitems
                    Set itmX = Lw.ListItems.Add(, , MyArray(i))
                Else '<~~ Creating Listsubitems
                    Lw.ListItems(j).ListSubItems.Add , , MyArray(i)
                End If
            Next i
            j = j + 1
        End If
    Loop
    Close #FileId
End Function

Hope this helps...

[kayatri]

Thanks for the reply koolsid

When i follow ur code i got error like this

Private Sub Command1_Click()

LoadLW List1, "C:\FQA\DataFile\X134\X134N22S001-001.txt"End Sub

the line that i highlighted give me error 'sub or fuction not defined'

I try to solve it but can not?

[Edgemeal]

Thanks for the reply koolsid

When i follow ur code i got error like this

Private Sub Command1_Click()

LoadLW List1, "C:\FQA\DataFile\X134\X134N22S001-001.txt"End Sub

the line that i highlighted give me error 'sub or fuction not defined'

I try to solve it but can not?

I'd guess koolsid just made a typo, so look at the function name, its called "LoadTextFile" and has two parameters, the first is the name of the ListvVew you want to use and the second is the filename, so you need to correct your call, maybe something like....

LoadTextFile LV5, "C:\FQA\DataFile\X134\X134N22S001-001.txt"

[Siddharth Rout]

@Kayatri: Edge is right. I made a type and I have amended it
@Edge: Thanks Mate.

[kayatri]

Thanks koolsid, Thanks edgemeal now my problem solve ready