Skip to main content
A
Anonymous
April 27, 2012
Answered

Comparing multiple datasources to excel

  • April 27, 2012
  • 1 reply
  • 1049 views

Hi,

I have two tables from different datasources and an excel file. The tables and the excel file have thousands of employee records in them.

I have to compare data from each table to records in the excel file. Initially, I was using Nested loops to compare the values, but, it is taking a lot of time to execute my code.

Please provide me a viable solution to my problem.

I really appreciate all the help and thanks in advance.

Vijayvijay77.

    This topic has been closed for replies.
    Correct answer Dan_Bracuk

    Hi Dan,

    Thanks for the reply. I used CfSpreadSheet to read the data from an excel file. Now, I want to detect and display  duplicate records from the same excel file.

    So, now I have a query object which contains the excel data without a database. How do I find Duplicates from a nondatabase query.

    I have tried query of queries, but it didn't work. And I cannot use Cfloop, since the excel file is too large.

       

    Please let me know, what do you think is the best possible solution. I really appreciate your help.

    VijayVijay77.


    Try something like this.  When you have your query, add a column to it.  Populate this column with the record numbers.  Then make a copy of your query.

    Now do a Q of Q where everything is the same except the record numbers.

    1 reply

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    April 28, 2012

    You'll have to show us an example of your code, describing what the intention is.

    A
    Anonymous
    May 1, 2012

    I have to produce duplicate values in an excel sheet having 2700 records in it. I could have used query of queries, but the excel file doesn't have any datasource .

    I converted the excel file to output as a table. Then I compared the excel columns with query from a datasource to produce the matches.

           

    But, how can I produce[output as a table or as a sheet in an excel file] Duplicates from the same excel file.

           

    Any advice or comments are highly appreciated.

           

    Thanks,

    Vijayvijay77.

    D
    Dan_Bracuk
    Inspiring
    May 1, 2012

    I've never actually done it, but the docs on cfspreadsheet imply that if you use action="read", you get a query object.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices