469,270 Members | 1,117 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

Import Excel Data into SQL Server with VB.NET

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

Thanks

Jul 21 '05 #1
6 43405
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

Jul 21 '05 #2
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)
Jul 21 '05 #3
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)

Jul 21 '05 #4
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)
Jul 21 '05 #5
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)

Jul 21 '05 #6
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)
Jul 21 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by Niklas | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.