Trying to get data from Excel under specific header name

Learn Academy Feedback
,
1

Hi All

Thanks in Advance

I want to fetch the data from Excel under specific header

Example : in sheet 1 have couple of headers and I have to get the data from under certain hader names in sheet have “silkboard” and “EC” header but I want to extract under silkboard hader data how can I extract

Additional info : if silkboard header is available in sheet2 then I need to fetch the data from sheet2 as well
Please find the attachment for the reference.

Screenshot_20190125-074740
Screenshot_20190125-0747401080×1920 223 KB

Thanks,
Naveen Ch

2

Hi @Naveen.Ch ,
If it is static range , you can use range in excel application scope.
You can use some conditional statement for headers to select.

3

Hi @lukas_krishnan

Thanks for the quick response
But that item is not in static and top of that I need to check in other sheet also if same header is there are not ,if it’s there means I need to fetch the data from that sheets as well

Best Regards,
Naveen

4

Hi @Naveen.Ch

Under each header the number of rows are static or not?

5

Hi @Chandru

Thank for the response

Header are static but the rows are not static it depends upon the content it will increase or decrease

6

@nadim.warsi
@acaciomelo
@vidyasagar

Can any one help me out to resolve above thread

7

Can any one help me to resolve this issue.

8

@vvaidya can you help me out

9

Hey - In case if you haven’t found the solution.Here’s the off the top of my head solution (there could be a better way):

Assuming dt as your datatable.

firstIndex = dt.Rows.IndexOf(dt.Select(“[Column0]=‘Silkboard’”).First)

lastIndex = dt.Rows.IndexOf(dt.Select(“[Column0] is null OR [Column0]=‘’”).First)

dtNew = dt.AsEnumerable.Skip(first+1).Take(last-first).CopyToDataTable

PS: Did not test.

2 Likes
10

Thanks for the quick response ,I will check let you know

11

Hi @vvaidya,

I have tried your solution but i am getting error like cannot find column [Column0]

1 2

3

12

Since your excel headers are not consistent, i think you should uncheck “Add Headers” in read range, then the default column names would be Column0,Column1…etc

Also I noticed that your Silkboard header is not always first, then you might have to make some changes interms of Index. Try something like below :

  1. first- dt.Rows.IndexOf(dt.Select(“[Column0]=‘Silkboard’”).First)

this should find the row index of first empty column0 after Silkboard row index.

  1. last- dt.Rows.IndexOf(dt.Select(“[Column0] is null OR [Column0]=‘’”).Where(Function(row) dt.Rows.IndexOf(row) > first).First)

  2. dtNew - dt.AsEnumerable.Skip(first+1).Take(last-first).CopyToDataTable

After this you should somehow make your 1st row in dtNew as header. Lazy way would be WriteRange and ReadRange back to dtNew with “Add Headers”.

13

Hi @vvaidya

Still i am facing some issue Cannot find column [‘bellandur’].
(please find .xaml file ,please look into it where i am doing the mistake)

Sample.zip (14.1 KB)

Best Regards,
Naveen Ch

14

Check below things:

Make sure your read range has ‘Add Headers’
unchecked.

Double check if “Bellandur” (and others) in Select statement has
straight quotes not curled quotes.

-
Yes

  • NO
15

Hi @vvaidya

Still i am facing same issue can you please look into my workflow once

image

16

Issue with quotes in 2 places. Try this Xaml :

HPPPPP.xaml (8.5 KB)

Output

image

1 Like
17

Hi @vvaidya

Thanks for the response and now I am able to see the output.
Is there any simply way to do this Process,can you please let me know it would be great help.

Best Regards
Naveen

18

Solved topic closes in 3 days, so I’m changing the thread as Unsolved so others can chime in to provide their ideas. Until then you can stick to this idea. Sounds good?

1 Like
19

Yup ,thanks @vvaidya

20

Hi @vvaidya

For 2 nd step when I am trying to enter manually i am not able to see any Autosuggestions after where condition.

Where(Function(row) dt.Rows.IndexOf(row) > first).First

I am trying to enter manually it is showing compiler error encountered processing expression

Best Regards,
Naveen Ch