How do I get unmatched data between an Excel and SQL data table in Blue Prism?











up vote
-1
down vote

favorite












sample data there is 1 unmatched record in excel which i need to get as my output.










share|improve this question




















  • 1




    Can you share more info? Do I understand it that you have two collections and you would like to get the ones that are in one table but not the other? What have you tried so far?
    – Marek Stejskal
    Nov 7 at 9:26










  • I need to get unmatched records from both collections, one source is Excel Data and other source is DB2OLEDB table data, both have the same columns but sometimes some columns data do not match to other, so i need to compare both sources data and send the unmacthed data in to excel sheet. added sample data image pls let me know if you need more details.
    – madhu
    Nov 8 at 19:32

















up vote
-1
down vote

favorite












sample data there is 1 unmatched record in excel which i need to get as my output.










share|improve this question




















  • 1




    Can you share more info? Do I understand it that you have two collections and you would like to get the ones that are in one table but not the other? What have you tried so far?
    – Marek Stejskal
    Nov 7 at 9:26










  • I need to get unmatched records from both collections, one source is Excel Data and other source is DB2OLEDB table data, both have the same columns but sometimes some columns data do not match to other, so i need to compare both sources data and send the unmacthed data in to excel sheet. added sample data image pls let me know if you need more details.
    – madhu
    Nov 8 at 19:32















up vote
-1
down vote

favorite









up vote
-1
down vote

favorite











sample data there is 1 unmatched record in excel which i need to get as my output.










share|improve this question















sample data there is 1 unmatched record in excel which i need to get as my output.







blueprism






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 8 at 19:46

























asked Nov 7 at 5:10









madhu

43




43








  • 1




    Can you share more info? Do I understand it that you have two collections and you would like to get the ones that are in one table but not the other? What have you tried so far?
    – Marek Stejskal
    Nov 7 at 9:26










  • I need to get unmatched records from both collections, one source is Excel Data and other source is DB2OLEDB table data, both have the same columns but sometimes some columns data do not match to other, so i need to compare both sources data and send the unmacthed data in to excel sheet. added sample data image pls let me know if you need more details.
    – madhu
    Nov 8 at 19:32
















  • 1




    Can you share more info? Do I understand it that you have two collections and you would like to get the ones that are in one table but not the other? What have you tried so far?
    – Marek Stejskal
    Nov 7 at 9:26










  • I need to get unmatched records from both collections, one source is Excel Data and other source is DB2OLEDB table data, both have the same columns but sometimes some columns data do not match to other, so i need to compare both sources data and send the unmacthed data in to excel sheet. added sample data image pls let me know if you need more details.
    – madhu
    Nov 8 at 19:32










1




1




Can you share more info? Do I understand it that you have two collections and you would like to get the ones that are in one table but not the other? What have you tried so far?
– Marek Stejskal
Nov 7 at 9:26




Can you share more info? Do I understand it that you have two collections and you would like to get the ones that are in one table but not the other? What have you tried so far?
– Marek Stejskal
Nov 7 at 9:26












I need to get unmatched records from both collections, one source is Excel Data and other source is DB2OLEDB table data, both have the same columns but sometimes some columns data do not match to other, so i need to compare both sources data and send the unmacthed data in to excel sheet. added sample data image pls let me know if you need more details.
– madhu
Nov 8 at 19:32






I need to get unmatched records from both collections, one source is Excel Data and other source is DB2OLEDB table data, both have the same columns but sometimes some columns data do not match to other, so i need to compare both sources data and send the unmacthed data in to excel sheet. added sample data image pls let me know if you need more details.
– madhu
Nov 8 at 19:32














1 Answer
1






active

oldest

votes

















up vote
0
down vote













There are two options I can think of from the top of my head:




  1. Simple way

  2. Coding way


Doing it the simple way would be looping through the larger of the two collections and using the Filter Collection from Utilities - Collection manipulation on the other collection to see if a row with the same values as in the primary collection is in the other collection. This approach will work well, but it will require an unnecessary amount of stages, more time to build and the performance will suffer in case you have large collections.



Doing it the coding way would mean using either VB.NET or C# and using the Except command. There are several examples even here on Stack Overflow (example). The drawback of this solution is that some background knowledge of .NET is required. You would need to add additional DLL references (System.Data.DataSetExtensions.dll and System.Core.dll) and namespaces (System.Linq).



The C# code then would be:



colOut = col1.AsEnumerable().Except(col2.AsEnumerable(), DataRowComparer.Default).CopyToDataTable();


...where col1 and col2 are input collections and colOut is an output collection.



Mind you that you that the code above will find you rows from col1 that are not present in col2, to find rows from col2 that are not present in col1 you will either have to switch the inputs and run it again or tweak the code some more.






share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53183872%2fhow-do-i-get-unmatched-data-between-an-excel-and-sql-data-table-in-blue-prism%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    0
    down vote













    There are two options I can think of from the top of my head:




    1. Simple way

    2. Coding way


    Doing it the simple way would be looping through the larger of the two collections and using the Filter Collection from Utilities - Collection manipulation on the other collection to see if a row with the same values as in the primary collection is in the other collection. This approach will work well, but it will require an unnecessary amount of stages, more time to build and the performance will suffer in case you have large collections.



    Doing it the coding way would mean using either VB.NET or C# and using the Except command. There are several examples even here on Stack Overflow (example). The drawback of this solution is that some background knowledge of .NET is required. You would need to add additional DLL references (System.Data.DataSetExtensions.dll and System.Core.dll) and namespaces (System.Linq).



    The C# code then would be:



    colOut = col1.AsEnumerable().Except(col2.AsEnumerable(), DataRowComparer.Default).CopyToDataTable();


    ...where col1 and col2 are input collections and colOut is an output collection.



    Mind you that you that the code above will find you rows from col1 that are not present in col2, to find rows from col2 that are not present in col1 you will either have to switch the inputs and run it again or tweak the code some more.






    share|improve this answer

























      up vote
      0
      down vote













      There are two options I can think of from the top of my head:




      1. Simple way

      2. Coding way


      Doing it the simple way would be looping through the larger of the two collections and using the Filter Collection from Utilities - Collection manipulation on the other collection to see if a row with the same values as in the primary collection is in the other collection. This approach will work well, but it will require an unnecessary amount of stages, more time to build and the performance will suffer in case you have large collections.



      Doing it the coding way would mean using either VB.NET or C# and using the Except command. There are several examples even here on Stack Overflow (example). The drawback of this solution is that some background knowledge of .NET is required. You would need to add additional DLL references (System.Data.DataSetExtensions.dll and System.Core.dll) and namespaces (System.Linq).



      The C# code then would be:



      colOut = col1.AsEnumerable().Except(col2.AsEnumerable(), DataRowComparer.Default).CopyToDataTable();


      ...where col1 and col2 are input collections and colOut is an output collection.



      Mind you that you that the code above will find you rows from col1 that are not present in col2, to find rows from col2 that are not present in col1 you will either have to switch the inputs and run it again or tweak the code some more.






      share|improve this answer























        up vote
        0
        down vote










        up vote
        0
        down vote









        There are two options I can think of from the top of my head:




        1. Simple way

        2. Coding way


        Doing it the simple way would be looping through the larger of the two collections and using the Filter Collection from Utilities - Collection manipulation on the other collection to see if a row with the same values as in the primary collection is in the other collection. This approach will work well, but it will require an unnecessary amount of stages, more time to build and the performance will suffer in case you have large collections.



        Doing it the coding way would mean using either VB.NET or C# and using the Except command. There are several examples even here on Stack Overflow (example). The drawback of this solution is that some background knowledge of .NET is required. You would need to add additional DLL references (System.Data.DataSetExtensions.dll and System.Core.dll) and namespaces (System.Linq).



        The C# code then would be:



        colOut = col1.AsEnumerable().Except(col2.AsEnumerable(), DataRowComparer.Default).CopyToDataTable();


        ...where col1 and col2 are input collections and colOut is an output collection.



        Mind you that you that the code above will find you rows from col1 that are not present in col2, to find rows from col2 that are not present in col1 you will either have to switch the inputs and run it again or tweak the code some more.






        share|improve this answer












        There are two options I can think of from the top of my head:




        1. Simple way

        2. Coding way


        Doing it the simple way would be looping through the larger of the two collections and using the Filter Collection from Utilities - Collection manipulation on the other collection to see if a row with the same values as in the primary collection is in the other collection. This approach will work well, but it will require an unnecessary amount of stages, more time to build and the performance will suffer in case you have large collections.



        Doing it the coding way would mean using either VB.NET or C# and using the Except command. There are several examples even here on Stack Overflow (example). The drawback of this solution is that some background knowledge of .NET is required. You would need to add additional DLL references (System.Data.DataSetExtensions.dll and System.Core.dll) and namespaces (System.Linq).



        The C# code then would be:



        colOut = col1.AsEnumerable().Except(col2.AsEnumerable(), DataRowComparer.Default).CopyToDataTable();


        ...where col1 and col2 are input collections and colOut is an output collection.



        Mind you that you that the code above will find you rows from col1 that are not present in col2, to find rows from col2 that are not present in col1 you will either have to switch the inputs and run it again or tweak the code some more.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 8 at 21:49









        Marek Stejskal

        1,6941221




        1,6941221






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53183872%2fhow-do-i-get-unmatched-data-between-an-excel-and-sql-data-table-in-blue-prism%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            這個網誌中的熱門文章

            Xamarin.form Move up view when keyboard appear

            Post-Redirect-Get with Spring WebFlux and Thymeleaf

            Anylogic : not able to use stopDelay()