BackgroundWith QBO 3's flexible query building, it is straight forward to have SQL read data directly from Excel, CSV or flat files. To import data from Excel:
Configuring Import File TemplatesThe qbo.Import and qbo.ImportWeb project contain the infrastructure for enabling end users to upload spreadsheets or CSV files for import into the database. Initial configuration includes:
TroubleshootingThe Microsoft Office Access database engine could not find the object 'Sheet1$' With a file named "Import.Sample.xlsx", I attempted this (note the misspelled Samples): Select * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\inetpub\local.quandis.net\Upload\Import.Samples.xlsx;HDR=Yes', 'SELECT * FROM [Sheet1$]') I received an error telling me that $Sheet1 could not be read. SQL appears to have created a 7K Excel file, without any valid content! Ultimately, I used Process Explorer to hunt this down and close the file handles. Access Denied; Cannot get column information If the spreadsheet has column headers in the first row, one is supposed to include HDR=Yes, otherwise, HDR=No in the connection string. I copied an example from the 'net with HDR=YES (note the caps). This produced: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied. Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". Changing to HDR=Yes (note the case) fixed the issue. Type Conversion: Text as Number | Double | MoneyThe Excel drivers (ACE and JET) by default attempt to scan the first 8 columns of a spreadsheet to determine data type. This behavior can be somewhat customized by:
If a column is not of mixed type (e.g. 12345 and A2345), no setting will cause ACE/JET to treat the column as text. The JET registry settings are:
The QBO 3 ExcelBulkEngine now supports a ForceAsText parameter, which will use nvarchar(255) as the column type when creating a table regardless of the data type returned by the OleDbReader. |