Import data from Excel and write into SQL Server 1

[bartdedecker]

Hi,

I want to write a tool in VB.NET to import data from an Excel file and write it to a SQL-Server.

Does somebody has any experience with this topic?
What is the best way to do this?

Thx,

Bart

 

[Korach]

This is how you get the data from excel:

			Dim tmpDS As New DataSet
			Dim tmpAdapter As OleDbDataAdapter
			Dim tmpCon As OleDbConnection

			tmpCon = New System.Data.OleDb.OleDbConnection( _
			  "provider=Microsoft.Jet.OLEDB.4.0; " & _
			  "data source=" & XLSFileName & "; " & _
			  "Extended Properties=Excel 10.0;")

			tmpAdapter = New OleDbDataAdapter("select * from [sheet1]", tmpCon)

			Call tmpAdapter.Fill(tmpDS)

			tmpCon.Close()

I'm not sure about the "sheet1", maybe it should be "sheet$1

 

[bartdedecker]

To get te records from the Excel file, you have to use "Select * from [sheet1$]".
Thx for your useful tip.
But once I have my data in the DataAdapter...
Is it possible to write the DataAdapter at once to the SQL server?
I have already tried it, but it doesn't work. I use the Update() method. -> myAdapter.Update(rows)
What may be the problem?

 

[Korach]

Change the connection of the adapter to the sql connection. Set the InsertCommand to a sql text or stored procedure name. Maybe you'll have to set the update commmand or delete command too.
I think it is not a good way to do it. Use a new data adapter.