Excel data after 50,000 columns is not updating

Help Activities
, ,
1

Hi all,

I have a Bad scenario,

I am getting the text file as input and I am converting the data to Excel using the Generate data table, and updating to the Excel.

The output of the generate data table is more than 90,000 but after 50,000 the data ta not displayin in the Excel and not updating to the Excel.

I am using the Excel application scope and Write range for updating,

Did any one face this issue,

Kindly help me on this…

Thans

2

@srinusoft37

Are you getting any kind of error?
Also, can you place a message and check the datatable how many rows count you are getting

Thanks,
Srini

3

@srinusoft37

Official limits

image
image1043×533 30 KB

cheers

4

No this is not throwing any error, the count is getting same but while updating only we are facing this error.

The Excel is not able to updating after 50,000 records.

Thanks
Sreeni

5

Yes, but my data is less than 1,00,00 only

6

@srinusoft37

You mentioned 50000 columns…hence given the limits…is it possible to provide the file?

Did you try using excel workbook activities?These are better for larger datasets

cheers

7

While using Excel work book activities, the Process is stoping due to unexpected error and the data is not updating

Thanks
Srini

8

@srinusoft37

Then try this…

Create a loop inside the excel application scope and break the data in chunks of 20000 and use append range

to get data in chunks of 20000 use dt.AsEnumerable.Skip(Cint(20000*counter)).Take(20000)

Create a counter variable and assign it to the index variable in for loop… in the for loop in Argument use Enumerable.Range(0,Cint(Dt.Rowcount/20000)+1).ToArray

use append range with the above given chunks formula

Hope this helps

cheers

9

Thanks for the code,

Could you please help me with the Xmal please, so I will replace in my Code,

I am getting issue with the variable creations in your code.

That helps me a lot for solving the issue soon.

Thanks
Sreeni

10

@srinusoft37

This is how it looks

image
image383×612 20.8 KB

Check the listUiElement.xaml
PepProcess - Copy (2).zip (8.0 KB)

cheers

11

Thanks a lot @Anil_G

I will try from my end

Thanks
Sreeni

1 Like
12

Hi Anil_G,

I tried with the 98147 rows of data but it is copying only 20,000 rows and the remaining data is not copied into respective Sheet, I followed the same code as you shared.

Do I need to change anything else.

Kindly assist.

Thanks
Sreeni

13

@srinusoft37

Are you copying into different sheets?

I am actually trying to append everything to same sheet

Can you show what you did

cheers

14

I did as it is what you shared in the code, in the for if condition,
In then section I have given write range and for else I am using the append range,

I just change the data table name of your code Anil.

Thanks
Sreeni

15

@srinusoft37

run it in debug mode and check if it is looping the data required number of times…keep a break point before loop…and then use step into and check

Cheers