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