I have an excel 2007 file with about 301808 rows and 2 columns. I was trying to use SSIS to import but cant use 2007 excel. I then decided to try and make a linked server in SQL Server, Following the instructions here:
- In SQL Server Management Studio, expand Server Objects in Object Explorer.
- Right-click Linked Servers, and then click New linked server.
- In the left pane, select the General page, and then follow these steps:
- In the first text box, type any name for the linked server.
- Select the Other data source option.
- In the Provider list, click Microsoft Jet 4.0 OLE DB Provider.
- In the Product name box, type Excel for the name of the OLE DB data source.
- In the Data source box, type the full path and file name of the Excel file.
- In the Provider string box, type Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97 - workbook.
- Click OK to create the new linked server.
From Here: http://support.microsoft.com/kb/306397/EN-US
However the instructions only covers previous excel files not 2007 so I looked up connection strings for 2007 and used Excel 12.0 instead of Excel 8.0 in settings of linked server:
- Provider: Microsoft Office 12.0 Access Database Engine OLE DB
- Provider Product Name: Excel Data source: C:\Documents and
- Settings\UserName\Desktop\Main\FilesIns\MyExcelFile.xlsx Provider
- String: Excel 12.0
- Location:
- Catalog:
I then got this error:
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "EXCEL_MY_FILE" does not contain the table "Report 1$". The table either does not exist or the current user does not have permissions on that table.
I went into security and added nt authority\system just in case it was permissions problem and it still gave me the error above.
The excel table is called 'Report 1'.
I also tried using openrowset in sql to see what happened and I got this:
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server.
I then tried the code in link below to see if it helped me use openrowset:
How to enable Ad Hoc Distributed Queries
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
And got:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Syntax error in FROM clause.". Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "Select * from C:\Documents and Settings\UserName\Desktop\Main\FilesIns\MyExcelFile.xlsx" for execution against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
So I am not sure if the values in my openrowset statement are correct.
Really just looking for a way to import this file into an sql table without using SSIS - I cant use it anyway.
Thanks
Andrew
Under your sp_Reconfigure, see that you reference an article here.. However, I think you may need to go to Microsoft instead..
http://technet.microsoft.com/en-us/library/ms187569%28v=sql.90%29.aspx
And there are quite a few examples of how to use OPENROWSET here -
http://technet.microsoft.com/en-us/library/ms190312%28v=sql.90%29.aspx