PCO reading MS Excel files via MII 12.1

[Private_Member_17892]

Hi ,

I want to read an Excel file from MII 12.1 (currently on SP05 - I'm in the process to update to SP08 ) via PCO 2.1.

What I have done so far :

Installed PCO 2.1 SP04 on the MII server (thus all server pointing points to local server)

1. Created a OleDB Source System

1.1. This source system points to an Excel file

1.2 Provider : MS Jet 4.0 OLE DB Provider

2. Created a Destination Server that Points to the MII instance

3. Created an Agent that points to the Source system configured in point 1

3.1 Query Port is set to Legacy (as this is MII 12.1) and Port : 9000

4. Started the agent and it is running

5. Created a UDC data server in MII that points to the local server where PCO 2.1 is installed on port 9000 as per 3.1

6. Enabled the Data server

And this is where I'm stuck when checking the data server status it shows an Error ?

1. So what could be the issue ?

2. If I get this working how do I now read information from the excel workbook (or where can I get a sample or document showing how to do this)

Thanks in Advance

Morne

[Former Member]

Morne,

Try an OLEDB connector instead of UDC.That should do it.

Once the connection is established it is a typical SQL Query to read the data. Essentially you would be creating a Query of type SQL on the MII WB.

Just out of curiousity, are you doing this just as an MII excercise or this is an actual customer requirement?

Goodluck.

Udayan

[Private_Member_17892]

Hi,

Thanks for the reply.

I created a OLEDB Data server in MII but still I get an Error on checking the Status ?

Is there somewhere a decent piece of documentation on PCO 2.1 on how to configure it on both ends PCO and MII to get it working with a OLE object ?

I have read through the help of PCO and I got all the documentation on how to set up the PCO site to hook on to an Excel document, so I assume that in not getting any errors and the agent shows that it is running seems to be fine.

But then from the MII side it seems that it cannot communicate with the PCO Agent.

Might it be issues connecting via the port ? Security issues or specific windows services that needs to run to allow for

the port connection.

To answer your last question it is for a client. They have numerous excel reports where we need to pull information from, and this is not the ideal I know. So what we introduced to them is to have a single excel workbook that all the required data points will be populated from the various excel reports they have , so then we only need to read one sheet. This is the fast and nasty bit for now the next step is to export the information into a flat SQL table.

Regards

[Former Member]

I guess all the documentation is part of SAP help, which, as you mention, you already have access to.

Apart from that I am not sure if anything's out there.

Take a look at the NW logs for any specific error message that might give you some pointer.

The port could be an issue , did you try a different port?

Another option would be to use UDS 4.0 instead of the PCo, I mean you can try that if you have time coz like you said eventually you would be moving to a SQL DB (Unless you foresee PCo specific requirements).

I hope you figure it out soon.

Goodluck again.

[Private_Member_17892]

Hi,

Thanks for your help.

Everything was correct to the last letter. However the Agent that I configured was the issue in the end.

It seems that some how it was not creating the port listner on the ports which I was specifying. It kept on defaultting to port 9001, I saw this after checking firewall settings and using netstat to see what port was activated by the PCO agent when it started up.

I deleted the agent , re-created it and after that it did use the port that I specified. So it seems it somehow the original agents port configuration got corrupted ??

Oh yes the OLEDB data server is much better to use than the UDC data server in MII, thanks for that !!