Does anyone know how to import exel data into sql server using vb.net? I want to create a openfiledialog and allow user to choose which exel file to be imported into the server.
Thank you so much~~ >.<
PS. I am just a beginner of vb.net. So would u pls tell me the solution in detail?
Dim fBrowse As New OpenFileDialog
.Filter = "Excel files(*.xls)|*.xls|All files (*.*)|*.*"
.FilterIndex = 1
.Title = "Import data from Excel file"
If fBrowse.ShowDialog() = Windows.Forms.DialogResult.OK Then
Dim fname As String
fname = fBrowse.FileName
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fname & ";" & "Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select Columns names from [sheet$]", MyConnection)
MyCommand.TableMappings.Add("Table", "Ur table name.")
DtSet = New System.Data.DataSet
'If dataset is not empty Then write code here to insert values to DB.
Catch ex As Exception
After getting the data into data set. loop throgh ur dataset and insert the values in to DB. There myt be many techniques for this... but i know this one. you can dirctly map the Excel file to SQL using SQL wizard.
select Columns names from [sheet$]
Columns names are you column names in excel and Sheet$ is the name of ur sheet in excel from where you want to import. If it is one time activivty do it using Maping from SQL wizard or else write code as shown above...
i did like this
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select B from Menu_templateจ้า", MyConnection)
and i keep getting "Make sure the object exist and that you spell its name and path name correctly" error
My exel name really spell like this and the column name is B
i cant even pick the name of your excel sheet let alone your computer. You can simply use the wizard in SSMS. It may save you the stress except you want to do with codes for some reasons best known to you
i need to do it with the code because i have to let user open file to import to the server but i really don't know how to do it.i really appreciate you all and i i'm trying my best but it didnt work T_T
@Netcode,it's thai language so hahaha
and i need to do it with code cuz i'm not the one who will be import to the sql server but the user will do it at the other end
i appreciate you both i'm trying my best but it didn't work T_T