从DB2读取Excel [英] Read Excel from DB2

问题描述

我必须定期导入一些Excel数据。检查 DB2文档可以通过外部函数直接访问OLE DB数据源。

I have to import some Excel data on a regular basis. Checking the DB2 documentation one can directly access OLE DB datasources via an external function.

但是我无法设置正确的我获得了 Microsoft Access数据库Enginge 2010 以及修复程序打包并将其安装在数据库服务器上。

However I'm unable to set it up properly. I got the Microsoft Access Database Enginge 2010 plus the fix pack and installed it on the database server.

我将Excel文件放在数据库服务器的本地目录中。 （ C：\Temp\test.xls ）

I placed the excel file in a local directory from the database server. (C:\Temp\test.xls)

excel有一个名为 TEST1 和两行 ABC 和 DEF 遵循一些数字数据：

The excel has a workbook called TEST1 and two rows ABC and DEF following some numeric data:

ABC | DEF
---------
1   | 5
2   | 6
3   | 7
4   | 8

为了创建表函数，我使用了以下语句：

For creating the table function I used the following statement:

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"';

似乎创建了这个功能。但是在查询数据时，请执行以下操作：

It seems to create that function. However when querying the data with:

SELECT * FROM TABLE(MYSCHEMA.test_excel()) AS FUNCTABLE;

我收到以下错误：

用户自定义函数MYSCHEMA.TEST_EXCEL从指定的OLE DB提供程序接收到OLE DB错误。 HRESULT =0x80040e37。诊断文本：Microsoft Access数据库引擎.. SQLCODE = -1183，SQLSTATE = 38506，DRIVER = 3.53.71

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

到文档错误意味着： 0x80040E37指定的表不存在。

！TEST！应该引用工作簿，但我不确定它是否是正确的语法。如何从DB2访问Excel工作表？有没有办法得到更详细的错误信息？有没有人正确的命名方案？

The !TEST! 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?

推荐答案

命名似乎不正确。看看其他oledb样本，我想到一个'$'丢失了。

The naming seems to be incorrect. Looking at other oledb samples I figured a '$' was missing.

！TEST $！引用名为 TEST 的工作表的名称，我可以访问数据。

!TEST$! works as an external name when referencing a worksheet called TEST and I can access the data.

这篇关于从DB2读取Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！