Export To a cell in Excel - SQL Server Forums

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 To a cell in Excel

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-05-02 : 12:20:13

Hi

I'm trying to automate a procedure where I export results from SQL to Excel. I've been successful in doing this for tables.

i.e.

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Concordance\DB Country Report.xls;', 
'SELECT Search_Country, [Document Type], Count FROM [Doc Type$]') 
select Search_Country, Doc_Type as 'Document Type', cast(count(*) as numeric) from DB1_Temp_Country_Search
group by Search_Country, Doc_Type

But how would I write it to export to a particularly cell?

I.e I would want the results of select count(*) from Table to go to cell reference C4 in an excel spreadsheet.

Thanks!!

sshelper
Posting Yak Master

216 Posts

Posted - 2007-05-02 : 12:31:59

You can issue an UPDATE statement to you Excel file as long as you have a way to reference the record/line you want to update. If you want to update cell C4, what you need is to know what column is assigned to column C and what's the primary key or unique identifier for row 4.

SQL Server Helper
http://www.sql-server-helper.com

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-05-02 : 12:37:22

would you be able to provide me with some sample code?

Thanks :)

sshelper
Posting Yak Master

216 Posts

Posted - 2007-05-02 : 13:18:54

Your query will look like this:

UPDATE A
SET [YourThirdColumn] = (SELECT COUNT(*) FROM YourTable)
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Concordance\DB Country Report.xls;','SELECT Search_Country, [Document Type], Count FROM [Doc Type$]') A
WHERE [Search_Country] = 'The value from your 4th row'

SQL Server Helper
http://www.sql-server-helper.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-03 : 06:27:20

Also refer
http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources