Import Excel Data into SQL Server with VB.NET

[khanif]

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.

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"khanif" <kh****@discussions.microsoft.com> wrote in message
news:28**********************************@microsof t.com...

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

Thanks

[Paul Clement]

On Mon, 6 Jun 2005 07:25:46 -0700, khanif <kh****@discussions.microsoft.com> wrote:

¤ 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)

[khanif]

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=Micros oft.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_C onnection=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_C onnectionÂ*=yes].[Population] FROM [Population$];", excelConnection)

excelCommand.ExecuteNonQuery()
excelConnection.Close()

The exception error is:

Could not find installable ISAM

Thanks in advance.

Khalid

"Paul Clement" wrote:

On Mon, 6 Jun 2005 07:25:46 -0700, khanif <kh****@discussions.microsoft.com> wrote:

¤ 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)

[Paul Clement]

On Mon, 6 Jun 2005 16:14:01 -0700, khanif <kh****@discussions.microsoft.com> wrote:

¤ 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=Micros oft.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_C onnection=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_C onnection*=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\Engi nes\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...b;en-us;239114
Paul
~~~~
Microsoft MVP (Visual Basic)

[khanif]

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" wrote:

On Mon, 6 Jun 2005 16:14:01 -0700, khanif <kh****@discussions.microsoft.com> wrote:

¤ 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=Micros oft.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_C onnection=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_C onnectionÂ*=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\Engi nes\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...b;en-us;239114
Paul
~~~~
Microsoft MVP (Visual Basic)

[Paul Clement]

On Wed, 8 Jun 2005 12:32:04 -0700, khanif <kh****@discussions.microsoft.com> wrote:

¤ 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)