Spliting excel data

Hi, I have this excel files.
image

Now I want to split the row data and want to save in diff - 2 excel files.
Like. In this 5 excel will be creating,
Ex. Excel1 contain the Headers with the A2 complete row data,
Excel2 contains the Headers with A2 complete row data.
I want to save this data into diff 2 excel files.

I created the logic but confused while writing the data of single row. since I am storing this complete data in a dt variable. but confused how to write only the single - 2 row data with the headers.

@Palaniyappan @HareeshMR

1 Like

Hi, Get this error can you help, is there anything wrong in the assigning part.

@Palaniyappan

Hope these steps would help you resolve this
—use a excel application scope and pass the file path as input
—inside the scope use read range and get the output with a variable of type datatable named dt
—then use a build datatable activity with similar columns as per in excel and get the output with a variable of type datatable named Finaldt
—now use a for each row loop and pass the variable dt as input
—inside the loop use a add data row activity and in the property panel mention the datatable as Finaldt and in the array row mention as row.ItemArray
—then use write range activity from workbook activities where pass the input and in sheetname alone mention as dt.Rows.Indexof(row).ToString
So that sheetname will be 1, 2, …till number of records and mention the datatable as Finaldt and ensure that ADD HEADERS is enabled in the property
—now next to this write range use a clear datatable activity and mention the datatable as Finaldt
—next while still being inside the loop use a Kill Process activity and mention the process name as “EXCEL”

That’s all you are done
Cheers @balkishan

Please reply to this @Palaniyappan
I am assigning the dynamic range.

And Thanks for your response. :slight_smile:

1 Like

rowcount.ToString
Mention like that buddy
Cheers @balkishan

Is that working buddy
Cheers @balkishan

I am trying to implement bro, will let you know

1 Like

I don’t want to create a Single excel file and then create 1,2,3, worksheets inside it.

I want to create single - 2 excel file for each of the record.


Please see this bro.

Kindly elaborate this pls
Cheers @balkishan

Hey @balkishan

So you want the same all rows data in both excel with same header or what?

Regards…!!
Aksh

Same steps till mentioned then from there on
Use a excel application scope and mention the filepath like this
“Yourfolderpathwithoutfileextension”+Now.Tostring(“hh_mm_ss”)+”.xlsx”

Now use a write range activity and mention the range as “” and sheetname leave it and in datatble mention as Finaldt

—again use another excel application and mention the same last two steps which will create two new files with those records and make sure that Add Headers is enabled in write range

—then at last use a clear datatable activity and mention as Finaldt while we are still inside the loop

Cheers @balkishan

1 Like

Hey @balkishan
you can take below below approach :slight_smile:

image
dt.rows = I used it to get total records count so i will write that many excel with that particular row

New Datatable = i am initializing new temp datatable on each iteration.

Dt.clone - will clone the main datatable header only.

Add data row - will add iterated row in new temp datatable

why i did not use for each - well you can use that as well. no issues just you have to remove .Rows if you are going to use For Each Row Activity . It is just i used Generic for each because i am much comfortable with it and Addictive :stuck_out_tongue:

Reference - BalkishanSample.xaml (8.6 KB) (only use if not able to implement based on flow mentioned in image :slight_smile:)

It is much simpler and useful :wink:

Regards…!!
Aksh

2 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.