Reading random Values in excel

Help Activities
, ,
1

Hi Team,

i am facing an issue with some of scraped datatables where the data is not in structed format of a column

as seen below the status of SE ID is randomly disbursed and i am not getting how to capture Terminated or Activated values of the SE ID

image

is it possible to get the values of that particular row… i am feeling to read each cell in the specific row and if it is Null i need to ignore if not i need to capture the value is there any optimal way for this issue? than what i think

2

Hi @devasaiprasad_K ,

Is this the expected result?
image

If so then please give this solution a try and let me know if it works out for you.

(From row In dt_data.AsEnumerable()
Let status = row.ItemArray.
	Skip(2).
	FirstOrDefault(Function(f) Not (IsNothing(f) OrElse String.IsNullOrEmpty(f.ToString)))
Let ra = row.ItemArray.Take(2).Append(status).ToArray()
Select dt_res.Rows.Add(ra)).CopyToDataTable()

CompressLastColumn.xaml (10.9 KB)

Kind Regards,
Ashwin A.K

3

i believe this is what i am expecting, will try and mark as Solution

4

hi @ashwin.ashok - if you dont mind can you pls explain what is the logic of the syntaxes, even though i am able to understand the syntaxes of skip and Take i am not able to frame it.

5

Hi @devasaiprasad_K ,

The status is conditionally acquired from each row. We first skip the first two columns since it will always contain values.
The FirstOrDefault fetches the first item that is not null or empty i.e., if it contains text like ‘Terminated’ or ‘Activated’.
The ra variable holds the required data for each row which is then written to the final output.

I’ve also written few articles to get you started on LINQ and you can check them out if you are interested.

Kind Regards,
Ashwin A.K

6

Hi Ashwin,

i am actually doing a table scrapping from desktop application and when i am doing scraping i am seeing table with different different formats, with some empty columns added in beginging or in middle, not able to understand the logic at backend.

i have attached sample output of scraping could you pls check once and let me know if we add a code for it…
DTTypes.xlsx (9.1 KB)

7

Hi @devasaiprasad_K ,

Asssuming that you are expecting three columns in the output, modify the logic and give this a try:

(From row In dt_data.AsEnumerable()
Let ra = row.ItemArray.Where(Function(w) Not(IsNothing(w) OrElse String.IsNullOrEmpty(w.ToString))).ToArray()
Select dt_res.Rows.Add(ra)).CopyToDataTable()

This works if there are only three items with values in a given row i.e., if there are 6 columns with 3 empty cells and 3 cells populated with data, this logic works.

CompressLastColumnv2.xaml (12.3 KB)

Kind Regards,
Ashwin A.K
|TheCodingTheory|

1 Like
8

learnt a lot, thank you so much… code is v accurate

9

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