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

Help Studio
, ,
1

image
image763×385 6.91 KB

if balank header or entire column blank then have to remove entire column

2

Hi @shaik.muktharvalli1 ,
You can try
-Read range excel file to get data table
-Filter data table to get new data table not column blank and header blank
Regards,
LNV

1 Like
3

Hi @Nguyen_Van_Luong1

Can you make sample workflow for me please

thanks
Shaik Muktharvalli

4

Hey @shaik.muktharvalli1 , When you read a datatable from excel and the column headers are empty, then the column headers are converted to Column1, Column2 and so on.

So to remove those, columns you can use

image
image522×612 17.6 KB

Note:- When reading from excel AddHeaders should be enabled

5

Hi

Can you try the following expression?

dt = dt.DefaultView.ToTable(False,dt.Columns.Cast(Of DataColumn).Select(Function(dc) dc.ColumnName).Where(Function(c)  dt.AsEnumerable.Any(Function(r) not String.IsNullOrEmpty(r(c).ToString))).ToArray())

Regards,

6

Hi @Yoichi

image
image1510×178 16.3 KB

Final_Dt my data table variable

7

HI,

Can you hover on the error icon and share the error message?

Regards,

8

Hi @Yoichi
Input:

image
image1005×668 12 KB

I have to remove above balnk rows and have to remove entire column if balnk header or entire column balnk then have to remove entire column without knowing the index
Note: above blank rows also without knowing index

Output:

image
image847×564 10 KB

9

HI,

Can you share your input data as file? it’s no problem if dummy data.

Regards,

10

@shaik.muktharvalli1

  1. Use read range with add headers uncheck and do not give any range.
  2. Now use for loop with dt.Columns
  3. Inside the loop use if condition with String.IsNullOrEmpty(dt.Rows(0)(currentItem.ColumnName).ToString) OrElse dt.AsEnumerable.Where(function(x) Not String.IsNullOrEmpty(x(currentitem.ColumnName").ToString)).Count=0
  4. On then side use delete column activity

Cheers

11

Hi,

How about the following sample?

image
image717×558 18.6 KB

dt = dt.DefaultView.ToTable(False,dt.Columns.Cast(Of DataColumn).Select(Function(dc) dc.ColumnName).Where(Function(c)  not String.IsNullOrEmpty(dt.Rows(0).Item(c).ToString())).ToArray())

Sample20230802-3L.zip (8.4 KB)

Regards,

12

image
image1599×1200 225 KB

13

Hi,

The following will work. Can you try this?

dt = dt.DefaultView.ToTable(False,dt.Columns.Cast(Of System.Data.DataColumn).Select(Function(dc) dc.ColumnName).Where(Function(c)  dt.AsEnumerable.Any(Function(r) not String.IsNullOrEmpty(r(c).ToString))).ToArray())

Regards,

14

Hi @Yoichi

Its only worked without knowing index in blank rows but i have to delete entire column also if header is blank and entire column is blank

Thank
Shaik Muktharvalli

15

How about the following sample. This remove columns which column’s name is blank.
Do you also need to remove all column which data is blank? (does this mean there is no data but column name exists?)

16

Column data exist but header name does not exist then have to remove entire column also

17

@Yoichi Can you see my above screenshot for your reference Input and Output

18

I think the above sample : Sample20230802-3L.zip will work as you expect. is there problem?

19

entire column not deleted

20

HI,

In my environment, the result is as the following.

image
image960×259 6.55 KB

If input data is different with yours, can you share your input file?

Regards,