I have 18000 record in excel

Help
, , ,
1

How I can get data in data table and how to use for each loop can any one suggest me…?

2

readrange

Use Read range activity to get the data from excel to DataTabel and use ForEach Row activity to iterate it.

3

Hi @Aditya10989

1.Use Excel application scope activity pass the path where you can save excel file.
2.next use Read Range activity and then create output data table variable.
3.next use For Each Row Activity there you can pass data table variable.

Regards,
Kommi Jeevan.

4

not able to got in data in data table take to much time. right now I am executing application from last 5 mint its showing in process but not got data

5

@Aditya10989

It should work and won’t take much time to read those records. And also 18000 records is not that much and we are reading the files which has more than 50K records.

May I know what is the RAM size in that machine ?

6

Hi
We can use EXCEL APPLICATION SCOPE and pass the filepath of the excel file as input especially for the files which have large number of records
As excel is limited to 1,048,576 rows by 16,384 columns
And this can be handled very easily and in faster way as well with EXCEL ACTIVITIES
Only if we don’t have Excel application installed then we can use Workbook activities which has the same set of activities as we have in excel

And it won’t take much time for 18k records and it won’t be more that a min

And once after using excel application scope passing the file path use READ RANGE activity inside the scope and get the output with a variable of type datatable named dt

—now use FOR EACH ROW activity and pass the variable dt as input

For more details on excel limitation in its row count

Cheers @Aditya10989

7

8 GB some time I am able to fetch the data and some time its showing executing in read range but data not fetch still showing executing after 10 mint.there is no change in data while executing and not executed

Untitled
Untitled1440×900 101 KB

8

@Aditya10989,

You can read part by part, means you can read 1000 rows then process it. After that next 1000 rows and process it. Will it be okay to do like that?

9

@sarathi125 thanks for reply yes we can also do that but how…?

10

@Aditya10989,

Check this post

How to delete a row in excel using invoke method

11

The read range activity runs SLOW if the PreserveFormat option is checked.

This is because it reads each cell one by one to also read the formatting.

I was reading excel files of 15-30 rows with 7 columns each, and it took about 100 seconds to read each file with PreserveFormat = True. With PreserveFormat = False it took 0.08 seconds to read a file!

That said, without PreserveFormat, dates show up as the raw ticker, not a pretty formatted date, so you will have to do some work on the data table if you really want the excel dates to be .net dates.

– M

12

I saw PreserveFormat is already unchecked still facing issue

13

Anybody has a solution for this? I am facing the same issue.

14

Try to check the excel file size, there might be an auto fill (formula on blank rows) or a macro formula on it that causes the file having a large size and also causes the longer process time on Read Range. If there’s an auto fill, try to copy the rows you want to process on a new excel file.