Remove empty values from column

Help Something Else
1

image

I want to remove the empty value in columns without affecting the next column

2

Hi @Rachel7

  1. Read the Excel or CSV file: Use the “Read Range” activity to read the data from the Excel or CSV file and store it in a DataTable variable.
  2. Iterate through the DataTable: Use a “For Each Row” activity to loop through each row of the DataTable.
  3. Check for empty value: Within the loop, use an “If” activity to check if the value in the desired column is empty. You can use the condition String.IsNullOrEmpty(row("ColumnName").ToString) to check if the value is empty.
  4. Remove the empty value: If the value is empty, you can remove it by using the “Remove Data Row” activity and passing the current row as the input.
  5. Write the updated DataTable: After iterating through all the rows, use the “Write Range” activity to write the updated DataTable back to the Excel or CSV file.

Hope it helps!!

3

Hi @Rachel7

Use Filter Data Table Activity

image
image885Ă—746 30.8 KB

I hope it works!!

4

Hi @Rachel7

Read the excel by using read range workbook activity and store it in a datatable

Try the below linq query

- Assign -> DataTableName=DataTableName.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(""))).CopyToDataTable()

Then write the datatable to excel by using write range workbook, you can give to same excel it overwrites the old data.

It works for me check the below image for better understanding.

image
image807Ă—551 79.3 KB

Hope it helps!!

5

Its not removing empty spaces

6

filter data table will remove the whole row. I want only the empty column to be removed not the full row

7

Is it possible without looping

8

@Rachel7
Why without looping.
The code with loop makes good understand and easy too

9

Hi @mkankatala field - do we need to give column name or field itself.If possible can you share your input file

10

No need to give any field or column in this, give the linq querey what I give exact. It will remove the all empty rows in the excel file.

Hope you understand!!

11

I tried, but no luck. I want to remove the empty values

12

@Rachel7
Try with my solution

Hope it helps!!

13

I have more number of rows. If we loop it, it will take more time.
Your solution will work definitely

14

@Rachel7 When we use the Remove data row activity inside the for each loop it didn’t work it will throw a error.

15

How are you ending up with the original set of data? Seems like you should focus on fixing the source so it provides correct data.

16

Hi @Rachel7 ,

Could you check with the below Steps :

  1. Firstly, we will read the data from the Excel sheet, store the data in a datatable variable say DT.

  2. We will then loop through the column names in the datatable and by doing so we will only capture that particular column data and remove the empty values in it and store it in an Array of DataRow variable.

(In For Each List of Items) = DT.Columns.Cast(Of DataColumn).Select(Function(x)x.ColumnName).ToArray

drArray = DT.DefaultView.ToTable(false,columnName).AsEnumerable.Where(Function(x)Not(String.IsNullOrWhiteSpace(x(0).ToString))).ToArray

Here, drArray is a variable of type Array Of DataRow.

image
image742Ă—307 16.7 KB

  1. Next, we will find the Excel letter Range by using the index of the Column retrieved from For Each activity.
ColumnLetter = UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(i+1)

Here, i is index created in the For Each activity and ColumnLetter is a string variable.

image
image732Ă—143 9.33 KB

  1. Next, we can write the Filtered data to the Excel Output sheet with Range as below :
ColumnLetter+":"+ColumnLetter

image

Let us know if you were able to implement this method and if it is acceptable for your case.

1 Like