How to import exel file to sql server using VB.Net

[Aviplo]

NEED HELP~!
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?
THANKS~~~!!

[Pgmer]

Try
            Dim fBrowse As New OpenFileDialog

            With fBrowse
                .Filter = "Excel files(*.xls)|*.xls|All files (*.*)|*.*"
                .FilterIndex = 1
                .Title = "Import data from Excel file"
            End With
            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
                MyCommand.Fill(DtSet)
'If dataset is not empty Then write code here to insert values to DB.
                MyConnection.Close()
            End If
  Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

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.

[Aviplo]

thanks and if i want to map the exel file to sql using sql wizard then can you please tell me how to do that.
Thanks

[Aviplo]

and one more thing.What does [sheet$] means???

oooh one more thing again

MyCommand = New System.Data.OleDb.OleDbDataAdapter("select Columns names from [sheet$]", MyConnection)

do i have to replace it with my column name in sql server

[Netcode]

do i have to replace it with my column name in sql server

yes you do. That was a sample code given to you by pgmer as he cant say exactly what the columns in your excel sheet are so you have to fix them in.

[Aviplo]

ooh ok thank you Netcode and please don't offline yet i got a lot of problem to ask :D

[Pgmer]

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...

[Netcode]

Aviplo, you're welcome.

[Aviplo]

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

[Netcode]

"select B from Menu_templateจ้า"

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

[Aviplo]

and it keep skiping this part of the code

Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

it would run normally till Dim fname As String
fname = fBrowse.FileName
then skip that part,then continue run from

MyConnection = New System.Data.OleDb.OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fname & ";" & "Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""")

and then error as i told you earlier.
thanks

[Aviplo]

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

[Aviplo]

@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

[Netcode]

you may want to see this

[Pgmer]

The code i posted is working fine for me in my project.
if you can send your excel file column names and Sheet name we can try out.

[Aviplo]

ooh may be i forgot to tell you one thing. I want to import the exel file and insert to the existing table in the sql server.I don't want to create a new table.

[Netcode]

MyCommand.TableMappings.Add("Table", "Ur table name.")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)

The part of the code above shows where to enter your table name.

[Aviplo]

Got it
Thanks you Thank you Thank you...