Import to SQL database from Excel

[schoemr]

Hi, I have a friend she is running cosmetics from home. She have A LOT of data in excel spreadsheet and now we try to see if we can put that in database. It is taking very long because there are many many rows of data...

So I was think, why not import from excel? I know how to do for one table but I need help for one-to-many tables.

This is the database:

Attachment 157263

Now this is the excel (example):


Attachment 157265


Now say:

Customer A will be CustomerID 1
Customer B will be CustomerID 2
Customer C will be CustomerID 3

Then it looks like:

Attachment 157267

Is this possible? If yes, how?

Thanks

[jmcilhinney]

Think about how you would do it if you were using pen and paper instead of code. What steps would you perform? Those are the steps that your code needs to perform too. As such, you need to work out what those steps are before you can write the code. There is no magic to code. It's just an implementation of an algorithm. Working out the algorithm is the first step. Do that first.

[schoemr]

Okay, first I would import the whole sheet into a datatable. This datatable will be unbound. Once all the data is in the datatable, then I need to copy it over into bound grids... That is what I think... I am not sure.. Please give me few min ill post the first part

[schoemr]

So this is what i do so far:

To import into unbound datagridview:

Dim PrmPathExcelFile As String = Me.TextBox1.Text
        Dim strCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & PrmPathExcelFile & ";" & "Extended Properties=""Excel 12.0;"""

            Dim strsql As String

        strsql = "SELECT * FROM [Sheet1$]" '<-- Load Sheet
            Dim con As OleDbConnection = New OleDbConnection(strCon)
            Dim cmd As OleDbCommand = New OleDbCommand(strsql, con)
            con.Open()
            Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            Dim myDataSet As DataSet = New DataSet()

            myDA.Fill(myDataSet, "Column1")
            DataGridView1.DataSource = myDataSet.Tables("Column1").DefaultView
            con.Close()

Then I have on my useform this:

Attachment 157269

[ChrisE]

Hi Schoemr,

you can sort the Excelsheet when loading to DGV

in my Example I have a ExcelFile -Customers- with -Sheet1-
there is a Header named -CustomerName-
I will load this with the -DISTINCT- keyword, that means the Customername will only be loaded once.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim PrmPathExcelFile As String = "C:\Customers.xls"
        Dim strCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & PrmPathExcelFile & ";" & "Extended Properties=""Excel 8.0;"""
        Dim strsql As String

        'Select the Customernames ! with DISTINCT the will load only once
        strsql = "SELECT DISTINCT CustomerName FROM [Sheet1$]" '<-- Load Sheet

        Dim con As OleDbConnection = New OleDbConnection(strCon)
        Dim cmd As OleDbCommand = New OleDbCommand(strsql, con)
        con.Open()
        Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        Dim myDataSet As DataSet = New DataSet()
        myDA.Fill(myDataSet, "MyT")
        DataGridView1.DataSource = myDataSet.Tables("MyT").DefaultView
        con.Close()
    End Sub

now you can export the DGV to a new Table in your Database with adding an Autoincrement Field as a CustomerID
then go to the next Table, and create the next Table

create the Keys/ foreign keys that way

EDIT: Import sorted Data (CustomerNames) to a new Table in Access

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sSql As String
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\NWind.mdb")

        '//Import from Excel to new Table
        sSql = "SELECT DISTINCT CustomerName INTO Table1 From [Sheet1$] In " & _
                               "'C:\Customers.xls' 'EXCEL 8.0;' "

        con.Open()
        ExecuteSQL(con, sSql)
        con.Close()
        con = Nothing
    End Sub

    Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                    ByVal sSQL As String, _
                                    Optional ByRef ErrMessage As String = Nothing, _
                                    Optional ByVal TransAction As  _
                                    OleDb.OleDbTransaction = Nothing) As Integer
        ErrMessage = Nothing
        Try
            Dim Result As Integer = 0
            Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                Result = Cmd.ExecuteNonQuery
            End Using
            Return Result
        Catch ex As Exception
            ErrMessage = ex.Message
            Return 0
        End Try
    End Function

regards
Chris

[sapator]

Hi, I have a friend she is running cosmetics from home. She have A LOT of data in excel spreadsheet and now we try to see if we can put that in database.
Thanks

I would comment on the LOT of data. You said you want to import to a database, yet you put it on a gridview. Either way, if you need to import, "I would suggest an SQL database.
If you are using MS SQL then you can bulk insert . This will have a huge benefit in performance. In you case you need 3 bulk inserts on customer orders and details.
Also be aware of "SELECT * FROM [Sheet1$]" That might be OK if you only have one datasheet but if you have more you would better use some convenience naming like "Customers" instead of "Sheet1" .

[kpmc]

To handle relations you could refer to the previous post you put up.

To Import data from/to whatever, here is an example of a little song I wrote, may as well sing it note for note..... Dont worry.... be happy...

Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class Zips
    Dim DSet As New DataSet
    Dim BS As New BindingSource
    Dim CSVConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DATA;Extended Properties = " &
                                  """text;HDR=Yes;FMT=Delimited"""
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        'If Datatable not in DataSet add tables
        If Not DSet.Tables.Contains("ZIP") Then
            'Add table for source
            DSet.Tables.Add(New DataTable With {.TableName = "CSV_ZIP"})
            'Add table for destination
            DSet.Tables.Add(New DataTable With {.TableName = "SQL_ZIP"})

            'create source connection
            Using OleDBConn As New OleDbConnection(CSVConnString)
                'Open source connection
                OleDBConn.Open()
                'Create source reader
                Using ZipsRDR As OleDbDataReader = New OleDbCommand("Select * From [Zip.Csv]", OleDBConn).ExecuteReader
                    DSet.Tables("CSV_ZIP").Load(ZipsRDR)
                    'Load Source DataTable
                    DGV_Data.DataSource = DSet.Tables("CSV_ZIP")
                End Using
                'Close source Connection
                OleDBConn.Close()
                'Set progressbar Max to source DataTableRowCount
                Prog.Maximum = DSet.Tables("CSV_ZIP").Rows.Count
            End Using

            'Create destination connection
            Using SQLConn As New SqlConnection("Data Source=Kpmsqlb\std;Initial Catalog=KPMIT;Integrated Security=True")
                'Create destination DataAdapter
                Using ZipsDA As New SqlDataAdapter("SELECT * FROM ZipCodes", SQLConn)
                    'Fill destination DataTable
                    ZipsDA.Fill(DSet.Tables("SQL_ZIP"))
                    'Set bindingsource datasource
                    BS.DataSource = DSet.Tables("SQL_ZIP")
                    'Create Commandbuilder
                    Dim cb As New SqlCommandBuilder(ZipsDA)

                    'Iterate source DataTble Rows
                    For Each Drow As DataRow In DSet.Tables("CSV_ZIP").Rows
                        'Find "Key" from source row in destination BindingSourceList
                        Dim FindIndex As Integer = BS.Find("ZipCode", Drow("ZipCode").ToString)

                        'If FindIndex has positive value there is a row found
                        If FindIndex >= 0 Then
                            'Update destination columns from source column
                            DirectCast(BS(FindIndex), DataRowView)("City") = Drow("City").ToString


                        Else
                            'If FindIndex =-1 no key value found in BindingSource List
                            'Add rows to destination BindingSource
                            'This method is addressing rowstate information that you
                            'would run into without employing a bindingsource
                            BS.AddNew()
                            DirectCast(BS.Current, DataRowView)("Zipcode") = Drow("ZipCode").ToString
                            DirectCast(BS.Current, DataRowView)("City") = Drow("City").ToString
                            DirectCast(BS.Current, DataRowView)("StateLong") = Drow("StateLong").ToString
                            DirectCast(BS.Current, DataRowView)("StateShort") = Drow("StateShort").ToString
                            DirectCast(BS.Current, DataRowView)("County") = Drow("County").ToString
                            DirectCast(BS.Current, DataRowView)("Latitude") = Drow("Latitude").ToString
                            DirectCast(BS.Current, DataRowView)("Longitude") = Drow("Longitude").ToString
                        End If
                        'increment a progressbar
                        Prog.Value += 1
                    Next
                    'update the destination
                    BS.EndEdit()
                    ZipsDA.Update(DSet.Tables("SQL_ZIP"))
                End Using
            End Using

        End If



    End Sub

    Private Sub Zips_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub
End Class

[schoemr]

Hi, thanks for replies and code. It is hard for me to relate some of this to my question.. kpmc I can not even see what your code is supposed to do..

So this is what I have so far. I want to change my question a lil just to include two (one-to-many) tables. To make it more simple. If I can import from excel into 2 tables then I am sure I would be able to relate that to more tables.

This is my form now:

Attachment 157291

On left side I have unbound datagridview, and the other two DGV's are from the database. They are thus bound. If I were to click on customer A it will show me all the products for that customer.

This is my excel sheet now:

Attachment 157293


When I click on first button (STEP 1) this is the code to import Customers from the excel sheet:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        'this code is to import data from an excel spreadsheet. The first row in the excel sheet 

        If Len(Me.TextBox1.Text) < 1 Then
            MsgBox("Please enter the Path")
            Exit Sub
        Else

            Dim PrmPathExcelFile As String = Me.TextBox1.Text

            Dim strCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & PrmPathExcelFile & ";" & "Extended Properties=""Excel 12.0;HDR=Yes;"""
            Dim strsql As String

            'strsql = "SELECT * FROM [CustomerSheet$A1:G2234]" '<--Select Range in Excel
            strsql = "SELECT Customers FROM [Sheet1$]" '<-- Load Sheet
            Dim con As OleDbConnection = New OleDbConnection(strCon)
            Dim cmd As OleDbCommand = New OleDbCommand(strsql, con)
            con.Open()
            Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            Dim myDataSet As DataSet = New DataSet()

            myDA.Fill(myDataSet, "Column1")
            DataGridView1.DataSource = myDataSet.Tables("Column1").DefaultView
            con.Close()

        End If
    End Sub

This give me this result:

Attachment 157291


Notice that I now have a repeat of customer names... I don't want that. So I alter code to select DISTINCT:

 strsql = "SELECT DISTINCT Customers FROM [Sheet1$]" '<-- Load Sheet

This give me this result:

Attachment 157295

It is better.. But it makes a blank row on top and bottom..

Next I want to copy this data over now from the Unbound DGV to the Bound DGV:

 Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        For Each row As DataGridViewRow In Me.DataGridView1.Rows

            Dim dr As CustomersDataSet.tblCustomersRow = CustomersDataSet.tblCustomers.NewRow

            On Error Resume Next
            dr("CustomerName") = row.Cells("Customers").Value
            CustomersDataSet.tblCustomers.NewRow()
            Me.CustomersDataSet.tblCustomers.AddtblCustomersRow(dr)

        Next
    End Sub

This give me this result:

Attachment 157297

When I press save button, there is blank first and last row.

So this is where I a stuck now...

In the SQL database I have my customers (with customerID now) - don't know why that numbers go so big.. But for now I want to focus not on that.

Now I have to figure out how to populate the orders table.

[schoemr]

This is the order table:

Attachment 157301

- OrdersID will be auto increment
- CustomerID must come from tblCustomers

The rest I dont know..

May I kindly ask that if someone decide to respond to keep to this scenario? Posting code for other things which may or may not relate to my question is just confuse me...

Thanks

[ChrisE]

Hi,

I don't know why you want to use the Unbound/Bound DGV.
Do the sorting of the Customers directly to the Database, I don't have SQL-Server on this PC
so I used Access

take it Step by Step.
first sort the CustomerNames to a new Table...

'Step1; create a Table
'Step2: insert the CustomerNames only once to the new Table
'Step3: create a Index for the Primary Key

Imports System.Data.OleDb

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Step1
        CreateTable()
    End Sub

    Public Sub CreateTable()
        Dim sSQL As String = Nothing
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\NWIND.mdb")
        'here you create your Table in the Database
        'Cust_ID will be AutoIncrement Field
        sSQL = sSQL & "  Create Table tbl_NewTable"
        sSQL = sSQL & "( [Cust_ID] Integer Identity"
        sSQL = sSQL & ", [CustomerName] varChar(50)"
        sSQL = sSQL & ")"
        con.Open()
        ExecuteSQL(con, sSQL)
        con.Close()
        con = Nothing
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'Step2 : Insert the DISTINCT CustomerNames
        Dim sSql As String
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\NWind.mdb")
        'Import From Excel to the new Table you just created
        sSql = "Insert Into tbl_NewTable Select Distinct CustomerName From [Sheet1$] In 'C:\Customers.xls' 'EXCEL 8.0;' "
        con.Open()
        ExecuteSQL(con, sSql)
        con.Close()
        con = Nothing
    End Sub

    Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                   ByVal sSQL As String, _
                                   Optional ByRef ErrMessage As String = Nothing, _
                                   Optional ByVal TransAction As  _
                                   OleDb.OleDbTransaction = Nothing) As Integer
        ErrMessage = Nothing
        Try
            Dim Result As Integer = 0
            Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                Result = Cmd.ExecuteNonQuery
            End Using
            Return Result
        Catch ex As Exception
            ErrMessage = ex.Message
            Return 0
        End Try
    End Function

   

    'Step3: create your Index
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        CreateIndex()
    End Sub

    Public Sub CreateIndex()
        Dim sSQL As String = Nothing
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\NWIND.mdb")
        sSQL = sSQL & "Alter Table [tbl_NewTable] Add Constraint [PrimaryKey] Primary Key (Cust_ID)"
        con.Open()
        ExecuteSQL(con, sSQL)
        con.Close()
        con = Nothing
    End Sub

you see .. work directly with the Database, no DGV needed

here Images of the Excelsheet and the Result in the Access table





regards
Chris

[wes4dbt]

You really can't break out the Excel data like that because with just those three columns there's not enough information to know which items go with which orders.

I mean, for example, the items listed for Customer A maybe all on one order, on three separate orders, on two orders. Initially when you import the Excel file you would setup the Customer file with each unique Customer and then create a record in the Order file and the Order Details file for each row, treating each row as a unique order. Also I wouldn't put information like "Foundation" in to the Order file, that would be in the details file.

As an example I created three new tables

CustomersDemo with fields CustomerId (Autonumber), CustomerName (String)
OrdersDemo with fields OrderId (Autonumber), CustomerId (Long), OrderDate (Date : Short Format)
OrderDetailsDemo with fields OrderDetailsId (Autonumber), ItemType (String), ItemDescription (String)

This should give you a good example of what I'm talking about (this is done with an Access database)

Imports System.Data.OleDb
Public Class Form1
    Private con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=c:\ajunkproject\test.xlsx;" & "Extended Properties=""Excel 12.0;""")
    Private da As New OleDbDataAdapter("SELECT * FROM [tags$]", con)
    Private daUsers As New OleDbDataAdapter("SELECT Distinct Customer FROM [tags$]", con)
    Private dtAll As New DataTable
    Private dtUsers As New DataTable

    Dim myCon As New OleDbConnection(My.Settings.WaterConnectionString)


    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Try
            da.Fill(dtAll)
            daUsers.Fill(dtUsers)

           myCon.Open()

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim daCustomers As New OleDbDataAdapter("Select * from CustomersDemo", myCon)

        Dim daOrderDetails As New OleDbDataAdapter("Select * from OrderDetailsDemo", myCon)
        Dim dtCustomers As New DataTable
        Dim dtOrders As New DataTable
        Dim dtOrderDetails As New DataTable
        Dim cmdBldr As New OleDbCommandBuilder(daCustomers)
        cmdBldr = New OleDbCommandBuilder(daOrderDetails)
        
        daCustomers.Fill(dtCustomers)
        For Each row As DataRow In dtUsers.Rows
            Dim r As DataRow = dtCustomers.NewRow
            r.Item("CustomerName") = row("Customer")
            dtCustomers.Rows.Add(r)
        Next
        daCustomers.Update(dtCustomers)
        dtCustomers.Clear()
        daCustomers.Fill(dtCustomers)
        daOrderDetails.Fill(dtOrderDetails)

        Dim rows As DataRow()

        For Each row As DataRow In dtCustomers.Rows
            rows = dtAll.Select("Customer = '" & row("CustomerName").ToString & "'")
            If rows.Count > 0 Then
                For i As Integer = 0 To rows.Count - 1

                    Dim cmd As New OleDbCommand("Insert into OrdersDemo (CustomerId, OrderDate) Values(?,?)", myCon)

                    cmd.Parameters.AddWithValue("CustId", CLng(row("customerid").ToString))
                    cmd.Parameters.AddWithValue("ODate", Date.Now.ToShortDateString)
                    cmd.ExecuteNonQuery()
                    cmd.Parameters.Clear()

                    Dim nextId As Integer
                    'IMPORT YOU MUST RETRIEVE THE LAST OrderId
                    Using command As New OleDbCommand("SELECT @@IDENTITY", myCon)
                        'Get the last OrderId.
                        nextId = CInt(command.ExecuteScalar())
                    End Using

                    Dim newRow As DataRow = dtOrders.NewRow
                    newRow = dtOrderDetails.NewRow
                    newRow("OrderId") = nextId
                    newRow("ItemType") = rows(i).Item("ItemType")
                    newRow("ItemDescription") = rows(i).Item("Description")
                    dtOrderDetails.Rows.Add(newRow)
                Next
            End If
        Next
        daOrderDetails.Update(dtOrderDetails)

    End Sub

End Class

I didn't do any inline documentation, I ran out of time. I'm sure your smart enough to figure it out. Also, it pretty sloppy work but this is just something you would run only once. If going to SQL Sever database you may have to retrieve the OrderId differently.

[schoemr]

hi all, thank you for replies. I must now come back to this thread later. My windows 10 updated and now I am not getting SQL server 2012 to work again..