Hi all, I have an excel file which I need to update. Basically there are 3 columns which are:
MonthYear, Day, SalesData
Based on today’s date, I need to find the row index so that I can write the Sales Data into the correct row. In order to do this, I need to check two column which are MonthYear and Day. I saw there is Find/Replace function in the Studio but it only works for a single column. The reason why I don’t want to read the file and filter it by using Filter Datatable is because the number of rows is huge and it may impact the performance of the machine.
For example, let’s assume your sheet is as the following.
Then, the following expression will return index of datatable. Please note that we need to offset +2 to get excel row index because there is header and datatable 0 based index.
As you can see in the file, there are 2 sample data for 22nd of August and September in Input File sheet. Based on that date, I want to find the correct row in Output File sheet based on column MonthYear and Day and write the into the SalesData column.
Thank you for the sample code. If we go with your way, I will need to read the whole excel file which can be quite risky as the number of rows there is huge and may causes some performance issue on the machine. Would 10k rows will be a problem if we go with your solution?
Around 10k rows is no problem to handle. Can you try the following sample?
Compatibility of this sample project is Windows( .net6) to enable to handle huge data just to be sure. However, it will work even if compatibility is Windows-Legacy, I think.