Reading Excel files using BlitzData
Excel 95 used for this test.
Created spreadsheet with two columns, and three rows.
Looks like this:
Name Account
Stacy 123
Don 456
Defining the range will define the table name used in SQL.
Highlited the entire range, Insert, Name, Define, and specifiy the range name. I used 'DemoRange'.
Once this is done 'DemoRange' defines your table to ODBC.
This will enable your SQL statement to work.
Example: Select * from DemoRange
Saved the spreadsheet as Book1.xls
Used the following Blitzdata sample source to read the poor mans database.
Some of you maybe thinking.. why not just read from a CSV, and you'd be right.
I present, reading Excel files, because it's there.
8)
Special note:
www.connectionstrings.com
This site offers a connection string to process Excel data without range name definition.
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.
Excel 95 used for this test.
Created spreadsheet with two columns, and three rows.
Looks like this:
Name Account
Stacy 123
Don 456
Defining the range will define the table name used in SQL.
Highlited the entire range, Insert, Name, Define, and specifiy the range name. I used 'DemoRange'.
Once this is done 'DemoRange' defines your table to ODBC.
This will enable your SQL statement to work.
Example: Select * from DemoRange
Saved the spreadsheet as Book1.xls
Used the following Blitzdata sample source to read the poor mans database.
Some of you maybe thinking.. why not just read from a CSV, and you'd be right.
I present, reading Excel files, because it's there.
8)
Special note:
www.connectionstrings.com
This site offers a connection string to process Excel data without range name definition.
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.
Graphics 800,600,32,2 SetBuffer BackBuffer() t% = BD_OpenDatabase("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=d:\ExcelData\Book1.xls;DefaultDir=d:\ExcelData","","","",4) If t = -1 Then Text 20,20,"Could not open database." Flip key = WaitKey() End Else Text 20,20,"Opened TEST database Successfully." Flip While Not GetKey():Wend EndIf t = BD_SQLQuery("SELECT * FROM DemoRange ") If t = 0 Then Text 20,40, "Opened recordset successfully" Flip EndIf Text 20,60, "Columns in DemoRange: " + BD_ColumnCount() Text 20,70, "Rows in DemoRange: " + BD_RecordCount() BD_CloseQuery() t = BD_SQLQuery("SELECT Name, Account FROM DemoRange") If t = 0 Then Text 20,90, "Opened recordset successfully" Flip EndIf For t = 1 To BD_RecordCount() Text 20, 80 + (t * 20), BD_GetColumn("Name")+", "+ BD_GetColumn("Account") BD_NextRecord() Flip WaitKey() Next BD_CloseQuery() BD_CloseDatabase() While Not GetKey():Wend