locked
unwanted characters inside the Datatable RRS feed

  • Question

  • HI

    I want to review an excel file after importing it from excel. And save the Excel file in datatable.
    Also there might be unwanted Symbols or characters that I want to delete inside the Datatable

    Symbols are not desirable {' / # ] [ $ % () ! "}

    My unsuccessful method of identifying the symbol

     For i = 0 To DT.Rows.Count - 1
                                If DT Is Nothing Then
                                    Exit For : Exit Sub
                                End If
                                If DT.Rows(i).Item(i).ToString.Contains("'") Then
                                    MsgBox("UNWANTED CHARE" & "EXCEL ROW NO" & DT.Rows(i).Item(i).ToString)
                                Else
                                    MsgBox("GOOD")
    
                                End If
                            Next

    Wednesday, September 30, 2020 8:37 AM

Answers

  • It seems that you also want to preview the unwanted values. Then you can use two loops like these:

    If DT Is Nothing Then
        Exit Sub
    End If
    
    For i = 0 To DT.Rows.Count - 1
        For j = 0 To DT.Columns.Count - 1
            Dim text = DT(i)(j).ToString
            If text.Contains("'") Then
                MsgBox(String.Format("Unwanted character {0} in row {1}, column {2}", "'", i, j))
            Else
                MsgBox("GOOD")
            End If
        Next
    Next
    

    The body can be adjusted to look for other unwanted characters.


    • Edited by Viorel_MVP Wednesday, September 30, 2020 2:02 PM
    • Marked as answer by ahmeddc Friday, October 2, 2020 10:32 AM
    Wednesday, September 30, 2020 2:01 PM
  • Hi ahmeddc,

    You can bind DataTable to DataGridView to preview the data, and then use 'Clean' method leshay suggested to remove unwanted characters from datagridview cells.

    Here's the code you may need.

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            DataGridView1.AllowUserToAddRows = False
            For row As Integer = 0 To DataGridView1.Rows.Count - 1
                For column As Integer = 0 To DataGridView1.Columns.Count - 1
                    Dim value As String = DataGridView1(column, row).Value.ToString
                    If value IsNot Nothing Then
                        DataGridView1(column, row).Value = Clean(value)
                    End If
                Next
            Next
        End Sub

    Hope it could be helpful.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ahmeddc Friday, October 2, 2020 10:33 AM
    Thursday, October 1, 2020 7:07 AM

All replies

  • Hi

    Here is one of many possibilities

    Option Strict On
    Option Explicit On
    Public Class Form1
    	Dim filter As String = "{'/#][$%()!""}"
    	Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
    		Dim s As String = "some {text} containing [the] unwanted (characters)' which /need# to be $removed !from this% string"
    
    		Dim cleaned As String = Clean(s)
    
    		' cleaned = some text containing the unwanted characters which need to be removed from this string
    	End Sub
    	Function Clean(s As String) As String
    		For Each c As Char In filter
    			s = s.Replace(c, String.Empty)
    		Next
    		Return s
    	End Function
    End Class


    Regards Les, Livingston, Scotland

    Wednesday, September 30, 2020 12:56 PM
  • It seems that you also want to preview the unwanted values. Then you can use two loops like these:

    If DT Is Nothing Then
        Exit Sub
    End If
    
    For i = 0 To DT.Rows.Count - 1
        For j = 0 To DT.Columns.Count - 1
            Dim text = DT(i)(j).ToString
            If text.Contains("'") Then
                MsgBox(String.Format("Unwanted character {0} in row {1}, column {2}", "'", i, j))
            Else
                MsgBox("GOOD")
            End If
        Next
    Next
    

    The body can be adjusted to look for other unwanted characters.


    • Edited by Viorel_MVP Wednesday, September 30, 2020 2:02 PM
    • Marked as answer by ahmeddc Friday, October 2, 2020 10:32 AM
    Wednesday, September 30, 2020 2:01 PM
  • It seems that you also want to preview the unwanted values. Then you can use two loops like these:

    If DT Is Nothing Then
        Exit Sub
    End If
    
    For i = 0 To DT.Rows.Count - 1
        For j = 0 To DT.Columns.Count - 1
            Dim text = DT(i)(j).ToString
            If text.Contains("'") Then
                MsgBox(String.Format("Unwanted character {0} in row {1}, column {2}", "'", i, j))
            Else
                MsgBox("GOOD")
            End If
        Next
    Next

    The body can be adjusted to look for other unwanted characters.


    hi  Viorel_

    The way you used there are many unwanted characters, and I will use more than one condition.

    Wednesday, September 30, 2020 2:42 PM
  • Hi ahmeddc,

    You can bind DataTable to DataGridView to preview the data, and then use 'Clean' method leshay suggested to remove unwanted characters from datagridview cells.

    Here's the code you may need.

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            DataGridView1.AllowUserToAddRows = False
            For row As Integer = 0 To DataGridView1.Rows.Count - 1
                For column As Integer = 0 To DataGridView1.Columns.Count - 1
                    Dim value As String = DataGridView1(column, row).Value.ToString
                    If value IsNot Nothing Then
                        DataGridView1(column, row).Value = Clean(value)
                    End If
                Next
            Next
        End Sub

    Hope it could be helpful.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ahmeddc Friday, October 2, 2020 10:33 AM
    Thursday, October 1, 2020 7:07 AM