Create sql Table programatically using column names of excel sheet vb.net

Question

I am trying to import an excel sheet as a sql table(with column names already in the excel sheet) I am doing the following steps to finally create table.

step 1: browse for excel file and fill the combo box with the list of excel sheets.

Private Sub btnspibrowse_Click(sender As System.Object, e As System.EventArgs) Handles btnspibrowse.Click
    If openfile.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
        Cursor = Cursors.WaitCursor

        'Populating the Combobox with the sheet names
        xlconnstring = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + openfile.FileName + "; Extended Properties='Excel 12.0 Xml;HDR=YES'")
        Dim shtname As String = ""
        xldatatable = New DataTable
        Try 'for XL oledb


            'get the nams of sheets
            xlconnstring.Open()
            'help regarding getoledbschematale
            'https://support.microsoft.com/en-in/kb/309488
            'The OLE DB .NET Data Provider uses the GetOleDbSchemaTable method of the OleDbConnection object to expose schema information. 
            'GetOleDbSchemaTable returns a DataTable that is populated with the schema information.

            'The first argument of GetOleDbSchemaTable is the schema parameter, 
            'an OleDbSchemaGuid argument that identifies which schema information to return (such as tables, columns, and primary keys). 
            'The second argument is an Object array of restrictions to filter the rows that are returned in the schema DataTable 
            '(for example, you may specify restrictions for table name, type, owner, and /or schema).

            xldatatable = xlconnstring.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

            Dim int As Integer = 0
            For int = 0 To xldatatable.Rows.Count - 1
                If xldatatable.Rows(int)!TABLE_TYPE.ToString = "TABLE" Then
                    cbodbsheet.Items.Add(xldatatable.Rows(int)!TABLE_NAME.ToString())
                End If
            Next
            xldatatable = Nothing
            xlconnstring.Close()
        Catch ex As Exception
            MsgBox(ex.ToString, MsgBoxStyle.Critical, "Database Import Error")
        End Try
    End If
    Cursor = Cursors.Default

End Sub

Step 2: I select the sheet required and show a glimpse of the sheet to user in datagridview

Private Sub cbodbsheet_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cbodbsheet.SelectedIndexChanged
    Cursor = Cursors.WaitCursor
    xlconnstring = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + openfile.FileName + "; Extended Properties='Excel 12.0 Xml;HDR=YES'")
    Dim shtname As String = ""
    Try 'for XL oledb
        xlcommand = New OleDbCommand
        xldatatable = New DataTable

        'get the nams of sheets
        xlconnstring.Open()
        Try
            'help regarding getoledbschematale
            'https://support.microsoft.com/en-in/kb/309488
            'The OLE DB .NET Data Provider uses the GetOleDbSchemaTable method of the OleDbConnection object to expose schema information. 
            'GetOleDbSchemaTable returns a DataTable that is populated with the schema information.

            'The first argument of GetOleDbSchemaTable is the schema parameter, 
            'an OleDbSchemaGuid argument that identifies which schema information to return (such as tables, columns, and primary keys). 
            'The second argument is an Object array of restrictions to filter the rows that are returned in the schema DataTable 
            '(for example, you may specify restrictions for table name, type, owner, and /or schema).

            xldatatable = xlconnstring.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

            'Dim shtindex As Integer = xlschema.Rows.IndexOf(cbodbsheet.SelectedValue)
            shtname = xldatatable.Rows(cbodbsheet.SelectedIndex)("TABLE_NAME").ToString()
            xlconnstring.Close()
            xldatatable = Nothing
        Catch ex1 As Exception
            MsgBox(ex1, MsgBoxStyle.Critical, "Import Wizard-Excel Table Schema")
        End Try

        'read from selected sheet
        xlconnstring.Open()
        Try
            xldatatable = New DataTable
            xlcommand.CommandText = "SELECT * From [" & shtname & "]"
            xlcommand.Connection = xlconnstring
            xldataadapter.SelectCommand = xlcommand
            xldataadapter.Fill(xldatatable)
            xlconnstring.Close()
        Catch ex2 As Exception
            MsgBox(ex2.ToString(), MsgBoxStyle.Critical, "Import Wizard-Excel Command Query")
        End Try
        'bind data to gridview
        dgxlview.DataSource = xldatatable
    Catch ex3 As Exception
        MsgBox(ex3.ToString(), MsgBoxStyle.Critical, "Import Wizard-OLEDB Connection Problems")
    End Try
    spiselectedflag = True
    ' MsgBox(spiselectedflag)
    Cursor = Cursors.Default
    xldatatable = Nothing
    xlcommand = Nothing
    xldataadapter = Nothing
End Sub

Step 3: The selected sheet has a table of any number of rows(it according to requirement). So upon clicking a button I need to first create a table with all the columns of this selected excel sheet. But I am not able to fetch the columns. Kindly guide me with this simple thing.

Thanks for reading.

Why aren't you just creating a data table? If you create the datatable, you could then get the column name headers then create some stored procedure to fist create the table with desired names then insert the rows. Stored Proc would look like this ALTER TABLE table_name ADD column_name datatype - the column name would be from your excel file. — codeMonger123, Apr 08 '16 at 16:24
Thankyou @codeMonger123 for replying. Can you guide me to extract column headers from the selected excel sheet??? I am having problem to extract column headers using GetOleDbSchemaTable. Rest I found to do it with stored procedure. Kindly guide me with the code. — Srikanth Chilivery, Apr 09 '16 at 04:10
Hi, I am now at present extracting column headers from the datat grid view. and use these to build table in sql. Is it a good practice or do you think that I should read the column names from Excel using oledbconnection??? — Srikanth Chilivery, Apr 10 '16 at 09:19
I found the method to extract using datatable. Code till Step 5 of [this page](https://support.microsoft.com/en-in/kb/318373#bookmark-4) and Answer of [this page](https://stackoverflow.com/questions/9107916/sorting-rows-in-a-data-table) — Srikanth Chilivery, Apr 10 '16 at 10:33

score 0 · Answer 1 · answered Apr 10 '16 at 14:04

Here is some code that took me a while to figure out initially, but I use it all the time now for xlsx and similar code for csv. Its simple and from what I researched one of the fastest ways to process. It uses a Function GetFiles with anything that has xlsx, will loop through that list and create a datatable based on that list.

Friend Shared Function GetExcelFileToDataTable As DataTable

 Dim lstFilesToProcess() As String = Directory.GetFiles("YOURFILEPATH", "*.xlsx")

    Try
        Dim dt As New DataTable
        Dim Conn As System.Data.OleDb.OleDbConnection
        Dim cmd As System.Data.OleDb.OleDbDataAdapter

        If lstFilesToProcess.Length > 0 Then
            For Each ExcelFiles In lstFilesToProcess

                Dim FileInfo As New FileInfo(ExcelFiles)
                Dim strFileName As String = FileInfo.Name

                    Conn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFiles + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1';")
                    Conn.Open()
                    Dim dtSheets As DataTable = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
                    Dim listSheet As New List(Of String)
                    Dim drSheet As DataRow
                    For Each drSheet In dtSheets.Rows
                        listSheet.Add(drSheet("TABLE_NAME").ToString())
                        cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [" & drSheet("TABLE_NAME").ToString() & "]", Conn)
                        cmd.TableMappings.Add("Table", "Net-informations.com")
                        cmd.Fill(dt)
                        Conn.Close()
                    Next

            Next
        End If

        Return dt

    Catch ex As Exception  
        Return Nothing
    End Try

End Function

Create sql Table programatically using column names of excel sheet vb.net

1 Answers1