Read excel file with headings and remove some of the columns

[cristian_ivanoff]

Hi all!
I’m importing some excels in a loop. The excels are supposed to have the exact same headings but sometimes there are four initial columns (A to D) that shouldnt be there - these four columns always have the same headings. After columnd D the excel is as it should be.

I would like to do a check the data table something like this:

• IF (Column A = “Heading1” and Column B = “Heading2” and Column C = “Heading3” and Column D = “Heading4”)
  Then remove column A, B, C, and D
  Else exit;
  This is the current loop:
  
  

Any suggestions?
Br
Cris

[arivu96 (Arivazhagan A)]

Hi @cristian_ivanoff,

use the below code in the if condition
DataTableName.Columns.Contains("YourColumnName")
if returns true
use Delete Column activity to delete the column from the data table.
do the same thing for rest of other columns

Regards,
Arivu

[cristian_ivanoff]

Great! Thanks!
The delete column needs “Table name”. Is there a passaround for this? The excel doesnt have any table names when I do the read range.

[arivu96 (Arivazhagan A)]

Hi @cristian_ivanoff,

so for this purpose use read range activity to get the datatable.
after that check in if condition delete the column. then use write range activity to write the data into excel file.

Regards,
Arivu

[MAHESH1 (MAHESHKUMAR JV)]

@cristian_ivanoff @arivu96

You can also try like this,
Create a list of column name whose values you want to delete from datatable, Let us take ListA

Use this Query
List B=(From p in dt.Columns.CastOf(System.Data.DataColumns)
where not ListA.Contains(p.ToString)
Select p).ToList()

Now you can get the datatable
DataTable_Variable.AsEnumerable.DefaultView.ToTable(False,List B)

This will return the datatable without columns which are there in ListA
If you want Distinct values make it as True

Regards,
Mahesh

[cristian_ivanoff]

Thanks for your help. I use a “Read Range” activity but when I want to use the “Delete Columns” activity it requires a “Table Name”. See screen shot attached.

[cristian_ivanoff]

Interesting. I will try this as well…

[cristian_ivanoff]

I did this: