473,376 Members | 1,009 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,376 software developers and data experts.

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 43798
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Rathtap | last post by:
I want to set up the import of data from an excel file into SQL Server from my ASP.net (C#) application. The user will select the file that needs importing. I want to use DTS to do this. I was...
10
by: Niklas | last post by:
Hi Before I start coding I need to be sure that nobody else has not done it yet and that I can use it. I need an import utility which import data from Excel to a database or some object in...
0
by: jayfeb29 | last post by:
Can any one help me with importing excel data into sql server using ado.net and c#
4
by: pcoder | last post by:
i need to import Excel data into Mysql database. How can i do it. Is there any utility or any script can do it. Thanks in advance.
0
by: jwmaiden | last post by:
I tried importing Excel data using the guidelines in this thread: http://www.thescripts.com/forum/thread124113.html When I tried the code, the program worked until the...
0
by: levis123 | last post by:
hi, anybody know how to import excel data into sql server using asp.net (vb.net). i've read some articles in the internet, however, try to implement it seems...still have problems...any1 who solved...
0
by: ghazal01978 | last post by:
hi all I need some help how to import excel data into sql server using vb6? I have a sub , and used that but for many fieldes had problem , although those fields had text format data , vb...
0
by: prashantdixit | last post by:
Hi, I have beent trying importing Excel data with one column containing PDF/JPEG file name to access 2007. I have a Excel file with few columns One of the columns named as "Reference". The...
4
by: colintis | last post by:
Before asking the question I've been digging a hole that is deep enough to bury myself in google....Most of the results are simply importing the excel file to table with the same table structure. ...
4
by: apssiva | last post by:
Hi, i tiring to insert excel data into mysql using php. 1. i saved the Excel data as a CSV file. 2. i use below code to retrieve data. $fcontents = file('./ite_APS.csv');...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.