How to clean excel file?

Well, the bot only needs the cellphone numbers of the customers; however, occasionally, the files contain emails and messages. I want to clean up the excel file so that it only contains the numbers and removes any extraneous columns.

the files looks like this.

I used this solution but it cleared all the sheet1 and i wanted to delete the columns

Thanks in advance


Try this:

// Assuming dtInput is your DataTable containing the Excel data
// Replace “PhoneNumber” with the actual column name containing the phone numbers

// Select only rows where the “PhoneNumber” column is not null or empty
Dim filteredRows = dtInput.AsEnumerable().Where(Function(row) Not String.IsNullOrWhiteSpace(row(“PhoneNumber”).ToString())).CopyToDataTable()

// Create a new DataTable with only the “PhoneNumber” column
Dim dtCleaned As DataTable = New DataTable()

// Copy filtered phone numbers to the new DataTable
For Each row As DataRow In filteredRows.Rows

// Assign the cleaned DataTable to the original DataTable variable
dtInput = dtCleaned

When working with Excel, also have a look at these Activities:

And also check the below activity for using it against a range:

As an Alternate we can work via DataTable and write it back to Excel


Another approach using datatable to keep only one column

YourDataTableVar = YourDataTableVar.DefaultView.ToTable(False, "ColumnToKeep")

@naveen.s Thank you so much

@ppr thank you so much

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.