Export from SQL server to Excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Export from SQL server to Excel

Author

Topic

benramz
Starting Member

6 Posts

Posted - 2007-12-31 : 17:00:55

Given the following SQL Query, can some one send me the correct syntax for exporting a single item of data to a specific cell on an excel spread sheet?

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\MyPath\MyExcel.xls;hdr=no','SELECT * FROM [Sheet$b18:b18]')
SELECT sum(income) FROM DATA_Daily_Income_Summary where daterecorded >= {ts '2007-11-05 23:00:00'}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-31 : 17:25:09

This isn't a working script, so I'm moving the thread. Not sure if 2000 or 2005 is being used here, so I'll just make an assumption of 2005.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

benramz
Starting Member

6 Posts

Posted - 2007-12-31 : 17:34:26

I am currently working on sql 2000

Sorry for the confusion

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-02 : 00:51:56

Make sure to read this fully
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

benramz
Starting Member

6 Posts

Posted - 2008-01-03 : 16:40:33

I have reviewed the information and did not find any clear reference that answers the question.

Simply. I have one item of data that is extracted from a sql 2000 database. I would like to use the OPENROWSET put the one item into a specific cell of an existing excel spread sheet. Is this possible or not?

I have included the following as the example usage of OPENROWSET.

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\MyPath\MyExcel.xls;hdr=no','SELECT * FROM [Sheet$b18:b18]')
SELECT sum(income) as DailyIncome FROM DATA_Daily_Income_Summary where daterecorded >= {ts '2007-11-05 23:00:00'}

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2008-01-03 : 18:21:08

HI Benramz

I had a similiar problem and reverted to using the Data Import feature (Data - Import External Data) in Excel to connect to my SQL Server and then write the Query in the Excel feature. With this feature you can easily specify which cell you want the data to and when you refresh the data set it will update to the values in your SQL table.

If it's feasible to use that option, then go for it! ;)

benramz
Starting Member

6 Posts

Posted - 2008-01-04 : 11:19:19

Thanks Dnf999

I found that posible solution yesterday as well. It is a feasible option, though I will have to figure out how to dynamically update the date range for the query in Excel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources