XLS to Memory Table

Trying to loop thru an unknown formated XLS file and write it to a memory table. Although I see a TableToXLS, I don't see the reverse. So here is what I am doing.

Create a MemoryTable, one column.
Open the XLS file.
Get the number of columns (in this case 7).
For i = 2 to NumberOfColumns
..ControlClone the first column
End

That gives me a memory table with 7 columns.

Now I need to read through the XLS file and add lines to the table. I can read the value from each and every cell in the XLS file, by row and column. What I am having difficulty in is adding it to the memory table.

I tried TableAddLine(TheTable), then tried to find a way to modify each column in the table with the values from each cell. No luck.

Then I tried to concatinate all the XLS's cells on a row (like this):

AddLineValue is String
For j = 1 to NumberOfRows
..For k = 1 to NumberOfColumns
....CellValue = xlsData(xlsfile,j,k)

Then concatanate all the cell values into AddLineValue, which looks like this:

AddLineValue = ,"Shannon MacArthur","222-222-2222","2","Management","US","Management","Melinda Overmire"

Then I do TableAddLine(TheTable,AddLineValue)

That results in the entire AddLineValue ending up in the FIRST column in TheTable... not seperated into all 7 columns.

However, I can take the contents of AddLineValue and put it in TableAddLine and it works fine, adding all the values to the seperate columns in the table:

TableAddLine(TheTable,"Shannon MacArthur","222-222-2222","2","Management","US","Management","Melinda Overmire")

Any ideas where I'm going wrong?

Or better yet, is there a XLSToMemoryTable out there somewhere? I looked at BuildBrowsingTable and FileToMemoryTable but couldn't see where I could use the XLS as a data source (either File or View or Query).

Thanks in advance,
Art

Hi Art,

Best way in this case is the oldfashioned "tableadd".

For j = 1 to NumberOfRows 
    sLine=xlsData(xlsfile,j,1)
    For k = 2 to NumberOfColumns 
        sLine+=Tab+xlsData(xlsfile,j,k)
    end
    TableAdd(Table,sLine)
end

Best regards,
Piet

.

Yep, sure was.

THANKS!

Art