Unusual reading a csv file takes 30 mins

Since the weekend my program when loading data from a csv file into a listbox now takes ages (30 mins). Until the weekend it took about 2 secs. I have not touched the code. Have uploaded latest Mac OS on both my Mac laptops, also have refreshed Xojo on one of them. Result is no difference - problem is still there.
Any suggestions please?

We’d need to see the code that parses the file and loads it into the Listbox.

If you’ve not changed the code, is the file on a network drive that could be experiencing issues with latency (since you say that you see this on multiple computers)?

Here tiz, unchanged from 6 months ago

Dim f As FolderItem
Dim textInput As TextInputStream
Dim rowFromFile, oneCell As String
Dim i As Integer
dim txt as String

#If TargetWindows Then
// Windows-specific code here
f = GetFolderItem(“c:\axojo\stock.csv”)
#ElseIf TargetMacOS
// Mac-specific code goes here
f = GetFolderItem(“stock.csv”)
#ElseIf TargetLinux Then
// Linux-specific code goes here
#ElseIf TargetXojoCloud Then
// Xojo Cloud-specific code goes here
#EndIf

If f <> Nil And f.Exists Then
Dim tab As String = “,”
textInput = TextInputStream.Open(f)
textInput.Encoding = Encodings.MacRoman // strings are MacRoman

// Set number of columns and their widths for Listbox1
//If ListBox1.ColumnCount < 5 Then
ListBox1.ColumnCount = 23
listbox1.columnwidths=“35,40,35,50,80,50,60,30,100,100,100,100,100,40,100,100,35,35,35,100,100,150,60”
listbox1.hasheader=true
//End If

While Not textInput.EOF
rowFromFile = textInput.ReadLine

For i = 1 To 23
  if NthField(rowFromFile, tab, 0)<>"Row" Then
    oneCell = NthField(rowFromFile, tab, i)
    ListBox1.Cell(ListBox1.ListCount - 1, i - 1) = oneCell
    if ListBox1.ListCount=1 then
      ListBox1.CellBold(ListBox1.ListCount - 1,i-1)=true
    end if
    Listbox1.CellAlignment(ListBox1.ListCount - 1,i-1) = ListBox.AlignCenter
    ListBox1.InvalidateCell(ListBox1.ListCount - 1,i-1)
    ListBox1.Cell(ListBox1.ListCount - 1, i - 1) = oneCell
  end if
Next
ListBox1.AddRow("")

Wend

textInput.Close

End If

for i=0 to listbox1.columnCount-1
txt=ListBox1.cell(0,i)
SortCol.AddRow(txt)
next i

No file always local. It is about 2500 lines

f = GetFolderItem(“stock.csv”)

A data file should not be in Applications folder
If your app is, you may be suffering from permissions issues.

Away from this there is much unnecessary code in here.
Set the alignment of the column not by individual cell, and remove this line:
Listbox1.CellAlignment(ListBox1.ListCount - 1,i-1) = ListBox.AlignCenter

You do not need this line:

Listbox1.CellAlignment(ListBox1.ListCount - 1,i-1) = ListBox.AlignCenter

This line is duplicated:
ListBox1.Cell(ListBox1.ListCount - 1, i - 1) = oneCell

This stuff only needs doing once (I assume it is to set the ‘header’ to be bold)

if ListBox1.ListCount=1 then ListBox1.CellBold(ListBox1.ListCount - 1,i-1)=true

Adding rows to a listbox goes faster if you disable the vertical scrollbars while you fill.

OK Jeff, thanks for that. CSV file is in a documents folder (as always in the past).
The code is not originally mine - I lifted it from other applications. I will try your suggestions - not sure that will make much difference since everything has worked well enough in the near past.

CSV file is in a documents folder

The code suggests it is ‘next to the app’

If your app is in Documents, it may have been translocated by OSX, which slows it down.
The CSV file may have been synched by iCloud.

The app should be in Applications,and the CSV file should be in
specialfolder.applicationdata.child(“com.yourapp”) or similar.

If it is the reading of the file that slows things down, read it all into memory in one go using ReadAll , and then parse the large string, instead of ReadLine

1 Like

I read a lot of .csv files, too. Memory is cheap and plentiful these days, so instead of reading them line by line, I do a ReadAll into a string (FileChunkStr, which is really just a big chunk of memory), then I do this:

FileChunkStr=FileChunkStr.ReplaceLineEndings(EndOfLine.Windows)

This normalizes the line endings, no matter where they came from. Next, I split FileChunkStr into an array:

Dim LineArray(-1) as string
LineArray=FileChunkStr.Split(EndofLine.Windows), which gives me a row in the array for each row in the file.

I’ve found that using .ReadAll and working with strings from the array is way faster than reading them one line at a time from the file.

Just my .02, of course :slight_smile:

1 Like

OK, so what’s the next bit of code to get the array into a listbox?

Just loop through LineArray(), it contains one row of data per listbox row. You can then assign content to each listbox row:

Dim LineStr as string
Dim RowStr as string

For Row as Integer=0 to ubound(LineArray)
LineStr=LineArray(Row)
Listbox1.AddRow(LineStr)
Next

1 Like

based on the original code:

'disable the scrollbars
listbox1.hasverticalscrollbar =false

'set the alignment
For i = 1 To 23
listbox1.columnalignment(i) = ListBox.AlignCenter
next

'do each row.. this should work because your separator is a comma
'even though the variable in the original code is called TAB

For Row as Integer=0 to ubound(LineArray)
'looks like you get Row 3, Row 4 etc above each data row and omit these?
if left(linearray(row),3) <> "Row" then   
Listbox1.AddRow(linearray(row))
end if
Next

listbox1.hasverticalscrollbar =true

Thank you, chaps, for your help. The magic clue was the possibility of iCloud interference. I had forgotten that I had only recently set it up to save documents. I have now removed that capability and rebuilt the csv file from original Windows XL source.
All 2500 lines now arrive in the listbox in about 2 secs. Phew!

This is still not very fast.

1 Like

Wasn’t a good trick to set the ListBox invisible at read time (before .Read…), then back to visible (after TextInputStream.Close) ?

https://documentation.xojo.com/api/deprecated/rectcontrol.html#rectcontrol-visible

App I am developing is for an antique Jewellery dealer, hence a current stock list of 2500 items. New items get entered 10 or more at a time, probably taking an hour or so. 2 secs is quite acceptable in those circumstances. I still have quite a lot more function to work on so speeding up the initial load is not high on my priorities.
Thanks to everyone for pitching in. Suggest this item should now be closed.