need help to store a excel sheet ie 20x20 in an array myarray[20,20]
Could you use ODBC to read the array?
yes but i need help is there any working example ?
OK. Here goes. ;D
Code and spreadsheet are also in the attached zip file.
'=============================================================================
' 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
REDEBOLT it works !!!! Thank you very much for your help
My pleasure!
8)
Very nice example!
It does not read the first line because it expects the fiirst line to be column titles and not data.
Haim
Thanks, Haim, for the clarification.
:)
P.S. Most of the code was copied from the help file.
REDEBOLT
i modyfy it to read strings instead of numbers
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
REDEBOLT
is it easy to to modify your sample to load strings ?
Do you know how long your maximum sized strings are? :)
Here goes:
'=============================================================================
' 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