How to copy a range after particular data in excel

Hi,

I have an excel file containing data which does not have headers, i want to copy data only after 8 rows.

Please suggest how can we do that?

Hi @nsharma

You can try something like this

dt = dt.AsEnumerable().Where(function(r) dt.Rows.IndexOf(r)>7).CopyToDataTable 

Or even this

dt = (From p In dt.Select()
Where dt.Rows.IndexOf(p)>7
Select p).CopyToDataTable

This will give you all the rows having index greater the 7

Thanks,
Prankur

2 Likes

Thanks it is working,
But can you please explain it why did you use “AsEnumerable()” in first one.
and in second one how did you use “p”?

Sorry if i am asking poor question.

Whenever we want to iterate on data or more specifically u want to loop on the data at that time use AsEnumerable

1 Like

Hi @nsharma

No problem you should ask everything :slight_smile:

Actually LINQ work on data sources and implements IEnumerable and IQueryable interfaces. However DataTable does not implement anything that is why you must use AsEnumerable extension method to work them as data sources.

p there in the query is a range variable

Thanks,
Prankur

2 Likes

Thanks,
The excel file contains headers also can we make a code to fetch data after the headers only by passing the header name, instread of passing the row index no?

Yes you can, something like this

dt.Select("[ColumnName]="Vaule"").CopyToDataTable

Thanks,
Prankur

But the column names will be in 3rd row and then there will be an empty row and then the main data start which i need.
For your reference i will share the demo excel file:

.abc.XLS (1.5 KB)

In this file headers are in 6th row and the data which i need to fetch is in 8th row

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