How to remove empty cells from datatable?

studio

#1

Hi All,

I have datatable which contains random blank cells.

Can you guys please help me out how to delete those blank cells?

Any help is much appreciated.

Thanks,

Paresh B.


#2

Hi @Paresh,

Refer this post
DataTable

Use below code to remove empty row from the table.
DataTable name->DataTableName
Use assign activity
DataTableName=DataTableName.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(""))).CopyToDataTable()

Remove rows based on particular column is empty.
DataTableName.Select("Convert(column3, System.String)< >''").CopyToDataTable()
or
DataTableName.Select("column3< >''").CopyToDataTable()

Regards,
Arivu


#3

Hi @arivu96,

Thanks for your reply!

I have already tried this however no luck… I do not want to delete whole row… i just want to delete empty unwanted cells.

Thanks,

Paresh B.


#4

Hi @arivu96,

on Trying this -

DataTableName.Select(“Convert(Part Number, System.String)< >’’”).CopyToDataTable()
or
DataTableName.Select(“Part Number< >’’”).CopyToDataTable()

[Column Name is - “Part Number”]

Getting below Error -

Please help me out if there any syntax error.


#5

Hi @Paresh,

Try this one.

DataTableName.Select("Convert([Part Number], System.String)< >''").CopyToDataTable()
Or
DataTableName.Select("[Part Number]< >''").CopyToDataTable()

Regards,
Arivu


How to remove blank row from dataTable
#6

Hi @arivu96,

Thanks,It Resolved the error but what it did is removed all rows which has empty cells.

I just want to remove those cells…not whole row.

Thanks,

Paresh B.


#7

Hello @Paresh, if we remove a certain cell from Excel, I think the data will be shifted up or shifted left, which is not preferable for a data. Is this what you tried to achieve for your datatable (to shift datas)?


#8

Hi @whyyouandi,

Yes for my data,it is preferable to shift cells up.
Any way to do that please help!

Thanks,

Paresh B.


#9

Hi @Paresh,

provide the sample input data and output data.

Regards,
Arivu


#10

Hi @arivu96,

Please Find below Image. Can’t Provide you live data of Project.
Expected and current ->

Thanks,
Paresh B


#11

Hi @Paresh, i think it is easier to do by from Excel. How about this?
Steps might be different depends on your Excel version, but you get the idea.

  1. Open Excel Data
  2. Home > Go To > Special > Blanks (Empty Cells)
  3. Home > Delete > Shift Up

If you mean to modify the DataTable, you can write it to Excel first then read it later.