title: Read Excel from DB2
body: <p>I have to import some Excel data on a regular basis. Checking the <a href="http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0002170.html" rel="nofollow">DB2 documentation</a> one can directly access OLE DB datasources via an external function.</p>
  
  <p>However I'm unable to set it up properly. I got the <a href="http://www.microsoft.com/en-us/download/details.aspx?id=13255" rel="nofollow">Microsoft Access Database Enginge 2010</a> plus the fix pack and installed it on the database server.</p>
  
  <p>I placed the excel file in a local directory from the database server. (<code>C:\Temp\test.xls</code>)</p>
  
  <p>The excel has a workbook called <code>TEST1</code> and two rows <code>ABC</code> and <code>DEF</code> following some numeric data:</p>
  
  <pre><code>ABC | DEF
  ---------
  1   | 5
  2   | 6
  3   | 7
  4   | 8
  </code></pre>
  
  <p>For creating the table function I used the following statement:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION MYSCHEMA.test_excel ()
    RETURNS TABLE(ABC INTEGER,
              DEF INTEGER)
  LANGUAGE OLEDB
  EXTERNAL NAME '!TEST1!Provider=Microsoft.ACE.OLEDB.12.0;
        Data Source=C:\Temp\test.xls;
    Extended Properties="Excel 8.0;HDR=Yes"';
  </code></pre>
  
  <p>It seems to create that function. However when querying the data with:</p>
  
  <pre><code>SELECT * FROM TABLE(MYSCHEMA.test_excel()) AS FUNCTABLE;
  </code></pre>
  
  <p>I'm getting the following error:</p>
  
  <blockquote>
    <p>User defined function "MYSCHEMA.TEST_EXCEL" received an OLE DB error from specified OLE DB provider.  HRESULT="0x80040e37". Diagnostic text: "The Microsoft Access database engine".. SQLCODE=-1183, SQLSTATE=38506, DRIVER=3.53.71</p>
  </blockquote>
  
  <p>According to the <a href="http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.licensing.doc%2Fdoc%2Fr0053238.html" rel="nofollow">documentation</a> the error means: <code>0x80040E37 The specified table does not exist.</code></p>
  
  <p>The <code>!TEST!</code> should reference the workbook, however I'm unsure if it is correct syntax. How can one access a Excel worksheet from DB2? Is there a way to get a more detailed error message? Does anyone the correct naming scheme?</p>