none
import excel problem RRS feed

  • Question

  • hi

    Use this code to import data from Excel
    The code works if the file does not have a large number of data

    error message after waiting
    The CLR has been unable to transition from COM context 0x3caf88 to COM context 0x3cb0f8 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.

      Try
                Dim openFileDialog1 As System.Windows.Forms.OpenFileDialog
                openFileDialog1 = New System.Windows.Forms.OpenFileDialog
                With openFileDialog1
                    .DefaultExt = ".xls"
                    .AddExtension = True
                    .Filter = "Excel Worksheets|*.xls; *.xlsx; *.xlsm"
                    If .ShowDialog = Windows.Forms.DialogResult.OK Then
                        filenameB = (CType(.FileName, String))
                    End If
                End With
                If filenameB <> "" Then
                   
                    Dim SelectStatement As String = "SELECT * FROM [Sheet1$]"
                    Using cn As New OleDbConnection With {.ConnectionString = Connection.NoHeaderConnectionString(filenameB)}
                        Using cmd As New OleDbCommand With {.Connection = cn, .CommandText = SelectStatement}
                            cn.Open()
    
                            Try
                                dt.Load(cmd.ExecuteReader)
                            Catch ex As Exception
                                MessageBox.Show(ex.Message)
                            End Try
                            'DATAG_CLIEN.DataSource = dt
                            For i = 1 To dt.Rows.Count - 1
                                DATAG_CLIEN.Rows.Add(New String() {dt.Rows(i).Item(0).ToString, dt.Rows(i).Item(1).ToString})
                            Next
                        End Using
                    End Using
                Else
                    MsgBox("file not found")
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try


    code


    • Edited by monemas Friday, December 6, 2019 4:21 PM
    Friday, December 6, 2019 4:20 PM

Answers

  • I could not reproduce your issues with over 4,000 rows and four columns.

    Here is what I've done

    Form code

    Imports System.Data.OleDb
    
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Dim table As New DataTable
            Dim Connection As New Connections
            Dim SelectStatement As String = "SELECT * FROM [Demo$]"
            Dim fileName = "C:\Dotnet_Development\VS2017\ExcelFiles\Shoppers.xlsx"
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Connection.NoHeaderConnectionString(fileName)
                }
                Using cmd As New OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = SelectStatement
                    }
    
                    Try
                        cn.Open()
                        table.Load(cmd.ExecuteReader())
                        DataGridView1.DataSource = table
                        Label1.Text = $"Row count: {table.Rows.Count}  Column count: {table.Columns.Count}"
                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                    End Try
    
                End Using
            End Using
        End Sub
    End Class

    Connection class

    Imports System.Data.OleDb
    
    Public Class Connections
        <DebuggerStepThrough()>
        Public Function HeaderConnectionString(fileName As String, Optional IMEX As Integer = 1) As String
            Dim builder As New OleDbConnectionStringBuilder
            If IO.Path.GetExtension(fileName).ToUpper = ".XLS" Then
                builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                builder.Add("Extended Properties", $"Excel 8.0;IMEX={IMEX};HDR=Yes;")
            Else
                builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                builder.Add("Extended Properties", $"Excel 12.0;IMEX={IMEX};HDR=Yes;")
            End If
    
            builder.DataSource = fileName
    
            Return builder.ToString
    
        End Function
        ''' <summary>
        ''' Create a connection where first row contains data
        ''' </summary>
        ''' <param name="fileName"></param>
        ''' <param name="IMEX"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        <DebuggerStepThrough()>
        Public Function NoHeaderConnectionString(fileName As String, Optional IMEX As Integer = 1) As String
            Dim builder As New OleDbConnectionStringBuilder
            If IO.Path.GetExtension(fileName).ToUpper = ".XLS" Then
                builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                builder.Add("Extended Properties", $"Excel 8.0;IMEX={IMEX};HDR=No;")
            Else
                builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                builder.Add("Extended Properties", $"Excel 12.0;IMEX={IMEX};HDR=No;")
            End If
    
            builder.DataSource = fileName
    
            Return builder.ToString
    
        End Function
    End Class

    Or with defined columns

    Imports System.Data.OleDb
    ''' <summary>
    ''' This version the columns are created in the designer
    ''' and DataPropertyName set to F1, F2, F3, F4
    ''' </summary>
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            DataGridView1.AutoGenerateColumns = False
    
            Dim table As New DataTable
            Dim Connection As New Connections
            Dim SelectStatement As String = "SELECT * FROM [Demo$]"
            Dim fileName = "C:\Dotnet_Development\VS2017\ExcelFiles\Shoppers.xlsx"
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Connection.NoHeaderConnectionString(fileName)
                }
                Using cmd As New OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = SelectStatement
                    }
    
                    Try
                        cn.Open()
                        table.Load(cmd.ExecuteReader())
                        DataGridView1.DataSource = table
                        Label1.Text = $"Row count: {table.Rows.Count}  Column count: {table.Columns.Count}"
                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                    End Try
    
                End Using
            End Using
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by monemas Friday, December 6, 2019 6:59 PM
    Friday, December 6, 2019 6:39 PM
    Moderator

All replies

  • Hello,

    Large amount of data has different means for different developers so please indicate how many columns and how many rows are you talking about in this case. And the error is from dt.Load(cmd.ExecuteReader) ?


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, December 6, 2019 4:58 PM
    Moderator
  • hi karen

    error occurs during display in datagridview
    Friday, December 6, 2019 5:26 PM
  • hi karen

    error occurs during display in datagridview
    How many rows, how many columns ???

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, December 6, 2019 5:49 PM
    Moderator
  • 3 column 3000 row
    Friday, December 6, 2019 6:09 PM
  • I could not reproduce your issues with over 4,000 rows and four columns.

    Here is what I've done

    Form code

    Imports System.Data.OleDb
    
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Dim table As New DataTable
            Dim Connection As New Connections
            Dim SelectStatement As String = "SELECT * FROM [Demo$]"
            Dim fileName = "C:\Dotnet_Development\VS2017\ExcelFiles\Shoppers.xlsx"
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Connection.NoHeaderConnectionString(fileName)
                }
                Using cmd As New OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = SelectStatement
                    }
    
                    Try
                        cn.Open()
                        table.Load(cmd.ExecuteReader())
                        DataGridView1.DataSource = table
                        Label1.Text = $"Row count: {table.Rows.Count}  Column count: {table.Columns.Count}"
                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                    End Try
    
                End Using
            End Using
        End Sub
    End Class

    Connection class

    Imports System.Data.OleDb
    
    Public Class Connections
        <DebuggerStepThrough()>
        Public Function HeaderConnectionString(fileName As String, Optional IMEX As Integer = 1) As String
            Dim builder As New OleDbConnectionStringBuilder
            If IO.Path.GetExtension(fileName).ToUpper = ".XLS" Then
                builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                builder.Add("Extended Properties", $"Excel 8.0;IMEX={IMEX};HDR=Yes;")
            Else
                builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                builder.Add("Extended Properties", $"Excel 12.0;IMEX={IMEX};HDR=Yes;")
            End If
    
            builder.DataSource = fileName
    
            Return builder.ToString
    
        End Function
        ''' <summary>
        ''' Create a connection where first row contains data
        ''' </summary>
        ''' <param name="fileName"></param>
        ''' <param name="IMEX"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        <DebuggerStepThrough()>
        Public Function NoHeaderConnectionString(fileName As String, Optional IMEX As Integer = 1) As String
            Dim builder As New OleDbConnectionStringBuilder
            If IO.Path.GetExtension(fileName).ToUpper = ".XLS" Then
                builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                builder.Add("Extended Properties", $"Excel 8.0;IMEX={IMEX};HDR=No;")
            Else
                builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                builder.Add("Extended Properties", $"Excel 12.0;IMEX={IMEX};HDR=No;")
            End If
    
            builder.DataSource = fileName
    
            Return builder.ToString
    
        End Function
    End Class

    Or with defined columns

    Imports System.Data.OleDb
    ''' <summary>
    ''' This version the columns are created in the designer
    ''' and DataPropertyName set to F1, F2, F3, F4
    ''' </summary>
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            DataGridView1.AutoGenerateColumns = False
    
            Dim table As New DataTable
            Dim Connection As New Connections
            Dim SelectStatement As String = "SELECT * FROM [Demo$]"
            Dim fileName = "C:\Dotnet_Development\VS2017\ExcelFiles\Shoppers.xlsx"
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Connection.NoHeaderConnectionString(fileName)
                }
                Using cmd As New OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = SelectStatement
                    }
    
                    Try
                        cn.Open()
                        table.Load(cmd.ExecuteReader())
                        DataGridView1.DataSource = table
                        Label1.Text = $"Row count: {table.Rows.Count}  Column count: {table.Columns.Count}"
                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                    End Try
    
                End Using
            End Using
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by monemas Friday, December 6, 2019 6:59 PM
    Friday, December 6, 2019 6:39 PM
    Moderator
  • I could not reproduce your issues with over 4,000 rows and four columns.

    Here is what I've done

    Form code

    Imports System.Data.OleDb
    
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Dim table As New DataTable
            Dim Connection As New Connections
            Dim SelectStatement As String = "SELECT * FROM [Demo$]"
            Dim fileName = "C:\Dotnet_Development\VS2017\ExcelFiles\Shoppers.xlsx"
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Connection.NoHeaderConnectionString(fileName)
                }
                Using cmd As New OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = SelectStatement
                    }
    
                    Try
                        cn.Open()
                        table.Load(cmd.ExecuteReader())
                        DataGridView1.DataSource = table
                        Label1.Text = $"Row count: {table.Rows.Count}  Column count: {table.Columns.Count}"
                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                    End Try
    
                End Using
            End Using
        End Sub
    End Class

    Connection class

    Imports System.Data.OleDb
    
    Public Class Connections
        <DebuggerStepThrough()>
        Public Function HeaderConnectionString(fileName As String, Optional IMEX As Integer = 1) As String
            Dim builder As New OleDbConnectionStringBuilder
            If IO.Path.GetExtension(fileName).ToUpper = ".XLS" Then
                builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                builder.Add("Extended Properties", $"Excel 8.0;IMEX={IMEX};HDR=Yes;")
            Else
                builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                builder.Add("Extended Properties", $"Excel 12.0;IMEX={IMEX};HDR=Yes;")
            End If
    
            builder.DataSource = fileName
    
            Return builder.ToString
    
        End Function
        ''' <summary>
        ''' Create a connection where first row contains data
        ''' </summary>
        ''' <param name="fileName"></param>
        ''' <param name="IMEX"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        <DebuggerStepThrough()>
        Public Function NoHeaderConnectionString(fileName As String, Optional IMEX As Integer = 1) As String
            Dim builder As New OleDbConnectionStringBuilder
            If IO.Path.GetExtension(fileName).ToUpper = ".XLS" Then
                builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                builder.Add("Extended Properties", $"Excel 8.0;IMEX={IMEX};HDR=No;")
            Else
                builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                builder.Add("Extended Properties", $"Excel 12.0;IMEX={IMEX};HDR=No;")
            End If
    
            builder.DataSource = fileName
    
            Return builder.ToString
    
        End Function
    End Class

    Or with defined columns

    Imports System.Data.OleDb
    ''' <summary>
    ''' This version the columns are created in the designer
    ''' and DataPropertyName set to F1, F2, F3, F4
    ''' </summary>
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            DataGridView1.AutoGenerateColumns = False
    
            Dim table As New DataTable
            Dim Connection As New Connections
            Dim SelectStatement As String = "SELECT * FROM [Demo$]"
            Dim fileName = "C:\Dotnet_Development\VS2017\ExcelFiles\Shoppers.xlsx"
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Connection.NoHeaderConnectionString(fileName)
                }
                Using cmd As New OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = SelectStatement
                    }
    
                    Try
                        cn.Open()
                        table.Load(cmd.ExecuteReader())
                        DataGridView1.DataSource = table
                        Label1.Text = $"Row count: {table.Rows.Count}  Column count: {table.Columns.Count}"
                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                    End Try
    
                End Using
            End Using
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    yes thanks karen
    Friday, December 6, 2019 6:59 PM