storing a excel sheet in an array

[splakidas]

need help to store a excel sheet ie 20x20 in an array myarray[20,20]   

[REDEBOLT]

Could you use ODBC to read the array?

[splakidas]

yes but i need help is there any working example ?

[REDEBOLT]

OK. Here goes. 

Code and spreadsheet are also in the attached zip file.

Code Select Expand

'=============================================================================
' Using ODBC to access data in an Excel spreadsheet.
'=============================================================================
'=============================================================================
' Operations note:  I couldn't make the program read the first row of the spreadsheet.
'    So I inserted a dummy row of all zeroes at the beginning of the spreadsheet.
'    This is most likely unacceptable.
'=============================================================================
openconsole

string strConnection
string somepath,driver,filename,options,selct
pointer pDB, pTables, pColumns
int hstmt,i,j,k,l,m,n
int myarray[20,20]
'=============================================================================
' Change this path to your spreadsheet source.
'=============================================================================
somepath = "F:\\File Server\\Installs\\EMERGENCE BASIC\\EXCELODBC\\"

'=============================================================================
' DSN-less connection string example.
'=============================================================================
' http://students.ukdw.ac.id/~sonny/odbc_dsnless.html#ODBCDriverForExcel
'strConnection =  "Driver={Microsoft Excel Driver (*.xls)};" + _
'              "DriverId=790;" + _
'              "Dbq=" + somepath + "EXCELODBC.xls;" + _
'              "DefaultDir=" + somepath

'=============================================================================
' My Home-brewed DSN-less connection string example.
' Connect to the database.
'=============================================================================
driver = "Microsoft Excel Driver (*.xls)"
filename = somepath + "EXCELODBC.xls"
options = ""

pDB = dbConnect(driver,filename,options)

if pdb = NULL then
print "Database connection is not established."
goto eoj
endif

print "Database connection is established."
PRINT

'=============================================================================
' List the table names.
'=============================================================================
pTables = dbListTables(pdb)
PRINT "Tables:"
IF pTables <> NULL
    FOR temp = EACH pTables as STRING
        PRINT #temp
PRINT "Cardinality:",dbCardinality(pdb,#temp)
PRINT
    NEXT
    ListRemoveAll(pTables,TRUE)
ENDIF
PRINT

'=============================================================================
' List the column names.
'=============================================================================
pColumns = dbListColumns(pdb,"Sheet1$") ' List the column names.
PRINT "Columns:"
IF pColumns <> NULL
    FOR temp2 = EACH pColumns as STRING
        PRINT #temp2,", ",
    NEXT
    PRINT
    ListRemoveAll(pColumns,TRUE)
ENDIF
PRINT

'=============================================================================
' Pause so you can read the screen.
'=============================================================================
PRINT "Press any key to continue..."
do:until inkey$<>""
print

'=============================================================================
' Read the database.
'=============================================================================
selct = "SELECT * FROM [Sheet1$]"
hstmt = dbExecSQL(pdb,selct)

error = dbGetErrorCode(hstmt)
IF LEN(error)
    PRINT
    PRINT "Error Code: ", error
PRINT selct
    PRINT "Error Text: ", dbGetErrorText(hstmt)
    PRINT
GOTO FreeIt
ELSE
i=0:j=0
    WHILE dbGet(hstmt)
print "Row #:", i+1
j = 1
while dbGetData(hstmt, j, myarray[i,j-1])
print myarray[i,j-1],
j++
endwhile
i++
print
if (i % 7) = 0 then
print
PRINT "Press any key to continue..."
do:until inkey$<>""
print
endif
    ENDWHILE
ENDIF

'=============================================================================
' Verify beginning and ending rows of the array.
'=============================================================================
print "First row: ",myarray[0,0], myarray[0,19]
print " Last row: ",myarray[19,0], myarray[19,19]
print

'=============================================================================
' Close the database.
'=============================================================================
label FreeIt
dbFreeSQL(hstmt)
dbDisconnect(pDB)

'=============================================================================
' Final exit.
'=============================================================================
label eoj
print "Done"
PRINT "Press any key to exit..."
do:until inkey$<>""

closeconsole


[splakidas]

REDEBOLT  it works !!!! Thank you very much for your help

[REDEBOLT]

My pleasure!

[Haim]

Very nice example!
It does not read the first line because it expects the fiirst line to be column titles and not data.

Haim

[REDEBOLT]

Thanks, Haim, for the clarification.


P.S. Most of the code was copied from the help file.

[splakidas]

REDEBOLT

i modyfy it to read strings instead of numbers

Code Select Expand

string strConnection
string somepath,driver,filename,options,selct
pointer pDB, pTables, pColumns
int hstmt,i,j,k,l,m,n
string myarray[3,5]  <-----------------this
'=============================================================================
' Change this path to your spreadsheet source.

but i dont know, must it be string or istring ??

and i would like to know also how can i determine the table size x,y ??
because if i open a sheet i would like to know the size of the table and then insert all the strings in it

Thank you

[splakidas]

REDEBOLT
is it easy to to modify your sample to load strings ?

[REDEBOLT]

Do you know how long your maximum sized strings are?

[REDEBOLT]

Here goes:

Code Select Expand

'=============================================================================
' Using ODBC to access data in an Excel spreadsheet.
'=============================================================================
'=============================================================================
' Operations note:  I couldn't make the program read the first row of the spreadsheet.
'    So I inserted a dummy row of all zeroes at the beginning of the spreadsheet.
'    This is most likely unacceptable.
'=============================================================================
openconsole

string strConnection
string somepath,driver,filename,options,selct
pointer pDB, pTables, pColumns
int hstmt,i,j,k,l,m,n,nTables,nColumns
string myarray[20,3]
'=============================================================================
' Change this path to your spreadsheet source.
'=============================================================================
somepath = "C:\\EXCELODBC\\"

'=============================================================================
' DSN-less connection string example.
'=============================================================================
' http://students.ukdw.ac.id/~sonny/odbc_dsnless.html#ODBCDriverForExcel
'strConnection =  "Driver={Microsoft Excel Driver (*.xls)};" + _
'              "DriverId=790;" + _
'              "Dbq=" + somepath + "EXCELODBC.xls;" + _
'              "DefaultDir=" + somepath

'=============================================================================
' My Home-brewed DSN-less connection string example.
' Connect to the database.
'=============================================================================
driver = "Microsoft Excel Driver (*.xls)"
filename = somepath + "EXCELODBC3.xls"
options = ""

pDB = dbConnect(driver,filename,options)

if pdb = NULL then
print "Database connection is not established."
goto eoj
endif

print "Database connection is established."
PRINT

'=============================================================================
' Pause so you can read the screen.
'=============================================================================
PRINT "Press any key to continue..."
do:until inkey$<>""
print

'=============================================================================
' List the table names.
'=============================================================================
pTables = dbListTables(pdb)

PRINT "Tables:"
IF pTables <> NULL
    FOR temp = EACH pTables as STRING
        PRINT #temp
PRINT "Cardinality:",dbCardinality(pdb,#temp)
PRINT
nTables++ ' Count the tables.
    NEXT
    ListRemoveAll(pTables,TRUE)
print "Number of Tables = ",nTables
print
ENDIF
PRINT

'=============================================================================
' List the column names.
'=============================================================================
pColumns = dbListColumns(pdb,"Sheet1$") ' List the column names.
PRINT "Columns:"
IF pColumns <> NULL
    FOR temp2 = EACH pColumns as STRING
        PRINT #temp2,", ",
nColumns++ ' Count the columns.
    NEXT
    PRINT
print
print "Number of Columns = ",nColumns
print
    ListRemoveAll(pColumns,TRUE)
ENDIF
PRINT

'=============================================================================
' Pause so you can read the screen.
'=============================================================================
PRINT "Press any key to continue..."
do:until inkey$<>""
print

'=============================================================================
' List the column names.
'=============================================================================
pColumns = dbListColumns(pdb,"Sheet2$") ' List the column names.
nColumns = 0
PRINT "Columns:"
IF pColumns <> NULL
    FOR temp2 = EACH pColumns as STRING
        PRINT #temp2,", ",
nColumns++ ' Count the columns.
    NEXT
    PRINT
print
print "Number of Columns = ",nColumns
print
    ListRemoveAll(pColumns,TRUE)
ENDIF
PRINT

'=============================================================================
' Pause so you can read the screen.
'=============================================================================
PRINT "Press any key to continue..."
do:until inkey$<>""
print

'=============================================================================
' Read the database.
'=============================================================================
selct = "SELECT * FROM [Sheet2$]"
hstmt = dbExecSQL(pdb,selct)

error = dbGetErrorCode(hstmt)
IF LEN(error)
    PRINT
    PRINT "Error Code: ", error
PRINT selct
    PRINT "Error Text: ", dbGetErrorText(hstmt)
    PRINT
GOTO FreeIt
ELSE
i=0:j=0
    WHILE dbGet(hstmt)
print "Row #:", i+1, "\x20\x20",
j = 1
while dbGetData(hstmt, j, myarray[i,j-1])
print myarray[i,j-1],"\x20\x20",
j++
endwhile
i++
print
if (i % 20) = 0 then
print
PRINT "Press any key to continue..."
do:until inkey$<>""
print
endif
    ENDWHILE
ENDIF

'=============================================================================
' Verify beginning and ending rows of the array.
'=============================================================================
i-=1
print
print "First row: ",myarray[0,0], "\x20\x20", myarray[0,1], "\x20\x20", myarray[0,2]
print " Last row: ",myarray[i,0], "\x20\x20", myarray[i,1], "\x20\x20", myarray[i,2]
print

'=============================================================================
' Close the database.
'=============================================================================
label FreeIt
dbFreeSQL(hstmt)
dbDisconnect(pDB)

'=============================================================================
' Final exit.
'=============================================================================
label eoj
print "Done"
PRINT "Press any key to exit..."
do:until inkey$<>""

closeconsole