Import Excel data into SQL Server

[shxrainz]

Hi guys,

how can I import excel data into SQL Server with vb.net?

[GaryMazzone]

A one time thing or to be done more then once?

[shxrainz]

Hi GaryMazzone,

Thanks for ur reply, I want to do it at one time..
I found out a solution by using sqlbulkcopy but i had some issue on how to define the data from the excel file to required field in sql because i been given an excel file without a header

here is my codes :

'Connect string to excel
Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties=""Excel 8.0;HDR=NO;"""

'connection to excel
Using connection As OleDbConnection = New OleDbConnection(excelConnectionString)

Dim myCommand As New OleDbCommand("Select * FROM [Sheet1$]", connection)
connection.Open()

'// Create DbDataReader to Data Worksheet
Using dr As OleDbDataReader = myCommand.ExecuteReader()
'// SQL Server Connection String
Dim sqlConnectionString As String = "data source=XPC010\SQLEXPRESS;initial catalog=Sabah;user id=sa;password=12345;persist security info=false"
'"Data Source=XPC010\SQLEXPRESS;id=sa;Password=12345 Initial Catalog=Sabah;Integrated Security=True"

'// Bulk Copy to SQL Server
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(sqlConnectionString)
bulkCopy.DestinationTableName = "dbo.Barcode"
bulkCopy.WriteToServer(dr)
End Using
End Using

End Using

Hope u can help me on this.
Thanks in advanced

[GaryMazzone]

The easiest way would be to not do it in VB but use SSIS package to import the data. Very quick and actually pretty easy to use.

[shxrainz]

really? but can u provide me the code for importing excel file one at a time using sqlcommand?

[GaryMazzone]

Treat Excel as a database.

1. Open a connection to the execl file (this will be an OLEDB connection).
2. Read the worksheet like a table in a database.
3. Use either a datatable/dataset or DataReader to perform step 2.
4. Close the connection to the Excel workbook
5. Open a connection to the SQL Server database.
6. Loop though the set created in step 2 and generate an Insert statement for the data going into the
SQL Server.
7. Close the connection to the SQL Server.

[gautamshaw]

Using con As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0 ; Data Source=database.xls ; Extended Properties=Excel 8.0")

            con.Open()

            Dim cmd As New OleDbCommand("select * from [sheet1$] where YourColumnName=@columnname", con)
            cmd.Parameters.AddWithValue("@columnname", TextBox1.Text)
            Dim dr As OleDbDataReader
            dr = cmd.ExecuteReader()

            dr.Read()

           
        End Using

this is the code that may help you to perofrm the first three steps,ok

now if you are working with excel then you might be knowing how to do the step 4.

and for steps 5 to 7......you have to do it yourself since i consider that you are not a beginner

Now,before posting some queries please check your code twice or thrice as i think there should not be any more question regarding this as gary have shown you the steps of doing it and i have given you an outline idea of how to implement these steps

[shxrainz]

Thanks dude..I got it..

[Baz_Singh]

Hi guys... just wondering is I can use any of this to load data in several excel files in a folder into a sql server 2000 table? All the data will be in the first sheets in the excel files and the table will be an existing table with the same columns etc.