DATAGRIDVIEW from excel

I am copy and paste data from excel into a datagridview with the following code

Dim strPasteText As String = Clipboard.GetText
        Dim strSplitter() As String = {vbCrLf}
        Dim strSplitterC() As String = {vbTab}
        Dim strRows() As String = strPasteText.Split(strSplitter, StringSplitOptions.RemoveEmptyEntries)
        Dim iRows As Integer = strRows.Length
        Dim strCells() As String = strRows(0).Split(strSplitterC, StringSplitOptions.None)
        Dim iCells As Integer = strCells.Length
        DataGridView1.RowCount = iRows
        DataGridView1.ColumnCount = iCells
        For iColCounter As Integer = 0 To iCells - 1
            DataGridView1.Columns(iColCounter).Name = strCells(iColCounter)
        Next
        DataGridView1.Rows(0).Selected = True
        For iRowCounter As Integer = 1 To iRows - 1
            strCells = strRows(iRowCounter).Split(strSplitterC, StringSplitOptions.None)
            For iCellCounter As Integer = 0 To iCells - 1
                DataGridView1.Rows(iRowCounter - 1).Cells(iCellCounter).Value = strCells(iCellCounter)
            Next
        Next
        Me.DataGridView1.Columns(0).Width = 90
        Me.DataGridView1.Columns(1).Width = 55
        Me.DataGridView1.Columns(2).Width = 55
        Me.DataGridView1.Columns(3).Width = 55
        Me.DataGridView2.Columns(0).Width = 90
        Me.DataGridView2.Columns(1).Width = 55
        Me.DataGridView2.Columns(2).Width = 40

Open in new window



this works fine, however I do not have control of the excel data and sometimes it comes with some extra characters  that I do not want on the datagridview, since once I get the data on the datagridview I am writting to SQL table, so I need this data to be clean.

What I have seen on this data coming is that sometimes has extra spaces or characters like  -  or "  . so is there any way to eliminate  all this once I copy into the datagridview  or before I copy
 there are not a lot of data posible 100 rows and 4 columns
teogosAsked:
Who is Participating?
 
CodeCruiserConnect With a Mentor Commented:
You can use trim function to get rid of any characters at either end of the string

strCells(iCellCounter).Trim().Trim("-")
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.