How to remove the empty columns

[anjani_priya]

How to remove the empty columns. If the column data is empty under a header how to remove the entire column

[Baskar_Gurumoorthy]

hi @anjani_priya

refer the below thread.

I want to remove entire column if balank header or entire column blank then have to remove entire column without knowing the index

[image] if balank header or entire column blank then have to remove entire column

[lrtetala (Lakshman Reddy)]

Hi @anjani_priya

Please check this

How to remove columns from excel (old solutions unable to be opened sorry!)

@kidd Follow this to remove all empty columms For loop with dt.Columns Inside loop use filter datatable on dt and output into filtereddt and in condition use currentitem.ColumnName and select is empty Now use if condition filtereddt.Rowcount=0 On then side use remove data column and give the column name as currentitem.ColumnName and datatable as dt This will delete all columns in which all rows are empty Cheers

Regards,

[ptrobot]

Test with this expression in an Assign activity:

filteredDT = origDT.DefaultView.ToTable(False, origDT.Columns.Cast(Of DataColumn)().Where(Function(col1) origDT.AsEnumerable().Any(Function(row) Not String.IsNullOrEmpty(row(col1).ToString()))).Select(Function(col2) col2.ColumnName).ToArray)

origDT is your original data table and filteredDT is the new data table with only columns that have any value.

[anjani_priya]

getting the duplicate column names but columns are not removing

[anjani_priya]

getting the duplicate column names but columns are not removing.

[Ajay_Mishra (Ajay Mahendra Mishra)]

Hey @anjani_priya

Can you share the input and expected output file or dummy file.

Regards,
Ajay Mishra

[anjani_priya]

samplefile.xlsx (9.6 KB)
the empty columns index are dynamic

[Ajay_Mishra (Ajay Mahendra Mishra)]

@anjani_priya

Use below mentioned LinQ in Assign:

dt_Input = dt_Input.DefaultView.ToTable(False, dt_Input.Columns.Cast(Of DataColumn).Select(Function(x) x.ToString).Where(Function(column) dt_Input.AsEnumerable().Any(Function(row) Not String.IsNullOrEmpty(row(column).ToString.Trim))).ToArray)

Workflow Screenshot:

Attaching .xaml for your reference:
UpdateColumnValuesSequence.xaml (8.0 KB)

Hope above LinQ will resolve your issue!

Regards,
Ajay Mishra

[anjani_priya]

If the excel file is different then?

[anjani_priya]

samplefile.xlsx (9.7 KB)
small change
if the header is empty, whether it has values also, the column should remove

[Ajay_Mishra (Ajay Mahendra Mishra)]

@anjani_priya It will work with any excel!

Regards,
Ajay Miishra

[anjani_priya]

no but i need a change in this code, if the column header is empty even if the data is present in the empty header column it should remove the entire column. Once see the above attached excel.

[Ajay_Mishra (Ajay Mahendra Mishra)]

Okay @anjani_priya

then use below mentioned LinQ in Assign Activity:

dt_Input = dt_Input.DefaultView.ToTable(False, dt_Input.Columns.Cast(Of DataColumn).Select(Function(x) x.ToString).Where(Function(column) dt_Input.AsEnumerable().Any(Function(row) Not String.IsNullOrEmpty(row(column).ToString.Trim)) Andalso Not column.ToString.ToLower.Contains("column")).ToArray)

Assign:

Input Output screenshot for your reference:

Regards,
Ajay Mishra