Android Question Loading array, Tab delimiter

JamesGreaves

Active Member
Licensed User
I am really struggling to get my brain around loading an array, or list with data that has been exported from Excel with a TAB delimiter. This is what I have so far.

At the end of the day, I am wanting to be able to retrieve the data with something like:
Cell_Value = Data_Array (Row, Column)


B4X:
    Dim Data_Filename As String = Building_Name & ".Readings"
    
    Dim ALL_Data As List
    ALL_Data.Initialize
    ALL_Data.clear
    ALL_Data = File.ReadList(File.DirInternal,Data_Filename)
        
    Dim Row_Count As Int = 0
    Dim Data_Array() As List
    For Each Row_of_Data In ALL_Data
        Row_Count = Row_Count +1
        Data_Array().Set(Row_Count) = Regex.Split(TAB, Row_of_Data)
        
    Next
 

emexes

Expert
Licensed User
Unless you know the number of rows beforehand, then Data_Array should be a list (which can grow) rather than an array (which is fixed in size).

But today is your lucky day: you can probably assume that the number of rows is the same as the number of lines read into ALL_Data.

So you can go with either structure:

i/ a list of rows, each element being a list of cells, or
ii/ an array of rows, each element being a list of cells

If you specifically want the entire thing as a two dimensional array per your example Data_Array (Row, Column), then you will need to do an initial scan through ALL_Data to find the maximum number of columns used; then when you know the number of rows and columns, you can Dim Data_Array(NumRows, NumCols) and then scan ALL_Data again to fill in Data_Array.

Easy!!! Well... Easyish!

:)
 
Last edited:
Upvote 0

emexes

Expert
Licensed User
you will need to do an initial scan through ALL_Data to find the maximum number of columns used
well, maybe that's not absolutely necessary... if you already know the structure of the incoming data, ie, which columns are what, and thus how many columns you will be using, then you could just dimension the array accordingly, and drop any columns that are beyond your expectation.

Something else you might want to consider is dimensioning the array one element larger in each dimension, eg Dim Data_Array(NumRows + 1, NumCols + 1), so that your row and column numbers/letters directly align with the array indices, eg Data_Array(3, 5) would be spreadsheet cell E3, where E = column 5 and 3 = row 3.
 
Upvote 0

JamesGreaves

Active Member
Licensed User
I have data as follows which has been exported from Excel with TAB delimiters:
1 23 45 67
3 25 47 69
2 24 46 68
7 29 51 73
So I'm just needing to load this into a 2D array.
Of course there is a lot more data and I don't know how many rows and columns there will be.
 
Upvote 0

emexes

Expert
Licensed User
Did this code read the file to a List of Strings?
B4X:
Dim ALL_Data As List
ALL_Data.Initialize
ALL_Data.clear
ALL_Data = File.ReadList(File.DirInternal,Data_Filename)
btw no need for .Clear since .Initialize will be giving you an empty list anyway

and having said that, you could probably skip the .Initialize too by just having:
B4X:
Dim All_Data As List = File.ReadList(File.DirInternal, Data_Filename)
I tried to shorten it by just one more line, but couldn't. Zero line programs are the best kind, since they have zero bugs.

:)
 
Upvote 0

JamesGreaves

Active Member
Licensed User
Did this code read the file to a List of Strings?
B4X:
Dim ALL_Data As List
ALL_Data.Initialize
ALL_Data.clear
ALL_Data = File.ReadList(File.DirInternal,Data_Filename)
btw no need for .Clear since .Initialize will be giving you an empty list anyway

and having said that, you could probably skip the .Initialize too by just having:
B4X:
Dim All_Data As List = File.ReadList(File.DirInternal, Data_Filename)
I tried to shorten it by just one more line, but couldn't. Zero line programs are the best kind, since they have zero bugs.

:)
Thanks. I've just discovered TableView, I'm going to check that out. Hopefully there is a nice way to load and save data straight into it.
 
Upvote 0

emexes

Expert
Licensed User
If you're ok with an Array of Lists, then this might do it:
B4X:
Dim NumRows As All_Data.Size
Dim Data_Array(NumRows) as List
For R = 0 to NumRows - 1
    Data_Array(R) = Regex.Split(TAB, All_Data(R))
Next R
and then to access the data, use something like:
B4X:
Dim R As Int = 3
Dim C as Int = 5

Log("R" & R & "C" & C & " = " & Data_Array(R - 1).Get(C - 1))
No tested, no guarantees, but: hey, what could possibly go wrong?!?!?!
 
Upvote 0

JamesGreaves

Active Member
Licensed User
If you're ok with an Array of Lists, then this might do it:
B4X:
Dim NumRows As All_Data.Size
Dim Data_Array(NumRows) as List
For R = 0 to NumRows - 1
    Data_Array(R) = Regex.Split(TAB, All_Data(R))
Next R
and then to access the data, use something like:
B4X:
Dim R As Int = 3
Dim C as Int = 5

Log("R" & R & "C" & C & " = " & Data_Array(R - 1).Get(C - 1))
No tested, no guarantees, but: hey, what could possibly go wrong?!?!?!
Thanks @emexes I appreciate your persistence in helping me.
 
Upvote 0

udg

Expert
Licensed User
Longtime User
Zero line programs are the best kind, since they have zero bugs.

Pearl of wisdom.
Valid reply for those asking for a 100% bug-free program too. :)
 
Upvote 0

emexes

Expert
Licensed User
Zero line programs are the best kind, since they have zero bugs. :)
Pearl of wisdom. Valid reply for those asking for a 100% bug-free program too. :)
Note that I didn't promise it would compile... ;-)

Presumably there would be warnings about a missing main, or no return value, or... I remember spending a half-hour at high school helping a friend discover that Pascal programs have to end with a full stop (or some miniscule thing like that).
 
Last edited:
Upvote 0

klaus

Expert
Licensed User
Longtime User
You might have a look at StringUtils.LoadCSV.
It returns a List of String arrays, one String array per row.
To get the List:
lstRows = StringUtils.LoadCSV(DirName, FileName, TAB)
To get a row:
Private strRow() As String
strRow = lstRows.Get(row)

To get a cell:
Cell = strRow(col)
Be aware that the first line could be the headers.
 
Upvote 0

JamesGreaves

Active Member
Licensed User
THank
You might have a look at StringUtils.LoadCSV.
It returns a List of String arrays, one String array per row.
To get the List:
lstRows = StringUtils.LoadCSV(DirName, FileName, TAB)
To get a row:
Private strRow() As String
strRow = lstRows.Get(row)

To get a cell:
Cell = strRow(col)
Be aware that the first line could be the headers.
Thank you, I'll chekc it out now.
 
Upvote 0
Top