How extract important data from excel cell

[Raghu_km]

Hello Guys,

Good day!
I have data in column A and need to extract only “Amended” and “Corrected”. How to extract please suggest me.
for more details please refer the screen shots.

Thank you,
Raghu

[postwick (Paul)]

Read Range into a datatable. Filter Data Table on Expected = Amended OR Expected = Corrected.

DataTables and Excel Automation with Studio (v2020.10)

[Shiva_Nikhil]

@Raghu_km

dt.asenumerable.select(function(a) a(1).tostring<>"").tolist

gives you all the values in a list variable

if you want all the values to print in a single line use this

string.join(",",dt.asenumerable.select(function(a) a(1).tostring<>"").tolist)

[Raghu_km]

sorry it’s not working for me.

[Raghu_km]

Hi Paul,

Thanks for the help however it doesn’t work. I tried from my end could you please elaborate a bit more on this? I want to extract data from Column A. added a sample in this Excel.

[MFK (Mohd Faiz Khan)]

Hi, @Raghu_km,

You can use the Select Methond and Filter Activity too. I am sharing both solution, please have a look.

Select Method : dtOriginal.Select(“[ColumnName1] = ‘Value1’ AND [ColumnName2] = ‘Value2’”)

Corrected

[postwick (Paul)]

What does “it isn’t working” mean? You have to give us details. There isn’t much more to elaborate on with a simple Read Range and Filter. After the Filter you’ll have a datatable with just the rows that have Amended and Corrected. What do you want to do with that data?

[postwick (Paul)]

You have != selected but amended and corrected are what they want so it should be = with an Or not an And.

[AJ_Ask]

Hi @Raghu_km

Use excel file & read range to get the data from excel to datatable

then use filter & write datatable activity to filter the data & write it to excel in sheet2

Please find attached the sample for your ref:
ReadRange.zip (33.8 KB)

Hope this helps

[Shiva_Nikhil]

@Raghu_km

you can use this it will give only the column A data which are having Amended,Corrected

in a array format

arr_Filenames=dt.AsEnumerable.Where(Function(a) a(1).ToString.trim.Equals("Amended") or a(1).ToString.trim.Equals("Corrected")).Select(Function(a) a(0).ToString).ToArray

or

string.join(",",dt.AsEnumerable.Where(Function(a) a(1).ToString.trim.Equals("Amended") or a(1).ToString.trim.Equals("Corrected")).Select(Function(a) a(0).ToString).ToArray)

gives the output as of string type

[postwick (Paul)]

You should mark AJ’s reply as the solution, not your own reply.

[Raghu_km]

@AJ_Ask
Thanks for your time and providing the solution. finally its working.

[AJ_Ask]

@Raghu_km You just marked your own reply as the solution! I think you are new to the platform; you need to mark the solution to the person whose solution works for you. Cheers!"