Validate data in Excel from SAP

[NafizZainal]

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

[PvD_SE]

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

[NafizZainal]

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

[PvD_SE]

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

[NafizZainal]

Hi Paul,

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