title: Import Excel 2010 into SQL Server
body: <p>I use Excel to collect &amp; configure data, then import it into SQL Server 2012 for storage.</p>
  
  <p>So far I've been using the SQL Server Import &amp; Export Wizard, but it is a pain to manually set it up constantly. Since I'm using Express, of course it won't allow me to save, or even view, the actual commands to transfer the data.</p>
  
  <p>I tried to set up a linked server, per <a href="http://support.microsoft.com/kb/306397/EN-US" rel="nofollow">How to use Excel with SQL Server linked servers and distributed queries</a>, but get the following error:</p>
  
  <blockquote>
    <p><em>The linked server has been created but failed a connection test. Do you want to keep the linked server?</em></p>
    
    <p><em>An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)</em></p>
    
    <p><em>Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FLTST".<br>
    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FLTST" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)</em></p>
  </blockquote>
  
  <p>I thought perhaps the Excel version number was the problem, since the web page is from 2005, so I tried with:</p>
  
  <ul>
  <li>Excel  8.0 (Excel 2002) as shown on the page</li>
  <li>Excel 12.0 (Excel 2007) which is what the wizard seems to use</li>
  <li>Excel 14.0 (Excel 2010) what I actually have</li>
  </ul>
  
  <p>All of those gave me identical results.</p>
  
  <p>Next I tried the <em>distributed query</em> as shown at <a href="http://forums.asp.net/t/1097839.aspx/1" rel="nofollow">Import excel file to SQL Server Express</a>, (again with different variations of the provider string)</p>
  
  <pre><code>USE ExTest
  
  SELECT * INTO TstTbl FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
  'Excel 14.0;Database=c:\ExTest.xlsm', [Contacts])
  
  go
  </code></pre>
  
  <p>Which gives me the following error:</p>
  
  <blockquote>
    <p><em>OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".<br>
    Msg 7303, Level 16, State 1, Line 3<br>
    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".</em></p>
  </blockquote>
  
  <p>Instead of going to SQL Server &amp; pulling the data in, should I stay in Excel &amp; push it over?</p>
  
  <p>What am I doing wrong?</p>
  
  <p>PS: <strong>Please</strong> don't tell me to convert it to a csv file! I'm trying to do <em>fewer</em> steps, not more!</p>