This is a sad story. Se developer came to me with the question: is there a DOS command to change an Excel file to a CSV file?
There was no context to the question, but I knew this was going to require a step back. The intention was to load different data sets on the same sheet into a database as part of a conversion away from EUC solutions to an enterprise solution. We discussed the merits of using third party libraries to access the Excel data, using an ETL tool with built in access engines (Informatica or SSIS), or using raw SQL. As he is stronger in SQL we discussed using OPENROWSET to access the data using the Excel/Access data access engine. We then planned to load into a staging table and parse out the subsets of data using the sub total columns as max row number delimiters.
One issue was that all examples we could find used the Jet 4.0 engine
This is only useful for 32 bit applications and servers. I found the 64 bit data access provider is the ACE provider. This needs to be downloaded and registered on the database server to allow access to EUC sources like Excel and Access in a 64 bit operating environment. the SQL would be reformatted as:
REFS:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
http://support.microsoft.com/kb/321686
http://social.msdn.microsoft.com/Forums/en-US/b6274401-d8fe-49ed-bd79-e8cd431fd3ce/microsoftjetoledb40-has-not-been-registered-for-openrowset-query?forum=transactsql
There was no context to the question, but I knew this was going to require a step back. The intention was to load different data sets on the same sheet into a database as part of a conversion away from EUC solutions to an enterprise solution. We discussed the merits of using third party libraries to access the Excel data, using an ETL tool with built in access engines (Informatica or SSIS), or using raw SQL. As he is stronger in SQL we discussed using OPENROWSET to access the data using the Excel/Access data access engine. We then planned to load into a staging table and parse out the subsets of data using the sub total columns as max row number delimiters.
One issue was that all examples we could find used the Jet 4.0 engine
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]
SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]') This is only useful for 32 bit applications and servers. I found the 64 bit data access provider is the ACE provider. This needs to be downloaded and registered on the database server to allow access to EUC sources like Excel and Access in a 64 bit operating environment. the SQL would be reformatted as:
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
REFS:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
http://support.microsoft.com/kb/321686
http://social.msdn.microsoft.com/Forums/en-US/b6274401-d8fe-49ed-bd79-e8cd431fd3ce/microsoftjetoledb40-has-not-been-registered-for-openrowset-query?forum=transactsql
