How to write or append bulk data in existing workbook?

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

@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

@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

@suraj_gaikwad

Please try excel as DB or Macro

cheers

Please elaborate more so i cand understand

How I’m iterating the data shown in below image

How data is appending and write range by using code

@Anil_G

Thanks

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

There no other solution

@postwick

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

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.

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

Please refer above image of my workflow with code

Can you explain more

@saurabhB

@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

Showing this error

@Anil_G

@suraj_gaikwad

After g you did not close the bracket…please check

Cheers

I done with close bracket but error is still there

@Anil_G

@suraj_gaikwad

Please show the error…

Better to paste the full formula here

Cheers

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

@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

cheers

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

@Anil_G

@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