Solved

DATAGRIDVIEW from excel

Posted on 2013-06-10
2
305 Views
Last Modified: 2013-06-13
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
0
Comment
Question by:teogos
2 Comments
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 39238890
You can use trim function to get rid of any characters at either end of the string

strCells(iCellCounter).Trim().Trim("-")
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now