topic Re: Validate data in Excel from SAP in Product Forum

Validate data in Excel from SAP

NafizZainal — Wed, 11 Jan 2023 02:26:34 GMT

Hi,

I would like to check a column (etc. Output device) in an excel collection to validate it from a collection in SAP. This process is to validate whether the output device column in excel was written correctly. It is like a data comparison between excel table and SAP table.

Please guide me on how to make a solution design for this.

Much appreciated.
Thank you

Re: Validate data in Excel from SAP

PvD_SE — Wed, 11 Jan 2023 10:42:56 GMT

Hi N,

Do you just need an answer if the two collections are identical, or do you need to know which values differ/are identical between the collections?

Happy coding!
---------------
Paul
Sweden

Re: Validate data in Excel from SAP

NafizZainal — Thu, 12 Jan 2023 08:42:11 GMT

Yes, that's right! I need to know which values in excel collection differ from SAP to give it an exception.

Re: Validate data in Excel from SAP

PvD_SE — Thu, 12 Jan 2023 10:02:01 GMT

Hi N,

Ok, next question (just to be sure):
So you have two collections in BP, one collection from the XL and another collection from SAP. Right?

If we assume the above to be true, you got a number of options:

Count the number of rows of both collections. If not the same, the collections are different.
Loop through one of the collections, checking each row against the other collection using the 'Collection contains value' action. If not found, the collections are different.
Compare the collections directly with the 'Intersection Between Collections' action.
Search for one of the previously described collection compare solutions on this community. (I'm quite sure there's one showing a code stage doing what you want)

Note that if the collections are big, a code stage might be preferable to a native BP solution due to higher speed and lesser memory consumption.

Happy coding!
---------------
Paul
Sweden

Re: Validate data in Excel from SAP

NafizZainal — Tue, 17 Jan 2023 04:01:57 GMT

Hi Paul,

Thanks for the ideas! I will try your ideas and update if anything.