Excel Integration with SAP MII Using PCO 2.3

[Former Member]

Hi Experts,

Requirement: Need to read Excel(.xlsx) file into SAP MII using SAP PCO 2.3 or any other recommended version, SAP MII 12.2 is the platform.

I created the following Source configurations:

In the connection string, following settings done:

I am using the above selected Provider for ODBC Drivers, as I was not able to read and test the connection succefully with Jet 4.0 Provider, further configurations done as:

My Actual Requirement is to read .xlsx but could not find it in the above list, so just tried with .xls :

Now I am able to read the Excel File, and test connection successfully:

Although Connection succeeds, I am not able to succesfully create an agent against it and run it, I get the error in log as:

Could you please let me know your valuable suggestions, how to proceed further.

Thanks in Advance

[Former Member · Accepted solution]

Hi Saptaparna,

I agree with Steve that it works fine in PCo 15.0. and another important point is you have to deploy the access database engine. in older version of PCo sometime MS Jet OLE DB source type doesn't work properly.

You can refer another post along with the suggestion of Steve for PoC or dev.

Hope it will help you.

Regards,

Suman

[Former Member · Accepted solution]

Hi Suman,

Yes, it is definitely helpful, but I would like to understand just for information why .xls file I could not locate using the 4.0 Jet provider whereas it should have worked for .xls, although my requirement is for .xlsx( SAP PCO 15.0).

Any clue You can give to my above queries would be further helpful for me to suggest and confirm the solution.

Thanks.

[former_member196557]

Hi Saptaparma,

I can set this up in PCO 15.0 using the following configuration:

if you don't have Office installed on PCo server you will need to download and deploy the Micorsoft Access Database Engine for Office 2010 (may a newer one for office 2013 but this works for xlsx files)

Provider

Connection

All Settings

Regards, Steve

[Former Member]

Hi Steve,

Thanks a lot for your valuable response.

Problem is curently I do not have SAP PCO 15.0 available with me to check the following things I have some doubts about:

1] I thought so, would not be configurable in old PCO versions, as in SCN help also the Provider Microsoft Access DB 12.0 OLEDB provider is suggested and I could not find that in the PCO 2.3.

[Using Microsoft Excel - SAP Plant Connectivity - SAP Library]

Could you please let me know why it is not available ?

2] Could i integrate SAP PCO 15.0 with SAP MII 12.2 SP 6 Patch 15 ? My doubt is mainly because SAP PCO 15 comes in instalable package with SAP MII 15.0, so can not actually check if SAP MII 12.2 is allowed in the configuration to access SAP PCO 15.0 without the PCO 15.0 Console management[Netweaver stack being diffrent till SAP MII 15.0]. If you could please let me know I can suggest this to client or management so that I can further create some POC to propose. Right now i do not have PCO 15 installed in the nvironment to check.

3] Would this create a seperate .dbo reference to the excel, similarly like earlier when using UDS we could create Access DB structure for reference.

4] When I tried to use Microsoft Jet 4.0 OLE DB Provider in PCO 2.3 I could not recognise the File (.xls) located? Was it because curently in the PCO server no Excel office package exists?

I am looking forward for your further response for the above queries I have.

Thank you very much.

[former_member196557]

Hi Saptaparna,

1, PCo OLEDB agent is only a wrapper to connect to an existing, installed OLEDB driver. You have to deploy the Microsoft Access Database Engine package, or install Office 2010 on the PCo server.

2, You can connect PCo 15 with older MII versions; this is defined in the Query Ports tab of the PCo  Agent Instance.  PCo does not use Netweaver and has no dependency on it.

3. Each OLEDB Agent Instance connects to only one xls or xlsx spreadsheet, so it does look like a database connection.

4. I believe that this is true, you need the Access Database Engine to access xlsx files.  Again, this is related to the actual OLEDB driver, PCo only accesses the driver and hosts the dialogs for settings, etc.

Regards, Steve

[Former Member]

Hi Steve,

Thanks a lot for the clarifications, let me try requesting fo rthe installation package for the Microsoft Access Database Engine and install Office 2010 on the PCo server i.e PCO 2.3 one and check whether I can get the requirement done.

I will surely get back to you with more quiries in case i get stuck again while achieving the same.

Regards,

Saptaparna

[Former Member]

Hi Steve,

Facing issue again, hoping for your response on this situation.

Client has requested to use PCO 2.1.5.1 , so I had been trying to establish the excel connection with PCO 2.1.5.1 console.

In the machine(Windows 2008 R2 Enterprise , SP1, 64 bit) PCO is installed, the "Access Database Engine x64" is installed now:

This I found in Microsoft site, that would install the driver to recognize excel, but still was not able to get the Microsoft Access DB 12.0 OLEDB provider in the PCO console management while configuring Source.[So I am still trying it with .xls instead of .xlsx]

Still, I was able to make connection to MII using OLEDB connector in the data server and server status in running mode checking legacy in the query port:

If I select MII and then run the connection, I was not able to establish connection with OLEDB connector type in Data servers, so for the time being I kept it as Legacy port type only.

Now I tried to query from MII workbench, even though I am able to now get all the columns of excel having the sheets in excel, whenever I query the table, I get the following error:

Error tells me Data Source not Supported, stuck here.

Please note Client has refused to install the Office 2010 package on the PCO server machine.

I am awaiting for your suggestions further, Please let me know.

Regards,

Saptaparna

[former_member196557]

Hi Saptaparna,

Looks like the connection is between PCo, OLEDB and MII is working. Did you configure the OLEDB driver exactly as in the example I provided above, especially the Extended settings for the driver for Excel?

I would recommend that you  PCo to the latest 2.1 SP and patch. 2.1.5 is a very old version.  I do not maintain an installation of PCo 2.1 and cannot do any testing with that release.

I would also recommend that you deploy PCo 15 on a separate PC and confirm that this will work with the newer PCo version and the OLEDB configuration that I have also tested.

Regards, Steve

[Former Member]

Hi Steve,

The issue is resolved now, I was not able to get the provider "Microsoft Access DB 12.0 OLEDB" in the PCO 2.1.5.1 with the "Access Database Engine x64" package installed on the server, recommended the 32-bit version , got it installed on client server and after that I was able to configure the source agent with the required provider "Microsoft Access DB 12.0 OLEDB" for the .xlsx file.

Now MII Query is giving me the Excel file output with proper column values from the excel file.

I have still kept the connector type as OLEDB while configuring in the MII Data Server.

For PCO connecter type, I was not able to configure the URL and it was not working, though not sure why?,[any pointer from you here would help me to learn further] but with OLEDB connector type my requirement is fulfilled it seems, I am yet to test the solution thoroughly, but atleast the main hurdle is now resolved.

For PCO 15 I am sure as you have already assured me about it will work, I will try it in another server having PCO 15 and use PCO connecter type as in that server I was able to configure the URL.

To conclude I guess, it is all about the correct version Access DB installation package to have the driver  recognize the particular file in required format for the PCO Version X.X.X.X.

Thanks a lot for your expert suggestions, as they have really helped me to overcome the challenges during the research.

I will get back to you again if needed in future for further suggestions.

Best Regards,

Saptaparna