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

Help Studio
, ,
1

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!

2

@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
3

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
4

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
5

@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
6

Hi All,

Thanks for the replies.

Excel A B
Excel A B910×457 21.7 KB

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

7

check this thread this is simple way

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

Regards

1 Like
8

@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
9

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

1 Like
10

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
11

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