How to write or append bulk data in existing workbook?

Help Activities
1

Hello team,

I’m downloading multiple xlsx files and there data in bulk like 10k rows are there in each file.and it’s appending the existing workbook but it’s taking lot of time around 45/50 minutes to append that all data ( Based on condition)

How it will append all data Fast

So how to handle that

Thanks

2

@suraj_gaikwad

  1. Did you try with Append Workbook activity?
  2. Appending data usign macro would be the next option
  3. Can use Excel as DB and can insert the data…even this would make the process little fast

cheers

3

@Anil_G

I tried with append and write range but both are taking times

Which data I’m appending and write that is filtered data

4

@suraj_gaikwad

Please try excel as DB or Macro

cheers

5

Please elaborate more so i cand understand

How I’m iterating the data shown in below image

IMG20231020230116
IMG202310202301161920×1440 266 KB

How data is appending and write range by using code

IMG20231020230126
IMG202310202301261920×1440 286 KB

@Anil_G

Thanks

6

It’s a lot of data. It will take time. You can’t make it go fast.

7

There no other solution

@postwick

8

Because it’s taking 40/50 minutes for each file

9

It looks like you’re looping through the data and writing to Excel over and over. You should get the data how you want it first, in the datatable, then just use one Write Range to write it all to Excel.

10

I have two suggestions, if you are appending the data one by one, like you read one input excel and append it to your output and move to next again read and append,
Continuous write will be slow process, can you try to keep the data in a datatable and when you are completed with reading all the input excel files finally write complete datatable to the excel

Can you try using a CSV file as temporary to write and append all the data, as CSV files work fast and easy for the device
You can convert this CSV to excel at end

11

Please refer above image of my workflow with code

Can you explain more

@saurabhB

12

@suraj_gaikwad

Instead of .Select(function(g) g.CopyToDataTable).ToArray

Use .SelectMany(function(g) g).CopyToDatatable

And write the datatable once and for all to excel…no need of loop

Cheers

13

IMG20231020234700
IMG202310202347001920×1440 131 KB

Showing this error

@Anil_G

14

@suraj_gaikwad

After g you did not close the bracket…please check

Cheers

15

I done with close bracket but error is still there

@Anil_G

16

@suraj_gaikwad

Please show the error…

Better to paste the full formula here

Cheers

17

Dt.AsEnumerable.GroupBy(Function(r) r(0).ToString).where(Function(grp) Not grp.Any(Function(a) a(“Match Type”).ToString.trim.Equals(“Confirmed”))).SelectMany(Function(g) g).CopyToDataTable

@Anil_G

18

@suraj_gaikwad

I dont see any error can you please confirm the error you are facing

Dt.AsEnumerable.GroupBy(Function(r) r(0).ToString).where(Function(grp) Not grp.Any(Function(a) a("Match Type").ToString.trim.Equals("Confirmed"))).SelectMany(Function(g) g).CopyToDataTable

image
image865×183 10.5 KB

cheers

19

IMG20231021155318
IMG202310211553181920×1440 121 KB

When I’m using selectmany this exception shows :sweat_smile:

@Anil_G

20

@suraj_gaikwad

In your assign the variable type of the left argument is array …please change it to datatable …that should solve the issue

Now no need to loop…directly use wrote range

Cheers