Remove Empty Rows

[Shaaz]

 Hi All,

I've 2 excel file's with 15 columns and 100 rows with same data structure and I'm using * to fetch both the files.

Out of 100 rows, 60 rows has data in it whereas remaining 40 rows are completely empty.

I can exclude the empty rows using data cleanse tool.

But I need to fetch FileName from input tool and because of which I'm unable to use data Cleanse tool.

How do I achieve this ?

1. Remove empty rows

2. I should have the FileName in the output.

[binuacs]

@Shaaz Use the Filter Tool and set the customer filter as !isEmpty([Field]), where [Field] is the first field name of our input file

[Shaaz]

No, this will not work, as this remove the row where [Field] column is empty but other columns have data in it.

I need to remove the rows where all cells are empty and I've bring FileName.

Here DataCleanse tool works to remove empty rows but it doesn't remove empty rows if I bring FileName.

[DataNath]

Are you able to provide a sample/screenshot? I'm having a hard time visualising what you mean, sorry.

[binuacs]

@Shaaz another option

1. add two select tools and connect to the input tool .

2. In the first select tool exclude the file name field and keep the rest of the fields from the input file name and use the data cleansing tool to remove the empty rows

3 . in the second select tool only select the file name. Add a sample tool and select only the first record

4. Use the append tool and connect both the input. you will get all the non- blank rows with the file nane

[DataNath]

After a re-read and some thought I think I get what you mean. 15 columns isn't loads so you could just use a formula like below, obviously replacing [Mock column x] with each of your column names, apart from the FileName one. This will remove all rows where everything other than FileName is empty.

Workbook attached.

True anchor (everything you want to keep):

False anchor (rows only containing FileName:

[SPetrie]

Another option is to transpose the data, made the values into strings and summarize with concatenate (no separators). Tag the emptys and join back to the original data and then filter.

[binuacs]

@Shaaz 

[Shaaz]

I tried this way, it works when input tool is connected to a single file.

When input tool is having multiple files with *, then cross join happens and output records from append tool will not be same as output from input tool.

[Shaaz]

I would like to have a dynamic filter as its not a best practice to add hundreds of columns in filter tool.