How to Get Headers on a Listbox from an Array

General
1

Hi all!

I’ have this code, for get data of a CSV file, load the info and display it on a Listbox:
Dim f As FolderItem
dim tis as TextInputStream
dim s as string
dim i as integer
dim fields() as string

'show standard file selector
f = GetOpenFolderItem(“any” )
if f=nil then exit sub 'cancel clicked

'open the file
tis = f.OpenAsTextFile
if tis=nil then 'failed?
MsgBox(“The file could not be opened.”)
exit sub
end if

'read file into grid
while not tis.EOF 'while not end-of-file
ListBox1.AddRow “” 'add row to grid
s=tis.ReadLine 'read line from file
fields=Split(s,",") 'put items in fileds() array
for i=0 to ListBox1.ColumnCount-1 'copy to grid

  ListBox1.HasHeading=True
       
  'Start from row 2 to apart the number one to be the header.
  ListBox1.Cell(ListBox1.ListCount-2,i)=Trim(fields(i))
next

wend

tis.Close 'close file

So, I started to count from -2 instead of -1 to discard the row number 1, because I wanna use like the Header.
Now the question is:
How can I get the data of the Headers?.

Regards

2

Don’t use ListCount that way. User Listbox1.LastIndex for the row number.

What do you want to do with the headers? Load them from somewhere? Put them in Listbox1.Heading(i)? Put them in the first row of the listbox? It isn’t clear from your description.

3 
  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
4

[quote=189309:@Tim Hare]Don’t use ListCount that way. User Listbox1.LastIndex for the row number.

What do you want to do with the headers? Load them from somewhere? Put them in Listbox1.Heading(i)? Put them in the first row of the listbox? It isn’t clear from your description.[/quote]

[quote=189309:@Tim Hare]Don’t use ListCount that way. User Listbox1.LastIndex for the row number.

What do you want to do with the headers? Load them from somewhere? Put them in Listbox1.Heading(i)? Put them in the first row of the listbox? It isn’t clear from your description.[/quote]
i Wanna use the first row of the CSV file to get the name of the headers- listbox1.heading

5

[quote=189315:@Axel Schneider][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
[/code][/quote]
I’m gonna test it, thanks in advance

6

[quote=189315:@Axel Schneider][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
[/code][/quote]

Yeah, It works, Thanks!!!.

Now what happens if I have a value with a comma in my CSV.
For example, I have this Value: United Enterprises, Co. Ltd.

If you view this inside the CSV files Excel, you can appreciate that this values with comma has inside of “”.

Eg. “United Enterprises, Co. Ltd”

Because when I load this kind of registries, it splits me: United Enterprises in one column, and Co. Ltd in another column.

Regards

7

If I replace: “,” with chr(34) in this mode:

ListBox1.Cell(ListBox1.LastIndex,-1) = s.ReplaceAll(chr(34), chr(9))

Its get’s the value inside of the commas but how can I get the values with both parameters, comma and the Chr34?

8

As soon as a csv parser assumes data will NOT contain commas (or many other items like returns lines feeds etc) you’re in trouble
The csv parser I mentioned a long time ago in this thread when you asked about importing data into a listbox already handles all this
The sample application reads the csv file and, since it also handles rows with varying numbers of columns, prints the data surrounded by [ and ]
But it’s all split up correctly

At least go get the sample and see if it handles this correctly for you

9

[quote=189710:@Norman Palardy]As soon as a csv parser assumes data will NOT contain commas (or many other items like returns lines feeds etc) you’re in trouble
The csv parser I mentioned a long time ago in this thread when you asked about importing data into a listbox already handles all this
The sample application reads the csv file and, since it also handles rows with varying numbers of columns, prints the data surrounded by [ and ]
But it’s all split up correctly

At least go get the sample and see if it handles this correctly for you[/quote]
Thanks Norman.

I Use your Norman’s CSV Parser.

And I adapted the Code, to get columns and rows in the Listbox.
I get the data of the first row to get the name of the Headers of the Table.
And then delete the first row of the list.

My code is like this:

  dim f as FolderItem
  dim i as integer
  dim s as string
  dim c as CSVRecordSet
  dim dbf as CSVField
  
  ListBox1.DeleteAllRows
  
  f = GetOpenFolderItem("special/any")
  
  if f is nil then return
  
  c = new CSVRecordSet(f)
  
  while c.EOF <> true
    s = ""
    
    for i = 1 to c.FieldCount
      dbf = c.idxField(i)
      s = s + "" + ReplaceLineEndings(dbf.StringValue,"<CR>") + "]"
      
    next
    
    
    listbox1.addrow
    'listbox1.addrow s
    listbox1.cell(listbox1.LastIndex,-1) = s.ReplaceAll("]" ,chr(9))
    
    
    
    c.MoveNext
    
  wend
  ListBox1.HasHeading = true
  ListBox1.Heading(-1) = ListBox1.Cell(0, -1)
  ListBox1.RemoveRow(0)
  
  
  c.Close

Now I have Rows and Columns with the data extracted of the CSV File, But I get one extra Column, And I don’t Need It.
Am i Wrong? or what do you suggest.

Because once get it properly loaded the data on the listbox I wanna import it on my mySQL database.

Regards

10

Can you send me your csv file ?
Or a small sample ?

11

[code]dim f as FolderItem
dim i as integer
dim s as string
dim c as CSVRecordSet
dim dbf as CSVField

ListBox1.DeleteAllRows

f = GetOpenFolderItem(“special/any”)

if f is nil then return

c = new CSVRecordSet(f)

listbox1.HasHeading = true
dim headerSet as boolean

while c.EOF <> true
s = “”

if headerSet then
  listbox1.addrow ""
end if
listbox1.ColumnCount = max(Listbox1.ColumnCount, c.FieldCount)

for i = 1 to c.FieldCount
  dbf = c.idxField(i)
  if not headerSet then
    ListBox1.Heading(i-1) = dbf.StringValue
  else
    ListBox1.cell(listbox1.lastindex, i-1) = dbf.StringValue
  end if
next

headerSet = true

c.MoveNext

wend

c.Close[/code]

12

[quote=189762:@Norman Palardy][code]dim f as FolderItem
dim i as integer
dim s as string
dim c as CSVRecordSet
dim dbf as CSVField

ListBox1.DeleteAllRows

f = GetOpenFolderItem(“special/any”)

if f is nil then return

c = new CSVRecordSet(f)

listbox1.HasHeading = true
dim headerSet as boolean

while c.EOF <> true
s = “”

if headerSet then
  listbox1.addrow ""
end if
listbox1.ColumnCount = max(Listbox1.ColumnCount, c.FieldCount)

for i = 1 to c.FieldCount
  dbf = c.idxField(i)
  if not headerSet then
    ListBox1.Heading(i-1) = dbf.StringValue
  else
    ListBox1.cell(listbox1.lastindex, i-1) = dbf.StringValue
  end if
next

headerSet = true

c.MoveNext

wend

c.Close[/code][/quote]
Men, you are a Genius!

Its works perfectly
Regards

13

[quote=189764:@Gerardo García]Men, you are a Genius!
Its works perfectly
Regards[/quote]

You’re welcome

14

Now where’s Lars with the obligatory

15

Now I have a Doubt, This is my final Code:
dim f as FolderItem
dim i as integer
dim s as string
dim c as CSVRecordSet
dim dbf as CSVField

winPrincipal.Listafacturas.DeleteAllRows

f = GetOpenFolderItem(“special/any”)

if f is nil then return

c = new CSVRecordSet(f)

winPrincipal.Listafacturas.HasHeading = true
dim headerSet as boolean

while c.EOF <> true
s = “”

if headerSet then
  winPrincipal.Listafacturas.addrow ""
end if
winPrincipal.Listafacturas.ColumnCount = max(winPrincipal.Listafacturas.ColumnCount, c.FieldCount)

for i = 1 to c.FieldCount
  dbf = c.idxField(i)
  if not headerSet then
    winPrincipal.Listafacturas.Heading(i-1) = dbf.StringValue
  else
    winPrincipal.Listafacturas.cell(winPrincipal.Listafacturas.lastindex, i-1) = dbf.StringValue
  end if
next

headerSet = true

c.MoveNext

wend

c.Close

winPrincipal.Listafacturas.Width = 1000
'XML = 300
'RFC Emisor = 105
'Nombre Emisor = 320
'RFC Receptor = 110
'Nombre Receptor = 330
'Tipo = 50
'Serie = 100
'Folio = 70
'Fecha = 140
'Sub Total = 80
'Descuento = 80
'Impuesto Trasladado = 130
'Nombre Impuesto = 120
'Impuesto Retenido = 118
'Nombre Impuesto = 118
'Total = 120
'UUID = 280
'Metodo de Pago = 110
'Moneda = 110
'Tipo de Cambio = 120
'Estado = 90
'Estatus = 270
'Conceptos =100
'Traslado IVA:16 = 120
'Traslado IVA: 0.16 =
'Traslado IVA: 0.16 =

winPrincipal.Listafacturas.ColumnWidths= “300,105,320,110,330,50,100,70,140,80,80,130,120,118,118,120,280,110,110,120,90,270,100,120,120,120”

Everything Works perfect If I Use the code on an Action Button.

But if I try to use it on a Menu Handler, I need to add WinPrincipal to the listbox,like this: winPrincipal.Listafacturas
Despite of correcting this, Already I’m getting this error:

The Current function must return a value, but this Return statement does not specify any value.

If f is nil then return

What I’m doing wrong?

16

change it to

If f is nil then return false

Look at the top of the Editor in the IDE - you see ‘(MenuName) as Boolean’