MII - Excel Integration with PCo

[Former Member]

Hi Experts,

we are trying to integrate MII 12.1 with Excel sheet using OLEDB driver of PCo.

But , our EXcel sheet is in .xlsm format which is not supported by MS Jet 4.0 OLE DB Provider in PCo.

We need to query and write data into the Excel sheet in MII using SQL queries.

Please suggest us the Excel sheet versions that PCo can support and any other way to fulfill our requirement.

Thanks,

Sravan

[agentry_src · Accepted solution]

I would suggest that you put SQL Server between the Excel spreadsheets and the MII system. SQL Server should integrate easily with Excel and is a more robust connection to MII. And you will not have to go the MS Jet OLEDB route.

I would also suggest that you move away from Excel, but that is sometimes not possible.

Regards,

Mike

[Former Member · Accepted solution]

Hi Mike,

thanks for your response !

Unfortunately , our client is already using the Excel sheet and it's not in our hands to avoid it.

As we need to write into one sheet and read from another sheet , I'm afraid using an SQL server for just 2 sheets is not a good approach.

It would be great if there are any options to directly connect with .xlsm sheet from MII !

Thanks,

Sravan

[Former Member · Accepted solution]

Hi Mike,

we tried connecting with .xls file using PCo from MII .

the data sources is of type "MS Jet 4.0 OLE DB" , but surprisingly we r able to connect with UDC instead of OLEDB connector from MII.

in the blog http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/17991 one point is mentioned as : Clear the Show Only Enabled Servers check box.

I can't find it in 12.0 or 12.1.

would you please tell me where is that option available and in which version ?

Thanks,

Sravan

[agentry_src · Accepted solution]

I believe that is a legacy from MII 11.5. In early versions of MII, you were provided with pre-built templates for a long list of different server types. In order to only find the ones which were being used, that filter was provided. The long list was cut down in 12.0 and then mostly eliminated in 12.1. I can't say I know for sure why, but suspect one reason was due to the increase in driver selections for the main databases. There may be other reasons as well.

Good luck,

Mike

[former_member203984 · Accepted solution]

Hi,

The .xsl file excel sheet is been connecting using PCO.

1.First Add the Source System Type as OLEDB Agent.

2.Go to Configure and Select the Provider as" Microsoft Jet 4.0 OLEDB Provider".Click Next.

3.In connection tab point the .xsl Excel File and give the username and test the connection.

4.In Advance tab Select the Read/Write option.

5.In ALL tab give the Extended Properties as"Excel 8.0; HDR=YES".

Follow the above steps and create the Agent instance then it will be connecting to MII using Data Server.

[Former Member · Accepted solution]

Hi,

Whoever next visit this thread need to follow the below steps in the sequence.

1.First Add the Source System Type as OLEDB Agent.

2.Go to Configure and Select the Provider as" Microsoft Jet 4.0 OLEDB Provider".Click Next.

3.In ALL tab give the Extended Properties as"Excel 8.0; HDR=YES".

4.In connection tab point the .xsl Excel File and give the username and test the connection.

5.In Advance tab Select the Read/Write option

This is same as Partheeban said, but in different sequence because you can't select excel file without extending the properties.

BR,

Rakesh