I need to solve Excel issue

[MD_Farhan1 (MD Farhan)]

Hi My excel file look like

but i need

How to do this pls support me

Thank You

[Darshan_Sable]

@MD_Farhan1

Read data from excel and store it in datatable

use filter datatable activity and remove rows where Quantity and rate is empty

[sharazkm32 (Sharaz K M)]

Follow below steps

1. Build one data table with only headers as Description, Quantity and rate and save to data table variable Dt_output

2. Open excel file and use read range activity to read the complete sheet

3. Loop through the range

4. Check if Quantity have value or not

5. If value available, Use Add Data Row Activity and add details to Dt_output {Description, Quantity, Rate}

6. Get the last row count of Dt_output

7. if quantity is blank - read the description and append to last row description of Dt_output

8. Next loop

At the end -Dt_output will have the required results. Save to another excel file

[yedukondaluaregala]

@MD_Farhan1

Attaching sample workflow for your reference.
DeleteBlankRows.zip (119.8 KB)

if it helps please mark post as solution!!
happy automation!!

[arivu96 (Arivazhagan A)]

Hi @MD_Farhan1 ,
Use below method.
dtoutput=dt.clone()

dtoutput=(From row In dt.AsEnumerable() Where Not row("Description").ToString.Trim.StartsWith("(") Let desc = row("Description").ToString.Trim Let subDesc = String.Join(",", dt.AsEnumerable().Where(Function(x) x("Description").ToString.Trim.StartsWith("(")).Select(Function(x) x("Description").ToString.Trim)) Select dtOutput.Rows.Add(desc & If(subDesc <> "", ", " & subDesc, ""), row("Quantity"), row("Rate"))).CopyToDataTable()

Regards,
Arivu

[MD_Farhan1 (MD Farhan)]

Hi,

This method to remove whole row but i mention clearly in screenshot.

Thank You

[MD_Farhan1 (MD Farhan)]

Hi,

This method to remove whole row but i mention clearly in screenshot. pls check another method.

Thank You

[MD_Farhan1 (MD Farhan)]

Hi,

how to use this pls explain me i am fresher.

Thank you

[MD_Farhan1 (MD Farhan)]

Hi,

If Possible to share workflow better to understanding me.

Thank you

[sharazkm32 (Sharaz K M)]

Kindly check the workflow after referring to my above comments. It is working fine and tested from my side

PLease understand the concepts as well. You can google each steps to understand the logic used here

BlankProcess.zip (130.6 KB)

[MD_Farhan1 (MD Farhan)]

Thank you for your Effort