Paste from Excel to DataGridView

I have this code that helps me to paste data from an Excel file to a Datagridview:

If e.Control AndAlso e.KeyCode = Keys.V Then

    DGVLookUP.Rows.Clear()
    DGVLookUP.Columns.Clear()

    Try
        Dim Generar As Boolean = False
        Dim N As Integer
        For Each line As String In Clipboard.GetText.Split(vbNewLine)
            If Not line.Trim.ToString = "" Then
                Dim item() As String = line.Trim.Split(vbTab)
                If Generar = False Then
                    For C = 0 To UBound(item)
                        DGVLookUP.Columns.Add("C" + CStr(N), CStr(N))
                        N += 1
                    Next
                    Generar = True
                End If
                Me.DGVLookUP.Rows.Add(item)
            End If
        Next

    Catch ex As Exception
        MessageBox.Show(ex.Message, My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try

End If

I am using VB.Net and I am pasting over 3k rows and it takes me between 5-10 mins to paste the info. I would like to optimize my code or if there is another way better to paste the data.

Before we look at how to improve performance, lets start by polishing your code.

Polishing

Dim Generar As Boolean = False
Dim N As Integer

Despite the fact that the .NET naming guidelines doesn't apply for internal and private fields the general consensus is to use English, descriptive and lower Camel case names.

I've renamed the variable and changed the initial value to True as "generate" (IMO) is referring to "columns".

The variable N is useless and should be removed as you already got a variable named c which you could use.

Dim generate As Boolean = True

For Each line As String In Clipboard.GetText.Split(vbNewLine)
    If Not line.Trim.ToString = "" Then
        Dim item() As String = line.Trim.Split(vbTab)

The first line "breaks" my compiler with the error BC30512 and correctly state that "Option Strict On disallows implicit conversions from 'String' to 'Char'". You should always make sure that the strict compiler option is set to On. I'm tempted to say that this is the most important thing you'll learn by reading this review. Oh, wait! I just did! ;)

The constants vbNewLine and vbTab (defined as String) are remnants from the past and should be regarded as obsolete. Many of the methods and classes inside the Microsoft.VisualBasic assembly were added to make the transition from VB6 to VB.NET more convenient. If there's a .NET equivalent, use it! However, in this case, we need a Char array so we create a few Char constants.

The Split method has an overload which allows us to pass an option to remove empty entries. We'll use this and remove the second line. But please note that a better alternative to your boolean expression would be to use the static method String.IsNullOrWhiteSpace. I've also renamed the variable item to values.

Const tab As Char = ChrW(9)
Const lineFeed As Char = ChrW(10)
Const carriageReturn As Char = ChrW(13)

For Each line As String In Clipboard.GetText.Split({lineFeed, carriageReturn}, StringSplitOptions.RemoveEmptyEntries)
    Dim values() As String = line.Trim.Split({tab})

For C = 0 To UBound(item)
    DGVLookUP.Columns.Add("C" + CStr(N), CStr(N))

Again, you're using an obsolete method, UBound. Just read the Length property of the array. (If you need to read the length of a give dimension, invoke GetLength.)

It's advisable to use & for string concatenation. And you ought to take advantage of all of the static methods the string type has to offer. I've also renamed the variable C to cell.

For cell As Integer = 0 To (values.Length - 1)
    DGVLookUP.Columns.Add(String.Format("C{0}", cell), cell.ToString())

Performance

The complete code at this point looks like below. The great disadvantage with this code is that you add a new row in each iteration. Each call to Add will queue a bunch of messages all of which needs to be processed by the window. Do we really need to repaint the control 3000 times? The answer is of course NO. So we need to fix this.

Const tab As Char = ChrW(9)
Const lineFeed As Char = ChrW(10)
Const carriageReturn As Char = ChrW(13)

Dim generate As Boolean = True

For Each line As String In Clipboard.GetText.Split({lineFeed, carriageReturn}, StringSplitOptions.RemoveEmptyEntries)

    Dim values() As String = line.Trim.Split({tab})

    If (generate) Then
        For cell As Integer = 0 To (values.Length - 1)
            Me.DGVLookUP.Columns.Add(String.Format("C{0}", cell), cell.ToString())
        Next
        generate = False
    End If

    Me.DGVLookUP.Rows.Add(values)

Next

A much better option would be to first read the data into a data structure (we'll be using a DataTable) and then update the grid.

Const tab As Char = ChrW(9)
Const lineFeed As Char = ChrW(10)
Const carriageReturn As Char = ChrW(13)

Dim table As New DataTable()
Dim generate As Boolean = True

table.BeginLoadData()

For Each line As String In Clipboard.GetText.Split({lineFeed, carriageReturn}, StringSplitOptions.RemoveEmptyEntries)

    Dim values() As String = line.Trim.Split({tab})

    If (generate) Then
        For cell As Integer = 0 To (values.Length - 1)
            table.Columns.Add(String.Format("C{0}", cell))
        Next
        generate = False
    End If

    table.Rows.Add(values)

Next

table.EndLoadData()

Me.DGVLookUP.DataSource = table

To further increase the performance we'll need to write our own parser and scrap the split method. You might need to fine tune this if the data inside the excel cell contains a line feed and/or a carriage return.

```
Const tab As Char = ChrW(9)
Const lineFeed As Char = ChrW(10)
Const carriageReturn As Char = ChrW(13)

Dim data As String = Clipboard.GetText()
Dim table As New DataTable()
Dim generate As Boolean = True
Dim cursor As Integer = 0
Dim position As Integer = 0
Dim cell As Integer = 0
Dim row As DataRow = Nothing

table.BeginLoadData()

While (cursor < data.Length)

Select Case data.Chars(cursor)

Case

StackExchange Code Review Q#116149, answer score: 3