Solved

I need to compare two excel file, more than 40000rows. What would be the fastest way?

  • 23 November 2023
  • 5 replies
  • 215 views

Userlevel 3
Badge +10

I used ExcelAdvanced: find, get single cell, set cell commands.

38000 rows were compared in 17hours.. then i stopped the bot.

What soluiton would be the best?

icon

Best answer by Tamil Arasu10 23 November 2023, 09:17

View original

5 replies

Userlevel 6
Badge +15

Hi @Semih 3318 ,

Could you please compare the Excel files using the Database Method which helps to save the lot of time.

Treat the Excel file as a database and perform the comparison.

 
Userlevel 3
Badge +10

Hi @Tamil Arasu10 

Thank you, i havent used this before. I will try and see the result. Do you have any guess about how much time would it take to finish if it can do it in 18 hours with excel commands? would it get too much faster?

Also one question, using excel basic commands would do it faster than excel advanced but not as fast as database, right?

Userlevel 6
Badge +15

how much time would it take to finish if it can do it in 18 hours with excel commands? would it get too much faster?

I'm sure that using Excel as a database is faster. To gauge the timing, let's compare both processes. For example, if handling 100 records takes 5 minutes with Excel commands and the same 100 records take 3 minutes elsewhere, then we have saved 2 minutes.

using excel basic commands would do it faster than excel advanced but not as fast as database, right?

Using basic Excel commands can help reduce processing time compared to the advanced package, although it may not be faster than a database. However, please give it a try

Userlevel 3
Badge +10

@Tamil Arasu10  okay, thank you for your help.

Userlevel 2
Badge +8

Late reply here: I would get the data out of excel and into an AA table if you are running multiple “find” commands. Using excel as a DB still means you would execute multiple SQL commands against a large data set to find and then update the row.

  1. Put the data into a table
  2. Loop the data datable
  3. Inside the loop put the row of data into a record variable
  4. Inside the loop create if statements to only update rows under certain conditions (the “find’  commands you are using
  5. Update the appropriate row in the data table when the data in the record variable meets the criteria. Note you will also need a variable to keep track of what row you are on as updating the record variable does NOT update the table. You must update the table cell for the specific row using a data table command

With this approach you only loop the data ONCE and loops in AA table are very fast. I wouldn’t be surprised if you cut your run time by 80-90% on this piece.

Reply