Export COUNT(s) to 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 2000 Forums

SQL Server Development (2000)

Export COUNT(s) to Excel

Author

Topic

liffey
Yak Posting Veteran

58 Posts

Posted - 2005-07-25 : 08:42:39

Suggestions please!

I have a stored proc that runs several queries to count records in a db based on certain criteria. What I wish to do is export this to a spreadsheet where I can produce a graph. How would you suggest I do this. The resulting dataset from the query returns many tables. Can this be done in SQL or do I need to write some code to extract the count value for each table and write a csv file for subsequent import to Excel?

SELECT COUNT(table_2.JobNumber) AS Table_2_Count
FROM table_1 INNER JOIN
table_2 ON table_1.JobNumber = table_2.JobNumber
WHERE (table_1.Duedate > @StartDate) AND (table_1.Duedate < @EndDate) AND (table_2.Value_A = 1)

SELECT COUNT(table_3.JobNumber) AS Table_3_Count
FROM table_1 INNER JOIN
table_3 ON table_1.JobNumber = table_3.JobNumber
WHERE (table_1.Duedate > @StartDate) AND (table_1.Duedate < @EndDate) AND (table_3.Value_B = 1)

etc ...

-dw

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-25 : 08:55:23

You can count the records from each table and export the result to Excel
Refer this on how to export data to excel
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

liffey
Yak Posting Veteran

58 Posts

Posted - 2005-07-25 : 10:27:42

Thanks for the reply. Please excuse my lack of knowledge but this is what I have done:

Create C:\testing.xls
Column 1 (A1) = OrderID
Column 2 ((B1) = CustomerID

Run query in Query Analyzer:

Use Northwind
GO
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT OrderID, CustomerID FROM [Sheet1]') select OrderID, CustomerID from Orders
GO

I get the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

I have looked at MS help but am no wiser!

-dw

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-25 : 10:29:39

You need to replace the name Sheet1 by actual name you used in the Excel file

Madhivanan

Failing to plan is Planning to fail

liffey
Yak Posting Veteran

58 Posts

Posted - 2005-07-25 : 10:37:58

Sheet1 is the sheet name in the workbook C:\testing.xls. I tried changing it to Orders with the same result.

Is there a way I can test the connection to Excel? I suspect this may be the problem.

Declan

-dw

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-25 : 10:45:22

Which version of SQL Server are you using 7 or 2000 and which version of Excel 97 or 2000?

Madhivanan

Failing to plan is Planning to fail

liffey
Yak Posting Veteran

58 Posts

Posted - 2005-07-25 : 10:51:50

Excel 2003 SP1
MS SQL Server 2000 - 8.00.760 (Intel X86)

-dw

liffey
Yak Posting Veteran

58 Posts

Posted - 2005-07-25 : 10:57:33

Use Northwind
GO
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\testing.xls;',
'SELECT OrderID, CustomerID FROM [Sheet1$]') select OrderID, CustomerID from Orders
GO

This works! The difference is adding $ after the sheet name.
i.e. [Sheet1$] and not [Sheet1]

Thanks.

-dw

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-25 : 11:12:22

Yes thats correct

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources