How do I load a tab-delimited file into a ListView ultra-quick?

[TheAmplifier]

Does anyone know a way to load a tab-delimited text file into a ListView ultra-quick?

Carriage returns make up the end of the record, while tabs separate each field.

I've tried two different methods. One is loading the file, one line at a time, into a string then using the InStr command to find the tabs, split up the line, and add it to the ListView.

The other way I've tried to do this is to load the file as binary, then use the Split command to split up the records based on carriage returns, then use the Split command again on each record to split it into fields. And then add them to the ListView.

Both methods parse a 32000 record file in about 11 - 12 seconds. Is there a faster way to do this, or am I asking for too much?

Thanks!

use the filesystemobject (FSO) to open the file as a textstream, then use split to take care of carriage returns and then again for the tabs.
should be about 15% faster (alledgedly).

[TheAmplifier]

I tried using the FileSystemObject to OpenAsTextStream, but for some reason it is incredibly slow. It took 10 minutes to read in the same file that only took 12 seconds before. So, obviously I've done something majorly wrong.

Here's what I have so far:

Dim Filesystem, File, TextStream
Dim StringToParse As Variant
Set Filesystem = CreateObject("Scripting.FileSystemObject")
Set File = Filesystem.GetFile(ServerDir + "connect log.txt")
Set TextStream = File.OpenAsTextStream(1, 0)
StringToParse = TextStream.ReadAll
TextStream.Close

Should this be working, or is there another way I should be using the FSO?

Thanks!
Amp

[Serge]

Sure. For this example I exported a customers table from Northwind database to a tab delimeted file (Customers.txt).

Private Sub Command1_Click()
    Dim i As Integer
    Dim strBuffer As String
    Dim strPath As String
    Dim intFFN As Integer
    Dim arrRec As Variant
    Dim lstItem As ListItem
    
    strPath = "c:\my documents\customers.txt"
    intFFN = FreeFile
    
    'Initialize ListView
    With ListView1
        .ListItems.Clear
        .ColumnHeaders.Clear
    End With
    
    'open tab delimeted file
    Open strPath For Input As intFFN
    Do Until EOF(intFFN)
        Line Input #intFFN, strBuffer
        arrRec = Split(strBuffer, vbTab)
        With ListView1
            If Not IsEmpty(arrRec) Then
                For i = 0 To UBound(arrRec)
                    'Add column headers
                    If .ColumnHeaders.Count < i + 1 Then
                        .ColumnHeaders.Add , , "Column" & i + 1
                    End If
                    'If i=0 then it is a first column
                    'assign the value to the first column
                    If i = 0 Then
                        Set lstItem = .ListItems.Add(, , arrRec(i))
                    Else 'everything else is a subitem
                        lstItem.SubItems(i) = arrRec(i)
                    End If
                    Erase arrRec
                Next
            End If
        End With
    Loop
End Sub

If you have VB5 and earlier, then you can't use Split function. Here's a custom Split function that will do the same thing for you:

Public Function Split(pString As String, pDelimeter As String) As Variant
    Dim intPos As Integer
    Dim arrTemp()
    Dim i As Integer
    Dim strTemp As String
    
    strTemp = pString
    
    intPos = InStr(strTemp, pDelimeter)
    Do Until strTemp = ""
        ReDim Preserve arrTemp(i)
        If InStr(strTemp, pDelimeter) Then
            arrTemp(i) = Trim(Left(strTemp, InStr(strTemp, pDelimeter) - 1))
            strTemp = Mid(strTemp, InStr(strTemp, pDelimeter) + 1)
        Else
            arrTemp(i) = Trim(strTemp)
            strTemp = ""
        End If
        i = i + 1
    Loop
    Split = arrTemp
End Function

Use it the same way:
Array = Split(YourString, Delimeter)

Regards,

[TheAmplifier]

Well, that definately helped! It sped it up by at least 25%. I never thought of using the split command there instead of the InStr.

I did a little expirement and removed the "ListView1.ListItems.AddItem" lines from the code and ran the program. It was able to load the log in under 3 seconds. So, it seems adding the items to the ListView is what's bogging it down.

Someone once said there was an API call called LVM_InsertItemA that I could use to add items to the ListView.

My question is this: does anyone know how to implement this and if so, would it significantly speed up adding items? (I hope I get a "yes" to both. )

Thanks!
Amp

[Paul282]

I'm not really sure why but I always get a significant speed boost if I break the operation up.

What I mean is have the first function load al the data into a UDT (with the split function)

the set the list view to visibility off, loop the UDT into the list view using the "With" keyword, (display a bar graph updated every 100 records if there are a lot of records) then then show the list view again.

You tend to find the the list view flickers once and the data's loaded, very quick

[TheAmplifier]

Yes, I am using VB6, SP3.

I have tried doing it the way you explained, but it still gets bogged down when filling the ListView.

Thanks!
Amp

[Paul282]

how many records, how many columns

[TheAmplifier]

Right now it's at 32,000. But I've seen it range from 16,000 to 250,000 before. As for columns, it ranges like this:

One file has 4 columns, two files have 5 columns, and one file has 6 columns. I want each file to be displayed in it's own ListView.

Hope this helps!

Thanks!
Amp

[Paul282]

ouch,

I remember using listviews with DB's for about 10,000 - 15,000 records and things were getting slow to load. admittedly DB slows things down.

For speed start the with statement before the loop and close it after.

I'm afraid I don't know the memory structure so I don't know how to dump the data in with API. With in theory is possible if you can match the type.

Even so, it'll be slow with 250,000 records no matter what you do. Have you considered a filter scheme?

Only keep 5000 rec in at a time. load them out of your UDT array as you need them. have some sort of slide or scroll bar to swap out the 5000 record chunks to the list view.

I can't think of any language that handles 250k records in a list view well. Ever tried opening a directory in explorer with 10,000 records? slow

Sorry, You may have to use some sort of interface design idea to make it seem like you're loading all the records, after-all the client can only see one screen at a time (scrollable)!

[TheAmplifier]

Since the ListView is only a small portion of what the user can do with the data, I believe I can pull it off by creating some arrays of UDT's.

Loading into memory is ultra-fast, it's just adding them to the ListView that slows it down.

So, I'll try this. Load everything into arrays of UDT's and do all the functions the user can do with the data from this array in memory. Then, if the user wants, I can fill the ListView from memory.

That way I don't force them to see the ListView and the slow load times every single time they access the information.

I'll let you know how it goes! And thanks for the suggestions!

Thanks!
Amp

[Serge]

ListView is definetely slowing down the loading process. But you can use LockWindowUpdate API to prevent ListView from refreshing (and repainting)

Private Declare Function LockWindowUpdate Lib "user32" Alias "LockWindowUpdate" (ByVal hwndLock As Long) As Long


Private Sub Command1_Click()
    Dim i As Integer
    Dim strBuffer As String
    Dim strPath As String
    Dim intFFN As Integer
    Dim arrRec As Variant
    Dim lstItem As ListItem
    
    strPath = "c:\my documents\customers.txt"
    intFFN = FreeFile
    
    'Initialize ListView
    With ListView1
        .ListItems.Clear
        .ColumnHeaders.Clear
    End With
    
    'Lock any updates on the ListView
    LockWindowUpdate ListView1.hWnd

    'open tab delimeted file
    Open strPath For Input As intFFN
    Do Until EOF(intFFN)
        Line Input #intFFN, strBuffer
        arrRec = Split(strBuffer, vbTab)
        With ListView1
            If Not IsEmpty(arrRec) Then
                For i = 0 To UBound(arrRec)
                    'Add column headers
                    If .ColumnHeaders.Count < i + 1 Then
                        .ColumnHeaders.Add , , "Column" & i + 1
                    End If
                    'If i=0 then it is a first column
                    'assign the value to the first column
                    If i = 0 Then
                        Set lstItem = .ListItems.Add(, , arrRec(i))
                    Else 'everything else is a subitem
                        lstItem.SubItems(i) = arrRec(i)
                    End If
                    Erase arrRec
                Next
            End If
        End With
    Loop

    'Unlock ListView
    LockWindowUpdate 0
End Sub

This should increase the speed atleast by 25%.