Read excel which contains duplicate Columns and delete few required columns

Help Studio
1

Hi ,

We need to read the excel which contains duplicate Columns and delete the columns which contains the text "20250516 Forecast $ " (Except in Totals column)

Note : We shouldn’t delete the Totals column which contains 20250516 Forecast $ .
Attached the input file
Input.xlsx (269.3 KB)

Note : please pass variable for 20250516 Forecast $ as the date will be dynamic in this text

The image shows a spreadsheet with data about forecast quantities and forecast dollar amounts for various months, and includes a note cautioning not to delete columns with forecast dollar totals. (Captioned by AI)
The image shows a spreadsheet with data about forecast quantities and forecast dollar amounts for various months, and includes a note cautioning not to delete columns with forecast dollar totals. (Captioned by AI)1897×247 19.8 KB

2

Hi @Sathish_Kumar_S

First you need to remove the duplicate columns in order to read the input excel

And then use the linq

dtInput = dtInput.DefaultView.ToTable(False, dtInput.Columns.Cast(Of DataColumn)() _
    .Where(Function(col) col.ColumnName = "Totals" OrElse _
        Not dtInput.AsEnumerable().Any(Function(row) row(col).ToString.Contains("20250516 Forecast $"))) _
    .Select(Function(col) col.ColumnName).ToArray())

Hope this helps!

3

Read starting from the first data row and turn off headers. Then rename the columns and use Filter Data Table to keep just the columns you want.