SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Export to Excel
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 30

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 01/23/2006 :  02:54:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by anglianthon

Hi, I'm new to SQL.

Is this used to export from SQL table to Excel?

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

What if I want to export result of a query (that was run in query analyzer) to Excel?

THank you


This is used to export data from SQL Server to EXCEL

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 02/28/2006 :  03:08:45  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
See if these are helpful
http://www.aspfaq.com/show.asp?id=2259
http://www.mvps.org/access/bugs/bugs0017.htm

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ziggy
Starting Member

1 Posts

Posted - 04/25/2006 :  19:50:15  Show Profile  Reply with Quote
Great post! I have been able to export successfully. One question though... If I want to run the script regularly is there something I can add to it to overwrite pre-existing data in the file rather than appending to the end of the file?

Thanks
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 04/26/2006 :  01:03:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
No. I think you cant overwrite the data in Excel. You can append it. Otherwise export it in other file; Delete Old file; Rename new file to have old name

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kanwarnet
Starting Member

7 Posts

Posted - 05/02/2006 :  13:18:22  Show Profile  Reply with Quote
Hi all
I am trying to import data from a excel file

Insert into _PATIENTS select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\svrsqlLive\Processing\PATIENTS.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

This is giving me the following error

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

The same Query on another SQL Server works fine
Both servers have same version(4.0.9025.0) of MSJET40.dll.

Thanks
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 05/03/2006 :  01:43:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Make sure the file path is correct. Also when you run that query the EXCEL file should be closed

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kanwarnet
Starting Member

7 Posts

Posted - 05/03/2006 :  09:02:46  Show Profile  Reply with Quote
hi Madhivanan
Thanks for replying,I am sure that file is not opened and path is also correct as it is working if i try it on another SQL server.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 05/03/2006 :  10:43:43  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Which version of EXCEL are you using?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kanwarnet
Starting Member

7 Posts

Posted - 05/03/2006 :  11:40:17  Show Profile  Reply with Quote
it is 9.0.2720
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 05/04/2006 :  04:26:17  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Is it EXCEL 97 or 2000?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kanwarnet
Starting Member

7 Posts

Posted - 05/04/2006 :  08:49:54  Show Profile  Reply with Quote
It is Excel 2000
Go to Top of Page

niraligdesai
Starting Member

USA
4 Posts

Posted - 05/05/2006 :  09:58:42  Show Profile  Send niraligdesai a Yahoo! Message  Reply with Quote
Hello! Madhivanan,

I need your help. I want to transfer data from Excel to SQL Server, and I have following code.

Dim myConnection As System.Data.SqlClient.SqlConnection
Dim myCommand As System.Data.SqlClient.SqlCommand
Dim SQLStmt As String
myConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("PeptideDBConn"))
myConnection.Open()

SQLStmt = "Insert into Imagetable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & ExcelFile.Value & ";HDR=YES','SELECT * FROM [Sheet2$]')"

myCommand = New System.Data.SqlClient.SqlCommand(SQLStmt, myConnection)
myCommand.ExecuteNonQuery()
myConnection.Close()

and I am getting following error.
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
I am using Excel 2002 and SQL Server 8.0. Can you please help me out here? I need to get done with this by Monday. Please Help me!!! I'll be very thankful to you.

Thanks,

Nirali Desai

quote:
Originally posted by madhivanan

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

Create an Excel file named testing having the headers same as that of table columns and use this query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')



Madhivanan

Failing to plan is Planning to fail



Nirali Desai
Go to Top of Page

niraligdesai
Starting Member

USA
4 Posts

Posted - 05/05/2006 :  10:02:17  Show Profile  Send niraligdesai a Yahoo! Message  Reply with Quote
Hello! Madhivanan,

I also have created linked server, but what I have to do next. and is it posible without linked server? Please help me....

Thanks,

Nirali Desai
quote:
Originally posted by madhivanan

Is it EXCEL 97 or 2000?

Madhivanan

Failing to plan is Planning to fail



Nirali Desai
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 05/05/2006 :  10:22:23  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
kanwarnet

Try adding EXCEL as Linked server and do query on that
In sql server help file read about sp_addlinkedserver

niraligdesai,
Read this and follow it
http://support.microsoft.com/default.aspx?scid=kb;EN-US;328569

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30187 Posts

Posted - 05/19/2006 :  06:42:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by MBeaudreau

Do you know if in addition to identifying the excel sheet name you could state which cell to start at?

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'Excel 5.0;HDR=No;IMEX=0;Database=\\seludt2135\c$\documents and settings\selupln\desktop\book2.xls', 'select * from [Sheet1$b35:q50]')
--where f1 <> 'a'

Edited by - SwePeso on 08/01/2006 02:18:07
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30187 Posts

Posted - 05/19/2006 :  06:43:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by MBeaudreau

I need to start reading the excel data from a certain point in the excel file. All rows starting at cell B35. If need be I could read the entire excel file, not use headers, search for the value in B35 that I want to start at by using 'WHERE F1 like' but I'd prefer not to.

thanks.
M



SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;HDR=No;IMEX=0;Database=\\seludt2135\c$\documents and settings\selupln\desktop\book2.xls', 'select * from [Sheet1$b35:q50]')
--where f1 <> 'a'
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 05/19/2006 :  09:21:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Read more about Named Ranges in EXCEL
http://support.microsoft.com/kb/257819

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

datagod
Starting Member

37 Posts

Posted - 05/24/2006 :  12:41:16  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

Make sure the file path is correct. Also when you run that query the EXCEL file should be closed





Madhivanan, you are a life saver...

---
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
---

I was scratching my head over the mysterious "80004005" error with no luck. Then I decided to read this entire thread and visit all KB articles mentioned. Still no luck. Then I saw this post.

It worked! Thanks!!

Edited by - datagod on 05/24/2006 12:42:01
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 05/25/2006 :  09:54:03  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Glad to know that It worked. Thanks for the feedback

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Senjaya
Starting Member

USA
2 Posts

Posted - 06/02/2006 :  16:30:36  Show Profile  Reply with Quote
Hi,
I want to know if I export from SQL to Excel 2003 - All the number become text and even I try to format the excel file first still did not help....I have a pivot table in Excel file to generate a graph and this text makes it imposible.
Go to Top of Page
Page: of 30 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000