How to clean excel file?

[pabaleloh]

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

[naveen.s]

@pabaleloh

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()
dtCleaned.Columns.Add(“PhoneNumber”)

// Copy filtered phone numbers to the new DataTable
For Each row As DataRow In filteredRows.Rows
dtCleaned.Rows.Add(row(“PhoneNumber”).ToString())
Next

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

[ppr (Peter Preuss)]

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

Activities - Delete Column

Activities - Fill Range

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

[rlgandu]

@pabaleloh

Another approach using datatable to keep only one column

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

[pabaleloh]

@naveen.s Thank you so much

[pabaleloh]

@ppr thank you so much