Solved

Output a SQL query to Excel

Posted on 2010-11-29
31
885 Views
Last Modified: 2012-05-10
Hi all, I have written a simple Query to Query Birthdates for a given month. All of that works great. i would like to be able to output the given results to a CSV file. So this is 2 parts. I have been able to execute the Query successfully for the results i want, but i cant put stage 2 (Outputting a CSV file) together, although i am able to do these seperatley to some extent. I have created a view of just the data that I wanted, but when I am aonly able to output ALL of the data in that view, I am unsuccessful in outputing based on my Query. Below  is the Query also I have listed the code that I am using to output the CSV, it doesnt matter how I change it it failes other than *.

Thank you
EXEC master..xp_cmdshell 'sqlcmd -S pmdb01 -d Ntier_Training -Q "SELECT * FROM dbo.Birthdate" -o "C:\output1.csv"'

Open in new window

DECLARE @MonthNumber int

SET @MonthNumber = 12



SELECT Prim_Policy_Certificate_No, Patient_Name, Patient_Street1, Patient_Street2, Patient_City, Patient_State, Patient_Zip_Code, 

                      Prim_Policy_Carrier_Name, Patient_DOB

FROM PM.vwGenPatInfo

WHERE MONTH(Patient_DOB) = @MonthNumber

Open in new window

0
Comment
Question by:dmanisit
  • 17
  • 12
  • +1
31 Comments
 
LVL 3

Expert Comment

by:flakier
Comment Utility
I suggest you put your query into a .sql file and use the -i  option of sqlcmd.  The shell may be eating up characters silently.

I also suggest you run your script via a scheduled task (wrap it in a batch file as necessary) as it will be easier to troubleshoot permissions issues.
0
 

Author Comment

by:dmanisit
Comment Utility
I understand but I am not running into Permissions issues. I can output a file just fine and grab ALL results from the View I created. i need to output the file via the Query listed above.
0
 
LVL 3

Expert Comment

by:ggupta7
Comment Utility
The Best Kept Secret About SQL Query Analyzer



or how to place a query result (with the heading of the columns) into Excel spreadsheet.



How many times did you read "In Query Analyzer, there was no way to copy the heading of the columns from a Result Set, if the output was in grid"? Or you could read long and not working solution for this simple task.

Now, here are two methods, step by step, how to place a query result into Excel spreadsheet:

Method 1
Highlight, drag and drop a result set from the Results Pane into Excel spreadsheet. But wait - there is a little trick you should know before you can make this technique working.

1. Open an Excel spreadsheet where you will place the results.

Now, in the SQL Query Analyzer, we need to get Results in text!

2. Click Query from Menu bar
3. Click Results in Text

Make sure that you have “Tab delimited” option in Results Output Format dialog box!!!

4. Click Tools
5. Click Options
6. Click Results
7. Select “Tab delimited” option in “Results Output Format” window!!!!!

Once you setup “Tab delimited” option in Results Output Format dialog box you could keep it unchanged.

8. Click "Print column headers(*)".
9. Click OK.

The next step is to get select statement using the Object Browser. You should use Object Browser to get all tables and column names.

10. Right click on the table Customers in database Northwind.
11. Click Script Object to New Window As
12. Click Select. Now you will have a select statement in the Editor Pane. You may modify it. If you need to write join you can save select statement generated by Object Browser to clipboard and paste it to current open Editor Pane.
13. Press P5 to run your query
14. Click anywhere in the Results Pane
15. Press CTRL + A to highlight the result set
16. Drag and drop the result into Excel spreadsheet

Using this technique you can get into Excel spreadsheet the output of multiple queries in one shot. (For queries that you run on regular basis you should use a DTS package.)

Here are some extra steps you should perform in Excel spreadsheet:

17. Click 1 to highlight the first row – the header row
18. Click B (bolt) button
19. Click select all button to select the content of spreadsheet
20. Click Format from Menu bar
21. Click Column
22. Click AutoFit Selection
23. Save Excel spreadsheet


You may need to remove the number of rows affected message text from the bottom of the Excel spreadsheet.

(Although this process seems to be lengthy it should take few minutes to complete it first time! And after you master it – few seconds.)


Method 2
This simple method should save you time when you present your results in Excel spreadsheets. The SQL Query Analyzer can directly save the results in Excel spreadsheet. That means you do not need to copy and paste or drag and drop the results - The SQL Query Analyzer does it for you and all it does is for free.

1. Click Tools from the Menu bar
2. Click Options
3. In Options dialog box click Results tab
4. Select “Tab delimited” option in “Results Output Format” window
5. Select “Print column headers(*)”
6. Click OK
7. Click Query from the Menu bar
8. Click Results to File (later you can use the shortcut CTRL+ SHIFT+F)
9. Type in your query in Editor Pane or use the Object Browser to generate a query
10. Press F5 key to execute your query
11. You should get the Save Results dialog box
12. In File Name window type in the name of your output file. For example: Orders.xls. Make sure you provided the file extension (xls).
13. Click Save.

In the Results Pane you will get the number of rows in the result set, path to the output file and the size of the file.

14. Now, you can open your file using Windows Explorer.
15. Click 1 to highlight the first row – the header row
16. Click B (bolt) button.
17. Click Save.

If asked "Do you want to keep the format of the file" click No.

You can save your results using CSV (comma separated value) file format if no “comma” appears in any column. You can also save your results using “Custom Delimiter” option. In this case you may use Actual Tilda character “~” or pipe “|” as a delimiter.

If you need to schedule your query and place results into Excel spreadsheet you can find a solution in an excellent article "Using DTS to Generate and Email Excel Reports" by Joseph Sack.

Credits: to all my colleagues for their unique sense of humor and endless jokes. This keeps my creativity engine working.






or




http://www.eggheadcafe.com/software/aspnet/29713573/query-output-to-excel.aspx

or
http://www.databasejournal.com/features/mssql/article.php/1493901/Automate-Excel-Spreadsheets-From-SQL-Queries.htm

or
Good Morning

Try these steps:

1) Spool test.txt
Select partno, partname,uob from partmast
Spool off;
2) Open MS-Excel
3) Open the test.txt file in Excel.
4) A wizard will be opened with two options
Specify from where to start, i mean, specify row number in the option Start import at row and click next.
5) Use the break line as required and click finish.

I hope this will help you.

Have a pleasant time.

or



0
 

Author Comment

by:dmanisit
Comment Utility
Great articles but none really pertain to what I was asking. I want to combine my 2 code examples for the Query that I had built. I knew all of what was listed above. Once this code is written and works I am creating a stored procedure
0
 
LVL 7

Expert Comment

by:rmm2001
Comment Utility
Try what @flakier mentioned..

Create a .sql (BirthdayQuery.sql) file with:
DECLARE @MonthNumber int
SET @MonthNumber = 12

SELECT Prim_Policy_Certificate_No, Patient_Name, Patient_Street1, Patient_Street2, Patient_City, Patient_State, Patient_Zip_Code, 
                      Prim_Policy_Carrier_Name, Patient_DOB
FROM PM.vwGenPatInfo
WHERE MONTH(Patient_DOB) = @MonthNumber

Open in new window


Then call your command line
EXEC master..xp_cmdshell 'sqlcmd -S pmdb01 -d Ntier_Training -Q BirthdayQuery.sql -o "C:\output1.csv"'

Open in new window

0
 

Author Comment

by:dmanisit
Comment Utility
Ok, thank you. I did just that. I created a stored procedure called Get_Birthdays and saved it. I then called it listed below and got this error

Msg 2812       Level 16       State 62       Server PMDB01       Line 1
Could not find stored procedure 'Get_Birthdays.sql'.                        
 Image
EXEC Ntier_Training..xp_cmdshell 'sqlcmd -S pmdb01 -d Ntier_Training -Q Get_Birthdays.sql -o "C:\output1.csv"'

Open in new window

0
 
LVL 7

Expert Comment

by:rmm2001
Comment Utility
EXEC Ntier_Training..xp_cmdshell 'sqlcmd -S pmdb01 -d Ntier_Training -Q "EXEC Get_Birthdays" -o "C:\output1.csv"'
0
 

Author Comment

by:dmanisit
Comment Utility
You guys have been a great help, one last thing. It all seems to be working with the exception that my csv file only has 2 columns in it. #1 is the Primary_Policy_cert and the oter is the name, nothing else is there and here is my sp"

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[Get_Birthdays]
AS
DECLARE @MonthNumber int
SET @MonthNumber = 12

SELECT Prim_Policy_Certificate_No, Patient_Name, Patient_Street1, Patient_Street2, Patient_City, Patient_State, Patient_Zip_Code,
                      Prim_Policy_Carrier_Name, Patient_DOB
FROM PM.vwGenPatInfo
WHERE MONTH(Patient_DOB) = @MonthNumber
0
 
LVL 7

Expert Comment

by:rmm2001
Comment Utility
Are there nulls for the columns that you're missing when you select the view from ssms?
0
 

Author Comment

by:dmanisit
Comment Utility
Yes there are some nulls in the Patient_Address2
0
 

Author Comment

by:dmanisit
Comment Utility
I meant Patient_street2
0
 
LVL 7

Expert Comment

by:rmm2001
Comment Utility
Hmmmmm try executing it in ssms and see what you get. Do both output the results to a grid and to text. Do the column headings print in the file for all of the columns?
try this
EXEC master..xp_cmdshell 'sqlcmd -S pmdb01 -d Ntier_Training -Q BirthdayQuery.sql -o "C:\output1.csv" -c -t, -T -N'
0
 

Author Comment

by:dmanisit
Comment Utility
this is the error:

Sqlcmd: '-c': Missing argument. Enter '-?' for help.
0
 
LVL 7

Expert Comment

by:rmm2001
Comment Utility
Odd just try this

EXEC master..xp_cmdshell 'sqlcmd -S pmdb01 -d Ntier_Training -Q BirthdayQuery.sql -o "C:\output1.csv" -N'
0
 

Author Comment

by:dmanisit
Comment Utility
I agree it is weird, although this is the first time I have used these commands: Here is the error, also please note that the name of BirthdayQuery changed I named it Get_Birthdays:

Sqlcmd: 'N': Unknown Option. Enter '-?' for help.

Here is my command

EXEC master..xp_cmdshell 'sqlcmd -S pmdb01 -d Ntier_Training -Q Get_Birthdays -o "C:\output1.csv" -N'
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 7

Expert Comment

by:rmm2001
Comment Utility
Hmmm..sorry about the -N..I forgot that's old. Can you paste the output header on what you get when you run it? When I run it everything looks ok even though I have nulls in my columns
0
 

Author Comment

by:dmanisit
Comment Utility
Sure, this is my output header, but if you look in the csv file that is where you see:

Sqlcmd: 'N': Unknown Option. Enter '-?' for help.
 op
0
 
LVL 7

Expert Comment

by:rmm2001
Comment Utility
EXEC master..xp_cmdshell 'sqlcmd -S pmdb01 -d Ntier_Training -Q Get_Birthdays -o "C:\output1.csv"

^ and then see what the output is in the file. What's the header that prints out?
0
 

Author Comment

by:dmanisit
Comment Utility
Ok sorry for not knowing what you are referring too when you say "Whats the header that prints out"? Can you explain? And when I run this command I still am only getting the 2 fields as indicated before, although let me give you a snapshot with no personal data in in from the file.

 excel
0
 
LVL 7

Expert Comment

by:rmm2001
Comment Utility
Ahh sorry about that. The column headings. Can you open it in notepad or a text editor and see what comes over?

Right click on the output1.csv -> Open With... -> select Notepad (or something else). Then uncheck the "Set as default" checkbox that's checked on the bottom left corner of the screen.

Have you considered using SSIS to do this? It'd be much easier that sqlcmd I think to output into excel
0
 

Author Comment

by:dmanisit
Comment Utility
Thank you for explaining, per your request opened in Notepad

Prim_Policy_Certificate_No Patient_Name  
0
 
LVL 7

Expert Comment

by:rmm2001
Comment Utility
Did you try what @flakier said and put the -i into your sqlcmd command string yet?
0
 

Author Comment

by:dmanisit
Comment Utility
Where and how would I implement that? Sorry?
0
 
LVL 7

Accepted Solution

by:
rmm2001 earned 500 total points
Comment Utility
EXEC master..xp_cmdshell 'sqlcmd -S pmdb01 -d Ntier_Training -Q Get_Birthdays.sql -o "C:\output1.csv" -i
0
 

Author Comment

by:dmanisit
Comment Utility
The results are:

Sqlcmd: The i and the -Q/-q options are mutually exclusive.
0
 
LVL 7

Expert Comment

by:rmm2001
Comment Utility
Errr let's see if I can type today... capital "i"
0
 

Author Comment

by:dmanisit
Comment Utility
mm2001, so you have answered the question in this thread. Would you like for me to award you the points and open a new question?
0
 
LVL 7

Expert Comment

by:rmm2001
Comment Utility
If you still can't get it to work then leave the question open. But if you have another question go ahead and post it in a new thread
0
 

Author Comment

by:dmanisit
Comment Utility
any ideas on why I am only still getting 2 columns?
0
 

Author Comment

by:dmanisit
Comment Utility
?????
0
 

Author Closing Comment

by:dmanisit
Comment Utility
thank you
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now