none
problem when importing from excel file RRS feed

  • Question

  • Hi members 

    when i import an excel fileto a datatable  the first rows  it be the column name in the datatgridview

    so i created another datatable where i named the column

    after that the code work perfectly but the first row become unreadeble it goes directly to 2nd row 

    this my code

      openFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop
                openFileDialog.Filter = "All File (*.*)|*.*|Excel Files(*.xlsx)|*xlsx|Xls Files(*.xls)|*.xls"
                If openFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK Then
                    Dim fi As New IO.FileInfo(openFileDialog.FileName)
                    Dim FileName As String = openFileDialog.FileName
                    excel = fi.FullName
                    conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + excel + ";Extended Properties=Excel 12.0;")
                    Dim command As New OleDbCommand("select * from [Feuil1$]", conn)
                    Dim dta2 As New OleDbDataAdapter(command)
    
                dta2.Fill(tab2)
                conn.Close()
    
    
                DT.Columns.Add("Command", GetType(String)) 'txt column
                    DT.Columns.Add("Article", GetType(String)) 'txt column
                    DT.Columns.Add("Désignation", GetType(String)) 'txt column
                    DT.Columns.Add("Qte", GetType(String)) 'sql column
                    DT.Columns.Add("PU", GetType(Double)) 'txt column
    
                For n = 0 To tab2.Rows.Count - 1
                    Dim Nrow As DataRow = DT.NewRow
                    Nrow("Command") = tab2.Rows(n).Item(0)
                    Nrow("Article") = tab2.Rows(n).Item(1)
                    Nrow("Qte") = tab2.Rows(n).Item(2)
                    Nrow("PU") = tab2.Rows(n).Item(3)
                    If cn.State = ConnectionState.Open Then
                        cn.Close()
                    End If
                    cn.Open()
                    Dim cmd23 As New SqlCommand("select AR_Design from F_ARTICLE where ar_ref='" & Nrow("Article").ToString & "'", cn)
                    Dim lbn As SqlDataReader = (cmd23.ExecuteReader)
                    lbn.Read()
    
    
                    Nrow("Désignation") = lbn(0)
    
                    DT.Rows.Add(Nrow)
                Next
                For i = 1 To DT.Rows.Count - 1
                        If cn.State = ConnectionState.Open Then
                            cn.Close()
                        End If
                        cn.Open()
                        Dim cmd5 As New SqlCommand("select count(AR_Ref) From F_article where ar_ref='" & DT.Rows(i).Item(1).ToString & "'", cn)
    
                        Dim ret = cmd5.ExecuteScalar
                        If ret = 0 Then
                            MessageBox.Show("Article n'existe pas : " & DT.Rows(i).Item(1).ToString & " BON COMMANDE : " & DT.Rows(i).Item(0).ToString)
                            Exit Sub
                        Else
    
    
    
                    End If
    
                    Next
    
    
                    DT.Columns("Désignation").SetOrdinal(2)
                            DataGridView4.RowHeadersWidth = 53
    
                            DataGridView4.DataSource = DT
                    End If

    problem when i import from excel the first row becom the column name

    even i did not write names of the columns in the excel file

    so here the problem is the name of column and the 1st row in excel file

    please how i manage to solve my problem

     

    Saturday, December 7, 2019 10:37 AM

Answers

  • Hello,

    Here is a working example pointing to one specific Excel file, a specific WorkSheet.

    Connection class which is setup to do first row with data or first row with column names. When using NoHeaderConnectionString method each column name is Fn were n is the ordinal index of columns in the sheet.

    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

    Form code

    Imports System.Data.OleDb
    
    Public Class Form1
        ''' <summary>
        ''' Load a WorkSheet where first row is data,
        ''' cell data is strongly typed, not all
        ''' strings, some are type double, if we had
        ''' boolean and date cells they would be strongly typed
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        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 = "SELECT * FROM [Sheet5$]"
            Dim fileName = "Shoppers.xlsx"
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = connection.NoHeaderConnectionString(fileName)
                }
                Using cmd As New OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = selectStatement
                    }
    
                    cn.Open()
                    table.Load(cmd.ExecuteReader())
                    table.Columns("F1").ColumnName = "Id"
                    table.Columns("F2").ColumnName = "Product"
                    table.Columns("F3").ColumnName = "Category id"
                    table.Columns("F4").ColumnName = "Per stock"
                    table.Columns("F5").ColumnName = "UnitPrice"
    
                    DataGridView1.DataSource = table
    
                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 Houssem12 Monday, December 9, 2019 9:29 AM
    Saturday, December 7, 2019 1:11 PM
    Moderator

All replies

  • Check the effect of “;HDR=Yes” or “;HDR=No” appended to connection string (https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax).

    Saturday, December 7, 2019 1:01 PM
  • Hello,

    Here is a working example pointing to one specific Excel file, a specific WorkSheet.

    Connection class which is setup to do first row with data or first row with column names. When using NoHeaderConnectionString method each column name is Fn were n is the ordinal index of columns in the sheet.

    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

    Form code

    Imports System.Data.OleDb
    
    Public Class Form1
        ''' <summary>
        ''' Load a WorkSheet where first row is data,
        ''' cell data is strongly typed, not all
        ''' strings, some are type double, if we had
        ''' boolean and date cells they would be strongly typed
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        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 = "SELECT * FROM [Sheet5$]"
            Dim fileName = "Shoppers.xlsx"
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = connection.NoHeaderConnectionString(fileName)
                }
                Using cmd As New OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = selectStatement
                    }
    
                    cn.Open()
                    table.Load(cmd.ExecuteReader())
                    table.Columns("F1").ColumnName = "Id"
                    table.Columns("F2").ColumnName = "Product"
                    table.Columns("F3").ColumnName = "Category id"
                    table.Columns("F4").ColumnName = "Per stock"
                    table.Columns("F5").ColumnName = "UnitPrice"
    
                    DataGridView1.DataSource = table
    
                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 Houssem12 Monday, December 9, 2019 9:29 AM
    Saturday, December 7, 2019 1:11 PM
    Moderator
  • Hi Houssem12, 

    Thank you for posting here.

    As Viorel_ suggested, you need to add ‘HDR=No;’ in your connection string.

        conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + excel + ";Extended Properties='Excel 12.0;HDR=No;'")

    You will get ‘F1’,’F2’ and so on as first row in datatable by default.

    You can rename a column by setting its ColumnName after you have loaded the data:

        dataTable.Columns(0).ColumnName = "Column1"
        dataTable.Columns(1).ColumnName = "Column2"
        dataTable.Columns(2).ColumnName = "Column3"
    

    Result:

    Hope it can help you.

    Best Regards,

    Xingyu Zhao




    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 9, 2019 5:38 AM
  • thanks a lot that help me
    Monday, December 9, 2019 9:31 AM