Excel insert column activity / slow update

Hello,
i have some issues with below workflow. basically I am just adding a column in an excel file with the date of today for each row in the file (number of rows is approximately 6000). the flow is working but it’s very slow to get executed (it takes like more then 10 minutes). do you know a faster / efficient way to do the same activity?
thanks

@giorgio.tabarellidefatis,

  1. Enter date using Write Cell activity in first cell
  2. Fill Range activity to fill the same value as first cell in all the remaining rows.

@giorgio.tabarellidefatis

better to insert the column and then instead of writing each row with for each…use a write cell activity to write in first row and then use auto fill range

alternately create a datatable with one column and fill the dates for how many ever rows you need and then use write datatable to excel with range as first cell value

better to go with first approach

cheers

Above solutions are the best. In case if you have any challenge in finding the solution within excel - You can try data table method

Approach 3: Use Linq for DataTable Manipulation

  1. Read the Excel File into a DataTable:
  • Use the “Read Range” activity to read the entire Excel file into a DataTable.
  1. Add a New Column to the DataTable:
  • Use the “Add Data Column” activity to add a new column to the DataTable.
  1. Use Linq to Update the DataTable:
  • Use an “Assign” activity to update the DataTable with today’s date using Linq.
dt.AsEnumerable().ToList().ForEach(Sub(row) row("Date") = DateTime.Now.ToString("MM/dd/yyyy"))
  1. Write the Updated DataTable Back to Excel:
  • Use the “Write Range” activity to write the updated DataTable back to the Excel file.

Hello @giorgio.tabarellidefatis,

Follow these steps:

  1. Use ‘Read Range’ to load the sheet into a DataTable (‘dtData’).
  2. Use ‘Add Data Column’ to create a new column (“Today’s Date”).
  3. Use ‘For Each Row’ on ‘dtData’ to populate the new column with ‘Now.ToString(“yyyy-MM-dd”)’.
  4. Use ‘Write Range’ (without Add Headers if needed) to write the updated DataTable back to Excel.

This method processes all data in memory, significantly reducing execution time.

thank you all,
attached screenshot with attached adjusted code, for record.
in the “Fill Range” activitz I used following expression to determine the number of rows, since it is a variable number: Excel.Sheet(“Sheet1”).Range(“BH3:BH”+(ExcelData.RowCount+1).ToString)

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.