Interface of Excel - xMII

[Former Member]

Hi All,

Request all for their contribution on below mentioned query

<b>Objective:</b> To interface Excel document (located in xMII server) with xMII by creating <b>System DSN</b> under ODBC Data sources (Control Panel -> Administrative Tools (2000)).

<b>Steps Carried out:</b>

a. Created a System DSN on xMII server

b. In xMII created a dataserver by copying Default ODBC_Database

c. Renamed ODBCDSN in ServerURL of dataserver with the System DSN name created in step (a) (taken care of case sensitivity).

d. Saved and checked the status which displayed 1 (Sucessful Connection)

e. Opened Query template and selected New->SQLQuery ->selected dataserver created in step (c) ->Query - >Clicked SQL QueryDetails

Observation: Did not display the list of tables and fields (In this case the named ranges - Excel sheet)

<b>Note:</b> When I created System DSN for the pubs.xls (default OLEDB excel file provided in service marketplace) and connected to xMII, I could get output of tables and fields. while customized excel file isn't working.

Please provide input or solution for this ASAP.

With regards,

Deepak Pai

[srinivasarao_kambala4]

Hi Deepak

Check this Link

http://www.sapbrainsonline.com/ARTICLES/TECHNICAL/SAP/SAP%20xApps%20Articles.html

Thanks

Srinivas

[Former Member]

As Martin recommends, you will get the best performance and reliability using the OLEDB UDS connector. One other advantage of this approach is that the Excel file need not be located on the xMII server. The UDS(s) can be installed on any server, and communicate via TCP/IP to the xMII server.

Best regards,

Rick

[Former Member]

Hi Rick, Martin,

Thanks for the prompt response.

Following are my views.

a. There is no doubt on the OLE DB Connector capabilities and we have used it for our earlier projects as well.

b. I dont find the need to instal OLE DB Connector on xMII server, while ODBC connector can do the same task. (Objective is only to read the content from the Excel rather than Insert/update data back in Excel)

c. There is no need to locate the Excel file at some other remote location in my case and connet to xMII.

Having said that, can I stil get the solution on this.

With regards,

Deepak Pai

[Former Member]

Even on the same server, you should absolutely, without question, use the OLEDB approach. The Sun JDBC/ODBC bridge is notoriously vulnerable to memory leaks and other issues. I would strongly advise using the OLEDB UDS in all cases, even on the same server.

- Rick

[Former Member]

Rick,

Thanks. I would go ahead with OLEDB UDS rather than ODBC bridge for Excel connectivity on xMII server.

But its really surprising, why Excel data does not reflect in Query Template when ODBC connectivity was sucessful. (As mentioned, it displays all data for pubs.xls and not for customized Excel file)

Anyways, if you have any solution for this pls let me know, this may be of some help at time.

With regards,

Deepak Pai

[Former Member]

Hi, Deepak.

Did you have a named range defined in your Excel spreadsheet? This is required. It makes the Excel "range" appear to be a database table.

You'll need to do this either with ODBC or with OLEDB.

Best regards,

Rick

[Former Member]

Deepak,

I would highly suggest that you connect to Excel through the xMII OLE DB UDS, we have developed it specially for the OLE DB data sources. The help should help you with the connection details.

Martin.