How can I extract data in between without specifying the exact cell position?

Krystal · March 10, 2022, 4:22pm

Hi! How can I extract the rows between Class A and standard without specifying the exact cell position then write it to another excel sheet? Next, repeat the process again for Class B. Thank You.

ashwin.ashok · March 10, 2022, 4:24pm

Hi @Krystal ,

Would you be so kind as to make this sample template available to us so that we can run some tests and come up with an end-to-end resolution?

Kind Regards,
Ashwin A.K

Krystal · March 10, 2022, 4:30pm

file.xlsx (9.4 KB)

ashwin.ashok · March 10, 2022, 4:49pm

Hi @Krystal ,

Is this the expected output?

SplitExcelDataFromSingleSheet.xaml (11.8 KB)

Kind Regards,
Ashwin A.K

Krystal · March 10, 2022, 5:22pm

Thanks a lot. This is what I want. But instead of writing it one by one, is it possible to do it in a faster way because I have a lot more data afterwards?

And I have another question, if I only want to extract columns A (if column A is empty then need to extract column B)and C further to a new sheet, how to get this done?

Below is the expected output.

Palaniyappan · March 10, 2022, 5:33pm

Hi

Hope the below steps would help you resolve this

Use a excel application scope and pass filepath as input and inside the scope use a read range activity and get the output as dt
Then use a Build datatable activity and create two columns named “Name” and “Value” and get the output as Finaldt
Use a clear datatable activity and pass Finaldt as input
Then use a For each row activity and pass dt as input
Inside the loop use a If condition like this

NOT String.IsNullOrEmpty(CurrentRow(0).ToString.Trim)

If true it goes to then block where use a ADD DATAROW activity and mention datatable as Finaldt and array row as {Currentrow(0).ToString, CurrentRow(2).ToString}

If false which means first column has null value then it goes to else block where use a add Datarow activity like this
{Currentrow(1).ToString, CurrentRow(2).ToString}