-
Mar 16th, 2018, 04:02 AM
#1
Thread Starter
Frenzied Member
Import to SQL database from Excel
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
-
Mar 16th, 2018, 04:12 AM
#2
Re: Import to SQL database from Excel
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.
-
Mar 16th, 2018, 04:31 AM
#3
Thread Starter
Frenzied Member
Re: Import to SQL database from Excel
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
-
Mar 16th, 2018, 05:16 AM
#4
Thread Starter
Frenzied Member
Re: Import to SQL database from Excel
So this is what i do so far:
To import into unbound datagridview:
Code:
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
-
Mar 16th, 2018, 05:41 AM
#5
Re: Import to SQL database from Excel
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.
Code:
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
Code:
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
Last edited by ChrisE; Mar 16th, 2018 at 06:22 AM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Mar 16th, 2018, 08:52 AM
#6
Re: Import to SQL database from Excel
Originally Posted by 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.
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" .
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Mar 16th, 2018, 02:41 PM
#7
Re: Import to SQL database from Excel
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...
Code:
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
-
Mar 17th, 2018, 02:31 AM
#8
Thread Starter
Frenzied Member
Re: Import to SQL database from Excel
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:
Code:
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:
Code:
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:
Code:
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.
Last edited by schoemr; Mar 17th, 2018 at 02:36 AM.
-
Mar 17th, 2018, 02:34 AM
#9
Thread Starter
Frenzied Member
Re: Import to SQL database from Excel
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
-
Mar 17th, 2018, 03:40 AM
#10
Re: Import to SQL database from Excel
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...
Code:
'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
Last edited by ChrisE; Mar 17th, 2018 at 03:46 AM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Mar 17th, 2018, 09:11 PM
#11
Re: Import to SQL database from Excel
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)
Code:
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.
-
Mar 19th, 2018, 03:06 AM
#12
Thread Starter
Frenzied Member
Re: Import to SQL database from Excel
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..
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|