Deleting first two rows of a Data Table


#1

Hi,
I need a kind help to remove the first two rows of an Excel file.
Here I attach the Excel file that I’m analyzing.
Lista_targhe.xlsx (7.9 KB)
I tried to remove the first 2 rows of the DataTable generating by the Read Range, but it deletes the first and the third.



I attach here the file Excel I obtain.
Accesso a Quotti n 0.xlsx (8.4 KB)

Can you help me in arranging the problem?
Thank you so much.
Camilla.


#2

Hi,
Can you check this sample . it helps you.

File : DeletingfirsttworowsofaDataTable.zip (13.3 KB)

Regrads
Balamurugan.S


Issue in Invoke Code-Example
#3

Hi,
This sample is by Invoke Code activity.

File : DeletingfirsttworowsofaDataTableInvokeCode.zip (18.3 KB)

Regards
Balamurugan.S


Invoke code knowledge base
#4

@CamiCat

Try this

Datatable VAriable=DataTable_Variable.AsEnumerable().Skip(2).Take(DTVar.Rows.Count-2).CopyToDataTable

DTVar is Your DataTable Variable.

Regards,
Mahesh


#5

Thank you so much @balupad14.
It hepled me.
Regards,
Camilla.


#6

Nice… :slight_smile:


#7

Thank you so much.
Can I overwrite the old datatable or it it compulsory to create a new one?
Thank you @MAHESH1


#8

@CamiCat

You can overwrite the old datatable.

Regards,
Mahesh


#9

This is what I would use except you don’t need the .Take() unless you only want to take a certain number of rows. In your case, you want the remaining table, so you can leave it off, like this:

DataTable_Variable.AsEnumerable.Skip(2).CopyToDataTable

To go even further you can add a filter to it so you do two things at once…

DataTable_Variable.AsEnumerable.Skip(2).Where(Function(r) r(0).ToString.Trim<>"" ).CopyToDataTable

Regards.


#10

Thank you so much @ClaytonM.
It works only without the part of code Take(DTVar.Rows.Count-2) because I only need to take the table without the first two rows and overwrite it to old one.
Thank you for your kind help.
Camilla


#11

I will also mention another tip on this where you can use the Index of a row as the number of rows to skip. This will be useful if there is a chance that “2” could change in the future or if you are manipulating spreadsheets on multiple customers where there could be differences.

DataTable_Variable.AsEnumerable.Skip( DataTable_Variable.Rows.IndexOf( DataTable_Variable.AsEnumerable.Where(Function(r) r(0).ToString.Trim.Equals(header1) ).ToArray(0) ) ).CopyToDataTable

Basically, here I am using .Where to find the row that has a key word or header name, then placing that row in .Rows.IndexOf() to find the index of where that row starts at. I can then use that number in the .Skip(), instead of hardcoding the number 2.

This is not always required but useful to know.

Regards.


#12

Thank you @ClaytonM.
Your advice will be useful for further projects.
Regards,
Camilla