Solved

DATAGRIDVIEW from excel

Posted on 2013-06-10
2
304 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
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…

743 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

14 Experts available now in Live!

Get 1:1 Help Now