Import Excel Data into SQL Server with VB.NET

[Guest]

Can someone provide me with an example with how to import data from an excel
file into SQL Server with VB.NET?

Thanks

 

[W.G. Ryan eMVP]

http://www.knowdotnet.com/articles/exceldatasource.html

However, I'd probably avoid this route, I'd create a DTS package and/or a
sql server job and have it do it, it'll be a lot faster.

 

[Paul Clement]

¤ Can someone provide me with an example with how to import data from an excel
¤ file into SQL Server with VB.NET?

See the following thread:

http://makeashorterlink.com/?N2494253B


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Guest]

Thanks Paul

I'm getting an exception error though that I cannot fix.

Do you mind checking my code?

Dim excelConnection As System.Data.OleDb.OleDbConnection = New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:/test.xls;Extended Properties=Excel 8.0;")
excelConnection.Open()

Dim excelCommand As New System.Data.oledb.OleDbCommand("INSERT
INTO [OBDC; Driver={SQL
Server};Server=(local);Database=FullDate;Trusted_Connection=yes].[Population]
SELECT * FROM [Population$];", excelConnection)

'Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT
INTO [ODBC;Driver={SQL
Server};Server=(local);Database=FullDate;Trusted_Connection­=yes].[Population] FROM [Population$];", excelConnection)

excelCommand.ExecuteNonQuery()
excelConnection.Close()

The exception error is:

Could not find installable ISAM

Thanks in advance.

Khalid

 

[Paul Clement]

¤ Thanks Paul
¤
¤ I'm getting an exception error though that I cannot fix.
¤
¤ Do you mind checking my code?
¤
¤ Dim excelConnection As System.Data.OleDb.OleDbConnection = New
¤ System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
¤ Source=c:/test.xls;Extended Properties=Excel 8.0;")
¤ excelConnection.Open()
¤
¤ Dim excelCommand As New System.Data.oledb.OleDbCommand("INSERT
¤ INTO [OBDC; Driver={SQL
¤ Server};Server=(local);Database=FullDate;Trusted_Connection=yes].[Population]
¤ SELECT * FROM [Population$];", excelConnection)
¤
¤ 'Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT
¤ INTO [ODBC;Driver={SQL
¤ Server};Server=(local);Database=FullDate;Trusted_Connection­=yes].[Population] FROM [Population$];", excelConnection)
¤
¤ excelCommand.ExecuteNonQuery()
¤ excelConnection.Close()
¤
¤ The exception error is:
¤
¤ Could not find installable ISAM
¤

I don't see a problem with the connection string for Excel. On what line of code does the error
occur?

I would also check the following Registry entry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

This entry should exist and the win32 entry should point to the following file:

C:\WINNT\System32\msexcl40.dll

Make certain that the above file exists.

If the Registry entry is incorrect or the file is missing then you'll need to install the Jet
database engine components:

http://support.microsoft.com/default.aspx?scid=kb;en-us;239114


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Guest]

Thanks Paul

I got it to work.

One more question

Do you know if there is a way to use an Update statement to overwrite
records in sql server with an excel file?

Thanks in advance

 

[Paul Clement]

¤ Thanks Paul
¤
¤ I got it to work.
¤
¤ One more question
¤
¤ Do you know if there is a way to use an Update statement to overwrite
¤ records in sql server with an excel file?

You can update existing rows but I don't know of any way to overwrite them. If you're doing this via
SQL then you would need to join on a unique index, such as a primary key field, or on multiple
columns.


Paul
~~~~
Microsoft MVP (Visual Basic)