Importing csv file into Listbox

I am trying to import a csv file into a listbox. I have used the well tried code which is often seen on the web. It works well enough when you wish to use a specific column count. The problem I have is that I wish to import csv files which from case to case may have different numbers of columns. At the moment, I can successfully import the file but only for the number of columns arbitrarily set for the list box. I want to set the column count on a case by case basis according to the number of columns in the csv file. There must be a way of doing this in real time. Has anybody got any suggestions?

https://documentation.xojo.com/index.php/ListBox.ColumnCount

Thanks Jason but I am familiar with the ColumnCount property. What I want to do is change this during program operation based upon the column numbers in the csv file. To do this I need to read the number of columns in the csv file before importing it. So far I have not been able to figure this out.

Read the first line from the CSV file, count the columns, set the listbox ColumnCount, fill in the first row, then process the rest of the file.

Thanks Tim, that is exactly what I need to do. Unfortunately, I have no idea how to do that. Is there any website which might have some code examples?

CSV’s unfortunately CAN have a different number of columns per row :stuck_out_tongue:
Joy oh joy
I wrote a CSV parser years ago and it should still work just fine see http://www.great-white-software.com

I’ve used Norman’s CSV parser. It works great! However it sounds like we need to work on some fundamentals.

First, you need to get a FolderItem that refers to the file. You do that with GetFolderItem or SpecialFolder. Where does the file reside? Is it next to the app? Is in in the user’s documents? Some place else?

Example:

dim f as FolderItem
f = GetFolderItem("csvfile.txt")

Next, you need to open a TextInputStream on the file. You do that by passing the FolderItem to TextInputStream.Open().

dim txtin as TextInputStream
txtin = TextInputStream.Open(f)

Next, you need to read a line from the file. You use ReadLIne.

dim s as String
s = txtin.ReadLine

Do you have any questions about this first part? I don’t want to dive into parsing the string yet, and I’ll probably just recommend you use Norman’s parser rather than do it yourself (unless the csv is simple and predictable). But the above concepts are important to have under your belt for any kind of file processing. So let’s explore them if we need to.

Cant’ you just look at the first row and go from there? Then if there are too many fields on a line, add a column. To change the columnCount, you just set the value and the listbox will adjust itself.

Here’s a very basic example.

[code] dim i,count As integer
dim lines() As String=Values.Split(EndOfLine.OSX) //or whatever line ending you have

//If the file has a field label line we’ll set the headings…
Listbox1.ColumnCount=lines(0).CountFields(",")
for i=0 to Listbox1.ColumnCount-1
Listbox1.Heading(i)=lines(0).NthField(",",i+1)
next

count=lines.Ubound
for i=1 to count
// I think this was the original question…
if CountFields(lines(i),",")>Listbox1.ColumnCount then Listbox1.ColumnCount=CountFields(lines(i),",")
Listbox1.AddRow(lines(i).Split(","))
next[/code]

Thanks Norman and Tim. I was not anticipating one csv file having a different number of columns per row, I was concerned with one csv file having a different number of columns to the next csv file.

Tim, with regard to your first six lines of code, I am in exact agreement up to your last line of code, albeit with different variable names. All I need to do is read the number of columns in the csv file.

I have just received another post from Jim and that looks promising, however, when I try and run that code it hangs up on the line.

dim lines() As String=Values.Split(EndOfLine.OSX)

The error message says “This item does not exist” and the word “value” is highlighted. I am also not sure what the extension OSX means with regard to my problem.

Once again, thank you for your help to date. If you can help any further it would be greatly appreciated.

[quote=62333:@Peter Scott]dim lines() As String=Values.Split(EndOfLine.OSX)
[/quote]
This line is splitting a constant I was using called Values into an array of strings…
I would say use Tim’s code to read from the file, then continue with the example code I gave.

Try this…

dim f as FolderItem
f = GetFolderItem("csvfile.txt")

dim lines() As String
dim txtin as TextInputStream
txtin = TextInputStream.Open(f)

while not txtin.EOF
  lines.append( txtin.ReadLine )
wend
txtin.close

dim i,count As integer
  
  //If the file has a field label line we'll set the headings..
  Listbox1.ColumnCount=lines(0).CountFields(",")
  for i=0 to Listbox1.ColumnCount-1
    Listbox1.Heading(i)=lines(0).NthField(",",i+1)
  next
  
  count=lines.Ubound
  for i=1 to count
    // I think this was the original question..
    if CountFields(lines(i),",")>Listbox1.ColumnCount then Listbox1.ColumnCount=CountFields(lines(i),",")
    Listbox1.AddRow(lines(i).Split(","))
  next

hmm… this is a variation on the example from the docs… look at this TextInputStream

Just be aware that the example in the docs is using a tab-delimited file. Tabs are much less likely (not at all?) to be embedded in the data than a comma, which makes them easier to deal with. A comma delimited file is not so straight-forward. You have to worry about quotation marks and embedded commas, so in general, you can’t really just use CountFields or Split. You can with a tab delimited file, but not a comma delimited file.

If this is a comma-delim file, I would highly recommend using Norman’s class.

Tim, others:
read csv @ wikipedia and use \tabs as delimiter, so you will not fall into troubles like quotes, comma in the text, and so on.

I stated in a similar thread that I have a list of Elvis Presley songs, forgot about the commas in song titles and try some lister’s project to fall into the comma trap:

“Today, Tomorrow and forever” or “I want You, I need You, I Love You” were displayed in many rows even if they are one song title using the comma as delimiter. On the other hand, when I import the file using Tabs as delimiters, it was doing fine.

I am lucky because a late friend, 20 years ago was always talking about “Tab Tab Return” and this stayed in my brain 'till I needed it. Thank you Antoine.
I do not recall having used comma delimited fields.

Jim, thanks for the code. It works.

Tim, thanks for the advice regarding tab and comma delimited files. I guess one option could be to write alternative lines of code depending upon the type of file being opened.

Sorry for my newbieness, When you say Tabs instead comma, did you mean this: “/”

Eg. My Code:

Dim csvType As New FileType
csvType.Name = “CSV File (*.csv)”
csvType.MacType = "TEXT "
csvType.Extensions = “csv”

dim f as FolderItem
dim s as string

dim dlg as new OpenDialog
dlg.Filter = csvType
f = dlg.ShowModal

if f <> nil and f.Exists then
dim tis as TextInputStream = TextInputStream.Open(f)
ListBox1.DeleteAllRows
while not tis.EOF
s = tis.ReadLine(encodings.UTF8)
ListBox1.AddRow “”
ListBox1.Cell(ListBox1.LastIndex,-1) = s.ReplaceAll(“,”, chr(9))
wend
ListBox1.HasHeading = true
ListBox1.Heading(-1) = ListBox1.Cell(0, -1)
ListBox1.RemoveRow(0)
else
return
end if

Must I replace “,” with “/”?

Regards

Are you the one creating these files using Excel? If so save them as Tab Delimited instead of CSV. That way each field is separated by a tab character Chr(9) instead of a comma. That way, if there is a comma in one or more of the fields it will not cause a problem when separating the fields. It is much more unlikely that your data will contain tab characters within the Excel fields. Now in your XOJO program split the line in to fields base on CHR(9) instead of a comma. Keep in mind that still, any field that contains a comma will be enclosed in " characters even when choosing Tab Delimited format.

If you are writing this for general use then you are going to have to write more code to properly separate the fields. Take a look at the code Norman Palardy referenced early on in this thread.

Tab delimited columns can contain tabs (although its a trick to get excel to do it but it will)
Such fun :slight_smile:

Gerardo:

Harrie gaves you the answer.

Tab: Chr(9) … [the —>| key on the keyboard’s left]
Comma: ‘,’ [without simple quotes]

/ = slash

LBN: you’d better use ReplaceLineEndings . It will avoid to your code to display discrepencies…

Excepted if you are only opening files you created and you know that it will never happens, not using ReplaceLineEndings can leads to reading many lines at a time.

You have to know that nowadays there are more than one “return” character around there.

About the use of Excell (and Crimosoft software in general):

It is a nightmare for a newbie (’, ", tab,using it as a data base, etc.)

BUT: it allows the user to be more respectfull to computer things and in the end, if this person start to develop software, that kind of experience can be very useful.

Note: excepted Internet Explorer recently, more that 20 years ellapsed since I used Excell or Word (or Microsoft Office). Qui boir Vabe va bien. So, I can be partial in presnting things. Vive la publicit. I recall Word without the Select All MenuItem, so go figure.

Advertisings have been generously provided (for those who do not use Windows 8, yet: be prepared to get them).