ASP.NET

How to import Data from Excel to SQL Server Express RSS

8 replies

Last post Sep 16, 2010 05:23 AM by suresh dasari

‹ Previous Thread|Next Thread ›

- Reply
Hisham Alsay...

0 Points

44 Posts

How to import Data from Excel to SQL Server Express

Sep 24, 2007 10:55 AM|LINK
Hi,
please any one is expert in Data Base help me, i would like to import two Data columns from Excel file 2003 instead of two columns in a table in Sql Server Express 2005 , How Can i do that? and what is the way if I have Sql Developer 2005, thank you .

Kind Regards
Hisham Alsayed
Software Developer
DATA MANAGEMENT SYSTEM
Email:\\Hisham.Alsayed@gmail.com
- Reply
PardeepBogra

Member

170 Points

64 Posts

Re: How to import Data from Excel to SQL Server Express

Sep 24, 2007 03:13 PM|LINK

Your query is not clear.. you wish to copy data from Excel to SQL Server Express or You wish to read Excel data from ASP.Net application:

If you want to know, how to read Excel data in ASP.Net Application, visit the follocing web link:

http://weblogs.asp.net/scottgu/archive/2006/05/29/Reading_2F00_Writing-Excel-Spreadsheets-with-ADO.NET.aspx

reading Excel files
- Reply
dstpierre

Member

8 Points

6 Posts
Re: How to import Data from Excel to SQL Server Express

Sep 24, 2007 03:31 PM|LINK
Hi,

You may try this SQL fonction OPENROWSET, suppose you have an Excel file in the C:\ drive of the SQL Server database, and the data is on a Worksheet called Sheet1 and your two columns are called Column1 and Column2.
```
INSERT INTO MyTable
```
```
SELECT Column1, Column2
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\MyTable.xls',
                'SELECT * FROM [Sheet1$]')
```
```
 
```
```
The first row of your Excel file should coutains the colums names.
```
```
 Hope this answer your question. 
```
```
Dominic 
```
- Reply
Hisham Alsay...

0 Points

44 Posts

Re: How to import Data from Excel to SQL Server Express

Sep 24, 2007 10:18 PM|LINK

hi, exactly i would like to copy two columns of data from Excel file instead of two columns of data in sql express,thank u

Kind Regards
Hisham Alsayed
Software Developer
DATA MANAGEMENT SYSTEM
Email:\\Hisham.Alsayed@gmail.com
- Reply
usimsek

Member

22 Points

6 Posts

Re: How to import Data from Excel to SQL Server Express

Jan 08, 2008 03:13 PM|LINK

Hello,

I was looking for the same answer and i achieved that using a script like below :

INSERT INTO [dbo].[MyTable] ( [Column1 ], [Column2] )

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\MyTable.xls',

'SELECT * FROM [Sheet1$]')

But, you must provide mytable with machin coloumns and Ad Hoc Distributed Queries must be enabled.

Best codes,

Umut SIMSEK

UMUT ŞİMŞEK
Sr. Software Developer
MCSD.NET, Electronics Engineer
Calik Group - Information Technologies

Yazılım ve Sistem Yöneticisi
Elektronik Mühendisi
Çalık Holding / Bilgi Sistemleri
Tel : 0 212 454 46 46 Dahili : 4616
usimsek@calik.com
- Reply
tech99

Member

2 Points

1 Post

Re: How to import Data from Excel to SQL Server Express

Apr 26, 2008 11:01 PM|LINK

Hello,
I was trying to do similar thing using Sql statement. But I got an error. like belowQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
I didn't find any solution in book online how to turn it on....

Thanks
- Reply
usimsek

Member

22 Points

6 Posts

Re: How to import Data from Excel to SQL Server Express

May 08, 2008 10:58 AM|LINK

Hello,

Yes. I saw that error message when i was trying to read from my pc while the SQL server was another server. I solved that problem copying the file to the server... Or maybe you can show the full path considering the network path.

UMUT ŞİMŞEK
Sr. Software Developer
MCSD.NET, Electronics Engineer
Calik Group - Information Technologies

Yazılım ve Sistem Yöneticisi
Elektronik Mühendisi
Çalık Holding / Bilgi Sistemleri
Tel : 0 212 454 46 46 Dahili : 4616
usimsek@calik.com
- Reply
aquilegia

Member

7 Points

2 Posts
Re: How to import Data from Excel to SQL Server Express

Sep 01, 2008 02:04 AM|LINK
Hi,

doubt this will be current for you anymore, but might help someone in future. I'm using SQL Server Express 2005 - I needed to alter some advanced options in order to run the statement. See below (first statement shows all possible options).

SELECT * FROM sys.configurations
ORDER BY name ;
GO
```
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
```
```
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO 
```
- Reply
suresh dasar...

Contributor

3606 Points

743 Posts

Re: How to import Data from Excel to SQL Server Express

Sep 16, 2010 05:23 AM|LINK

Here is the link which is helpful for your requirement

http://aspdotnet-suresh.blogspot.com/2010/09/import-data-from-excel-to-sql-database.html

My Blog

Please "Mark as Answer" If post helps you