Output a SQL query to Excel

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

dmanisitAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rmm2001Connect With a Mentor Commented:
EXEC master..xp_cmdshell 'sqlcmd -S pmdb01 -d Ntier_Training -Q Get_Birthdays.sql -o "C:\output1.csv" -i
0
 
flakierCommented:
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
 
dmanisitAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
ggupta7Commented:
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
 
dmanisitAuthor Commented:
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
 
rmm2001Commented:
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
 
dmanisitAuthor Commented:
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
 
rmm2001Commented:
EXEC Ntier_Training..xp_cmdshell 'sqlcmd -S pmdb01 -d Ntier_Training -Q "EXEC Get_Birthdays" -o "C:\output1.csv"'
0
 
dmanisitAuthor Commented:
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
 
rmm2001Commented:
Are there nulls for the columns that you're missing when you select the view from ssms?
0
 
dmanisitAuthor Commented:
Yes there are some nulls in the Patient_Address2
0
 
dmanisitAuthor Commented:
I meant Patient_street2
0
 
rmm2001Commented:
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
 
dmanisitAuthor Commented:
this is the error:

Sqlcmd: '-c': Missing argument. Enter '-?' for help.
0
 
rmm2001Commented:
Odd just try this

EXEC master..xp_cmdshell 'sqlcmd -S pmdb01 -d Ntier_Training -Q BirthdayQuery.sql -o "C:\output1.csv" -N'
0
 
dmanisitAuthor Commented:
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
 
rmm2001Commented:
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
 
dmanisitAuthor Commented:
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
 
rmm2001Commented:
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
 
dmanisitAuthor Commented:
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
 
rmm2001Commented:
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
 
dmanisitAuthor Commented:
Thank you for explaining, per your request opened in Notepad

Prim_Policy_Certificate_No Patient_Name  
0
 
rmm2001Commented:
Did you try what @flakier said and put the -i into your sqlcmd command string yet?
0
 
dmanisitAuthor Commented:
Where and how would I implement that? Sorry?
0
 
dmanisitAuthor Commented:
The results are:

Sqlcmd: The i and the -Q/-q options are mutually exclusive.
0
 
rmm2001Commented:
Errr let's see if I can type today... capital "i"
0
 
dmanisitAuthor Commented:
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
 
rmm2001Commented:
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
 
dmanisitAuthor Commented:
any ideas on why I am only still getting 2 columns?
0
 
dmanisitAuthor Commented:
?????
0
 
dmanisitAuthor Commented:
thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.