Excel to Datatable taking soo much time while updating each cell of the Datatable

excel
uiautomation
studio

#1

HI,

I have an excel file which has more than 500 rows or more, In excel scope I have stored the excel data into data table. Where I have to compare Column A with column B & C on the basis of condition if result comes in matches then I have to build another data table and start working on the rest of the code. But the problem while matching the condition it looks like Datatable is taking time to update column of each cell.

Is there any fatsest way to update datatable so that I can save time.

Regards
Sid


#2

Hi @sid_crpyt,

Check if the propertie “Autosave” of excel scope is enabled. If it is, uncheck the propertie and put a “save workflow” at the end of the scope!

Hope it works. Regards, :mage:
Pablo


#3

@Pablo_Sanchez, Thanks for replying, issue is still same bot is taking so much time


#4

Hi,

If you filtering your datatable1 with some condition and generating/updating datatable2, then you can try to use Linq filter in that.


#5

@aksh1yadav can you help out ?


#6

Hi there @sid_crpyt,
You can try something similar to:
dtNewDataTable = dtOriginalDataTable.AsEnumerable.Where(Function (row) row.Item(“A”).ToString = row.Item(“B”).ToString OR row.Item(“A”).ToString = row.Item(“C”).ToString).CopyToDataTable

As an example, the above will quickly filter a DataTable down to only those rows wherein:

  • Column A = Column B

Or

  • Column A = Column C

Thanks in advance,
Josh


#7

Try with another properties like “Hidden” … a 500row DT should be fast