1,105,423 Community Members

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

Member Avatar
Aviplo
Light Poster
43 posts since Oct 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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~~~!!

Member Avatar
Pgmer
Practically a Posting Shark
881 posts since Apr 2008
Reputation Points: 50 [?]
Q&As Helped to Solve: 158 [?]
Skill Endorsements: 3 [?]
Featured
 
0
 
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.

Member Avatar
Aviplo
Light Poster
43 posts since Oct 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Aviplo
Light Poster
43 posts since Oct 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Netcode
Veteran Poster
1,049 posts since Jun 2009
Reputation Points: 33 [?]
Q&As Helped to Solve: 86 [?]
Skill Endorsements: 8 [?]
 
0
 

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.

Member Avatar
Aviplo
Light Poster
43 posts since Oct 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Pgmer
Practically a Posting Shark
881 posts since Apr 2008
Reputation Points: 50 [?]
Q&As Helped to Solve: 158 [?]
Skill Endorsements: 3 [?]
Featured
 
0
 

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

Member Avatar
Netcode
Veteran Poster
1,049 posts since Jun 2009
Reputation Points: 33 [?]
Q&As Helped to Solve: 86 [?]
Skill Endorsements: 8 [?]
 
0
 

Aviplo, you're welcome.

Member Avatar
Aviplo
Light Poster
43 posts since Oct 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Netcode
Veteran Poster
1,049 posts since Jun 2009
Reputation Points: 33 [?]
Q&As Helped to Solve: 86 [?]
Skill Endorsements: 8 [?]
 
0
 

"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

Member Avatar
Aviplo
Light Poster
43 posts since Oct 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Aviplo
Light Poster
43 posts since Oct 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Aviplo
Light Poster
43 posts since Oct 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

@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

Member Avatar
Netcode
Veteran Poster
1,049 posts since Jun 2009
Reputation Points: 33 [?]
Q&As Helped to Solve: 86 [?]
Skill Endorsements: 8 [?]
 
0
 

you may want to see this

Member Avatar
Pgmer
Practically a Posting Shark
881 posts since Apr 2008
Reputation Points: 50 [?]
Q&As Helped to Solve: 158 [?]
Skill Endorsements: 3 [?]
Featured
 
0
 

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.

Member Avatar
Aviplo
Light Poster
43 posts since Oct 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
Netcode
Veteran Poster
1,049 posts since Jun 2009
Reputation Points: 33 [?]
Q&As Helped to Solve: 86 [?]
Skill Endorsements: 8 [?]
 
0
 
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.

Member Avatar
Aviplo
Light Poster
43 posts since Oct 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Got it
Thanks you Thank you Thank you...

Question Answered as of 2 Years Ago by Netcode and Pgmer
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article