title: Importing Excel 2007 File Using SQL Server 2005
body: <p>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:</p>
  
  <ul>
  <li>In SQL Server Management Studio, expand Server Objects in Object Explorer.</li>
  <li>Right-click Linked Servers, and then click New linked server.</li>
  <li>In the left pane, select the General page, and then follow these steps:</li>
  <li>In the first text box, type any name for the linked server.</li>
  <li>Select the Other data source option.</li>
  <li>In the Provider list, click Microsoft Jet 4.0 OLE DB Provider.</li>
  <li>In the Product name box, type Excel for the name of the OLE DB data source.</li>
  <li>In the Data source box, type the full path and file name of the Excel file.</li>
  <li>In the Provider string box, type Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97  - workbook.</li>
  <li>Click OK to create the new linked server.</li>
  </ul>
  
  <p>From Here: <a href="http://support.microsoft.com/kb/306397/EN-US" rel="nofollow noreferrer">http://support.microsoft.com/kb/306397/EN-US</a></p>
  
  <p>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:</p>
  
  <ul>
  <li>Provider: Microsoft Office 12.0 Access Database Engine OLE DB</li>
  <li>Provider Product Name: Excel Data source: C:\Documents and</li>
  <li>Settings\UserName\Desktop\Main\FilesIns\MyExcelFile.xlsx Provider</li>
  <li>String: Excel 12.0 </li>
  <li>Location: </li>
  <li>Catalog:</li>
  </ul>
  
  <p>I then got this error:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>I went into security and added nt authority\system just in case it was permissions problem and it still gave me the error above.</p>
  
  <p>The excel table is called 'Report 1'.</p>
  
  <p>I also tried using openrowset in sql to see what happened and I got this:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>I then tried the code in link below to see if it helped me use openrowset:</p>
  
  <p><a href="https://stackoverflow.com/questions/14544221/how-to-enable-ad-hoc-distributed-queries">How to enable Ad Hoc Distributed Queries</a></p>
  
  <pre><code>EXEC sp_configure 'show advanced options', 1
  RECONFIGURE
  GO
  EXEC sp_configure 'ad hoc distributed queries', 1
  RECONFIGURE
  GO
  </code></pre>
  
  <p>And got:</p>
  
  <blockquote>
    <p>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)". </p>
  </blockquote>
  
  <p>So I am not sure if the values in my openrowset statement are correct.</p>
  
  <p>Really just looking for a way to import this file into an sql table without using SSIS - I cant use it anyway.</p>
  
  <p>Thanks</p>
  
  <p>Andrew</p>