MS Small Basic: number table input from excel sheet

Question

I am planning a MS Small Basic program where I am bring a fair amount of numbers as input. How can I read the number table from Excel sheet to the program? Antti

Small Basic is not the language for this. There are no features that would let you directly load in a spread sheet, you would have to save it as a CSV file and manually parse the data as you loaded it. Also, Small Basic's array system is designed for ease of use and as a result runs painfully slow. I would suggest you look into Visual Basic, or Python for this project. — codingCat, Apr 03 '17 at 00:34
I believe that the Litdev extension has some tools that would help with this. — Zock77, Apr 04 '17 at 01:29

score 0 · Answer 1 · answered Apr 13 '17 at 18:51

The Hard Way -

The great part about programming is that nearly any language can do nearly anything. Small Basic is no exception. If you don't mind the huge time hit you will see compared to other languages, you can do anything you could want with a file.

To prove the point I have worked up an example that will load an Excel table into a two dimensional array in Small Basic.

To import an Excel file into Small Basic, the first thing you need to do is save the file in a format that is easy to deal with. The example below assumes that the file has been saved in the comma delimited CSV format. Following this format each cell is ended by a comma, and each row is ended by a CR-LF combo. The rest is just a matter of parsing the info character by character.

'Reading an Excel Table saved as a CSV (comma seperated) file. 
TextWindow.Show()

LoadFile()
TextWindow.WriteLine("File Size = " + Text.GetLength(DataIn))
ParseFile()
TextWindow.WriteLine("Rows = " + rows + ",  Columns = " + cols)
DisplayTable()

'---------------------------------------------------------------

'Read the contents of the CSV file into memory
Sub LoadFile
  filename = Program.Directory
  filename = filename + "\excelintoSB.csv"

  DataIn = File.ReadContents(filename)
EndSub

'Parse the file contents, looking for commas and line breaks to separate the cells. 
Sub ParseFile
  row = 1
  col = 1
  cell = ""

  For i =1 To Text.GetLength(DataIn)  'Look at each character in the file
    ch = Text.GetSubText(DataIn,i,1)
    'Is it a comma or a cariage return? Store the Cell
    If ch = "," or text.GetCharacterCode(ch) = 13 then
      table[row][col] = cell
      cell = ""
      If text.GetCharacterCode(ch) = 13 Then 'end of row, start a new one
        row = row + 1
        col = 1
      Else 'New Cell, current row
        col = col + 1
        If col > maxCol then 'Keep track of how many columns we have encountered
          maxCol = col
        endif
      endif
    ElseIf text.GetCharacterCode(ch) <> 10 then 'build the cell, ignoring line feeds.
      cell = cell + ch
    EndIf
  EndFor
  rows = row - 1
  cols = maxCol
EndSub

'Display the table in row / column format
Sub DisplayTable
  TextWindow.WriteLine("The Table --- ")
  For i = 1 To rows
    TextWindow.Write("[ ")
    For j = 1 To cols
      TextWindow.Write(table[i][j] + " ")
    EndFor
    TextWindow.WriteLine("]")
  EndFor
EndSub

The following is the file I tested it with, created originally in Excel and saved as a CSV:

1a,1b,1c,1d
2a,2b,2c,2d
3a,3b,3c,3d

Enjoy!

MS Small Basic: number table input from excel sheet

1 Answers1