What is the fastest and stable solution for writing datatable to excel

My goal is to write Excel A data to Excel B as fast as possible, stable as well.

Below is my workflow
1.) Read Range Workbook (Convert Excel A to Data Table)
2.) For Each Row in Data Table
inside the loop
2.1) Multiple Assign (e.g. Name = CurrentRow.Item(“Name”).ToString, Age = CurrentRow.Item(“Age”).ToString)
2.2) Write Cell Workbook * 10 (e.g. write Name to Cell “B4”, write Age to Cell “C4”)

This is my design, it took few minutes to Write 100 row.

I tried parallel, but seems like it not works for workbook activities
and parallel for excel activities, seems doesn’t run fast as well

Please suggest a faster way to do it

Thanks!

@mlellison

You can use Write Range activity to write directly datatable to Excel
But for calculation you are saying that you need LinQ to manipulate the results

Can you share the sample input and output you required

Thanks,
Srini

1 Like

Hi

Few queries

  1. Does the data to be filtered before pasting to second excel
  2. are you not doing for entire data of sheet
  3. does this destination of cells are dynamic

If you can provide sample source data and expected output data will help more in understanding

Regards

1 Like

Hi @mlellison

If you are well-versed in excel vba, you can write a vba code which will be easily processed by UiPath in more faster way.

Thanks!!

1 Like

@mlellison

Actually you can directly use copy sheet activity if there are no manipulations…

If there are any manipulations as well then can write excel formula in first cell using write cell activity and then use autofill range activity to fill the formula till down to do calculations

Cheers

1 Like

Hi All,

Thanks for the replies.

Above is the Sample excel, needa write Excel A data to Excel B

@Srini84
@LAKSHMI_NARAYANA_PEMMASAN
Thanks
1.) Data need not to be filtered
2.) Doing for every row, but not every column
3.) destination of cells are not dynamic

@Nitya1
Thanks for the advice, in this case, i want a fast way without vba/ invoke code

@Anil_G
Thanks, I am using write excel formula, i am looking for a faster way to do so if possible

check this thread this is simple way

Here you can utilize copy/paste activity to directly one column at a time

Regards

1 Like

@mlellison

You are using write excel formula and data in a loop…

Instead use copy sheet or range which is a bulk activity

And also for formula you areagain using in loop…instead use auto fill range where you will use write formula on the first cell and then use auto fill range activity to fill the formula in all cells

Hope this helps

Cheers

1 Like

@
ExcelWrite.xaml (12.4 KB)
mlellison : Please find attached workflow for your reference as per your requirement:

1 Like

Hi @mlellison ,

The fastest solution would be to read the data in a Datatable using Read Range Activity,
Loop through all the data,
edit the data in the datatable itself and write it in the separate excel file B using write range after exiting the loop.

If you’re using Write Cell one by one, that’ll definitely take long time.
If you’re using formula’s, write in row 1, use auto fill range and provide the range in the properties.

That’ll work fast and would be stable as well.

Regards,
Aditya

1 Like

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