Solved

Output a SQL query to Excel

Posted on 2010-11-29
31
931 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
ID: 34232408
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
ID: 34232422
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
ID: 34232602
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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

Author Comment

by:dmanisit
ID: 34232784
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
ID: 34233158
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
ID: 34233262
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
ID: 34233281
EXEC Ntier_Training..xp_cmdshell 'sqlcmd -S pmdb01 -d Ntier_Training -Q "EXEC Get_Birthdays" -o "C:\output1.csv"'
0
 

Author Comment

by:dmanisit
ID: 34233488
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
ID: 34233514
Are there nulls for the columns that you're missing when you select the view from ssms?
0
 

Author Comment

by:dmanisit
ID: 34233531
Yes there are some nulls in the Patient_Address2
0
 

Author Comment

by:dmanisit
ID: 34233540
I meant Patient_street2
0
 
LVL 7

Expert Comment

by:rmm2001
ID: 34233634
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
ID: 34233735
this is the error:

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

Expert Comment

by:rmm2001
ID: 34233751
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
ID: 34233785
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
 
LVL 7

Expert Comment

by:rmm2001
ID: 34233863
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
ID: 34233901
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
ID: 34233938
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
ID: 34234013
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
ID: 34234057
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
ID: 34234162
Thank you for explaining, per your request opened in Notepad

Prim_Policy_Certificate_No Patient_Name  
0
 
LVL 7

Expert Comment

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

Author Comment

by:dmanisit
ID: 34234463
Where and how would I implement that? Sorry?
0
 
LVL 7

Accepted Solution

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

Author Comment

by:dmanisit
ID: 34238724
The results are:

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

Expert Comment

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

Author Comment

by:dmanisit
ID: 34239143
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
ID: 34239518
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
ID: 34241728
any ideas on why I am only still getting 2 columns?
0
 

Author Comment

by:dmanisit
ID: 34250163
?????
0
 

Author Closing Comment

by:dmanisit
ID: 34872195
thank you
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

713 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