How to remove empty cells from datatable?

Help
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 filtering with expressions

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 -

image
image807×376 14.7 KB

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

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 →

image
image774×305 4.81 KB

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.

12

Hi @Paresh /@arivu96 ,

 I'm facing same issue what paresh is facing to remove empty cells based on columns. Can you please give solutions  to overcome the same issue?

@Paresh Can you please share the solution if you already resolved it?

Regards,
Chaithra

2 Likes
13

@arivu96 @Paresh Even I am facing the same issue, it would be great if you suggest a solution for this

image
image1920×1080 256 KB

Thanks in advance.

1 Like
14

Hii @neeharika9510

Here is the solution file i am uploading based on your example
EXCEL_AUTOMATION_FORUM_PROBLEM_1.zip (19.0 KB)

After executing you can see this outputs as
input Shee1
AFTER_JOINING Sheet2
OUTPUT Sheet3
Kind of lengthy logic but if you find any solution related to this example in short way just post in over here