Procedure to Integrate PCo with SAP MII to read Excel(.xlsx) files
Recently due to a project requirement, I had to integrate PCo for reading excel(.xlsx) with SAP MII. Thought of sharing the details so that if anyone has similar requirement can easily follow the steps and achieve the same.
- Tested Environment :
SAP MII Version : 12.2 SP6 Patch 15
SAP PCO : PCo 2.1.5.1
Installation Package : Microsoft Access DB Engine X32-bit
Connector Type : OLEDB
The PCo and Microsoft Access DB Engine should be installed on the same server.
- Note : SAP MII version and PCo version is independent and this should be configurable if the required provider exists, as to read excel(.xlsx) through PCo into MII is to have the SAP recommended provider “Microsoft Office 12.0 Access Database Engine OLE DB Provider”.
Refer the following links for details:
http://scn.sap.com/thread/3786499
https://www.microsoft.com/en-us/download/details.aspx?id=13255
http://help.sap.com/saphelp_mii140sp04/helpdata/en/4e/73a42090c5455780c43287923c1022/content.htm
- Steps :
- Install the PCo 2.1.5.1 in the Server(to be connected with MII data server) with Microsoft Access DB Engine X32 bit to get the provider to read excel file(.xlsx).
- Configure the Source System in PCo management console by following the steps below:
- Create New Source System.
2.Select Source System Type as “OLE DB Agent” , provide a relevant name and description to it and click “OK”
3.Once the Source is created click on Configure and set the Data Link Properties:
- Provider Tab: Select Provider “Microsoft Office 12.0 Access Database Engine OLE DB Provider” and click on Next.
- Make sure that the File is not open in anywhere while testing the connection and click on test connection, on successful connection you will get the following pop up.
- In the Advanced tab, check on the access permissions you would like to give the user.
- In the All Tab you will see in Data Source the exact file path you provided earlier( Kept blank here for project specific confidentiality), Extended Properties should have Value “Excel 8.0;HDR=YES” and Persist Security Info should have Value False as shown below:
- Click “OK” to save the Source as shown above.
- Select the Agent you just created , and Add an Agent Instance against that.
- Source System should have the Source Name selected, give an appropriate Agent Name and Description, and Click OK.
- Configure the Agent Instance further by giving the Service Username and relevant Password of the server.
- Check the Legacy Type, put the Port no(9000 is default port)
- Save the Agent Instance.
- Go to services.msc and provide the system username and confirm the password again, because sometimes the PCo agent is not able to recognize credential provided initially during agent instance configuration and gives error in PCo Log. The system username and password should be same as you have put in earlier step while configuring the PCo Agent.
- In the Windows Firewall with Advanced Security, check if the port 9000 is added, if not add the respective port here in Inbound Rules.
- Configure the MII Data Server to read the excel file through the Connector. Current configuration done via OLEDB Connector as:
In the Settings, check the Enabled box, in the Connection tab provide the IP/Server Name of the PCo installed machine and also provide the port as configured for in the PCo[9000 in this case].
On enabling check status further, it should be in running mode:
- Open MII WorkBench, Open an SQL Query, select the Data server “Test Excel” and choose “Fixed Query” Mode, and go to Query, you will see all the relevant columns available in the excel sheet per worksheets available and you can apply a simple select * from [<tablename>].
Now you are able to access all the Excel Sheet data you needed, and you can use it further as per your requirement.
Hope this blog will be helpful to you guys.
Also my special Thanks to Steve Stubbs for his valuable suggestions during my learning process for PCo.
Good blog. Really helpful. Great Job. 🙂
That's an informative blog. Thanks for sharing.
great blog. thanks for sharing you experience.
Thanks Guys, glad that you all found it informative.
Thank you for sharing your experience!
Hi Saptaparna,
Thanks for sharing your experience.
Suggestions required from you and from anyone reading the blog :
I was able to read the excel file but used Pco data connector in MII instead of OLEDB.
In both the cases the SQL select query and PCO DB fixed query runs equally good and returns expected values. Which data connector would be preferred for excel/.mdb files ?
Thanks
Shikhil
Hi Shikhil,
Good Question, When I was doing RnD on it I remember checking Microsoft site, it definitely recommended OLEDB because then PCo connector was not known but now as we have inbuilt Pco connector, it should be logically best over OLEDB considering current version of PCo i.e PCo 15.
My environment did not allow me to configure that and so I preferred OLEDB to get the requirement done.
However, full proof reasons I would ask Steve which one is preferable.
Thanks.
Thank you for such a consolidated info on PCo , Saptaparna 🙂
Regards,
Shailaja
Very nice informative blog. Thanks for sharing it.
Regards,
Anshul Arora
I followed the same procedure but I am getting an error Connection Refused for OLE DB connector.
Also I tried with PCo Query mode DBFixedQuery but it is giving below error:
 Query execution failed due to following:
OleDb Exception for query [select * from Sheet1$] Possible reason: Feature might not be supported by the Data source.
[Microsoft Access Database Engine] : Syntax error in FROM clause.
Can someone help me with this.
Regards,
Pooja Nilakhe
Â