Need to copy one of excel sheet data into another new excel

Help Activities
, ,
1

Hi Team,

I have an input excel with multiple sheets.

i need to read one of the excel sheet data which contains nearly 2 Lakhs rows.

It is taking so much time to read and write in another excel

is their any solution to complete it fast

Thanks
Likitha

2

Hello @vinjam_likitha

  1. Use the “Read Range” activity with appropriate settings:

    • Specify the range or limit the number of rows to be read.
    • If applicable, turn off “Add Headers” if the Excel file doesn’t contain headers.
    • Store the output in a DataTable variable.

  2. Process the data in the DataTable:

    • Perform necessary operations on the DataTable (e.g., filtering, transformations, calculations).

  3. Use the “Write Range” activity to write the data back to a new Excel file:

    • Specify the starting cell or range.
    • Use the DataTable variable containing the processed data as the input.

Thanks & Cheers!!!

3

Hi @vinjam_likitha

Use invoke vba activity instead of reading and writing the data in the excel.

image
image1170×1031 39.4 KB

New Text Document.txt (728 Bytes)

Hope it helps!!

4

Hi @vinjam_likitha

To handle with larger data

  1. Use Read Range activity with the “Preserve Format” option unchecked this will mprove the performance.
  2. Use the Workbook activities (like Excel Application Scope, Read Range, Write Range) instead of the Excel activities. This will have faster and better performance for handling large amounts of data.
  3. If possible, split the data processing into smaller chunks and use the Parallel activity to process data this will reduce the time.
  4. If your process involves extensive data manipulation, you can improve performance by disabling screen updating and calculations during the execution of the process.
  5. If you only need to work with a subset of the data, consider filtering the data using the Filter Table activity before processing it.
5

@pravallikapaluri My input excel file sheet name is Raw data from this sheet i need to extract >2 lakh data

and copy the same data in new file Demo soda raw data.xlsx

can you please help with with script

Thank you

6

HI @vinjam_likitha

Can you Give the input Data and expected output

7

@vinjam_likitha 2 lakh data means 2 lakhs rows am in right

8

@Dinesh_Guptil

yes. i cannot provide input file its confidential

Thanks

9

Hi @vinjam_likitha
1.Read The Excel Sheet using Read Range workbook
2. Demo DT = DT.clone
3.Demo DT = DT.AsEnumerable.Take(200000).CopyToDataTable
4. Use Write Range Workbook to write into new excel
It Helps!

10

while writing the data in new excel it is throwing array index out of bound exception

11

@vinjam_likitha

image
image746×311 10.8 KB

12

@vinjam_likitha
New Text Document.txt (1.9 KB)

Hope it helps!!

13

Hi if you just want to copy data into new excel sheet of new excel you can use Copy Sheet Activity in excel application scope
image

Copy Sheet.zip (126.6 KB)

14

@pravallikapaluri

Invoke VBA i didnt find that activity.

invoke vbscript activity i am getting

image

which parameters i need to pass