Importing Data

Background

With 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:
  • Web and application servers should have the Access Database Engine 2010 x64 version installed
    • IIS Application Pool Enable32BitApplications should be set to false
    • error message from mixing x64 and x86: 
      System.Runtime.InteropServices.SEHException: External component has thrown an exception.
  • MSSQL servers should have the Access Database Engine 2010 x86 version installed
    • error message: 
      "Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."
  • Ensure ad hoc distributed queries are enabled
    • EXEC sp_configure 'show advanced options', 1
      RECONFIGURE;
      EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
      RECONFIGURE;
    • error message: 
      SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' ...

Configuring Import File Templates

The 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:
  • Create an entry in FileObject.config called 'Database', and have it store data on the database's local drive (so SQL can read the file directly)
    <FileObject Name="Database" Type="qbo.Attachment.FileObjects.UNCFile, qbo.Attachment" Uri="//10.0.1.193/Documents/uatsls.quandis.net/" Compression="false"/>
  • Create an entry in Credentials.config matching this path
    <Credential UriPrefix="file://10.0.1.193/Documents/uatsls.quandis.net/" AuthType="Basic" Username="{some username}" Password="{some password}" Domain="" />
  • Create a Preview and an Import statement in ImportFile.config
  • Create an Import File Template pointing to the Preview and Import statements
    /Import/ImportFileTemplate.ashx
  • Create an Import File, uploading the spreadsheet as the Pre-Transform attachment
    /Import/ImportFile.ashx
  • Preview the data
    /Import/ImportFile.ashx/Preview?ID={Some ImportFileID}
  • Import the data
    /Import/ImportFile.ashx/Import?ID={Some ImportFileID}

Troubleshooting

The 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 | Money

The 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:
  • TypeGuessRows: setting the value to 0 (zero) will force ADO to scan all column values before choosing the appropriate data type,
  • ImportMixedTypes: should be set to value 'Text' i.e. import mixed-type columns as text,
  • IMEX: Using IMEX=1 in the connection string (as you have done) ensures the registry setting is applied
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:
  • Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
  • Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes
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.






Comments