Comparing multiple datasources to excel

[Guest]

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.

[BKBK]

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

[Guest]

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.

[Dan_Bracuk]

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

[Guest]

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.

[Dan_Bracuk · Accepted solution]

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.

[Guest]

That Worked, Thanks Dan.I really appreciate your time and advice.

Vijayvijay77.