Exporting Reports from SSMS the Easy Way (With Headers!)
Do you ever need to query up some data, manipulate it, maybe clean it up, and then export it to a specific directory so that it can be viewed in a spreadsheet?
I do ALL THE TIME! I am often tasked with creating new reports for different departments with specifically requested fields, to be generated at specified time intervals, and placed in a particular network folder where the department is able to access the report.
Querying and manipulating the data is usually a different process from report to report, but the export process is almost always identical. For that reason, I created a stored procedure that “magically” does the entire export process for me. I just pass in some parameters such as the desired name of the report and the path to the report’s destination and Voila! Let me show you what I whipped up. (Note: I use the bcp utility and execute it via xp_cmdshell in SSMS. To execute this system stored procedure, elevated permissions are required. For more information on the bcp utility please check out Microsoft’s documentation page https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver16)
For the purposes of this example I will be extracting the SQL code from the stored procedure and going through each section to show you how it works and why I included it.
The First Section: Declaring Variables
In this first section the variables needed for exporting the file are declared and set. Let’s go through them.
- @serverName > The name of the server where the database you will be querying data from is located, e.g. Test-Server
- @dbName > The name of the database where the table you will be querying data from is located, e.g. ExampleDatabase
- @tableName > The name of the table you will be querying data from e.g. #temp_example (There is a reason this is a temp table, we’ll come back to that)
- @headerFileName > The name of the exported data’s header file, e.g. headers.csv
- @sqlCommand > This variable will be set multiple times throughout the script, it is used to pass commands to the Windows command shell
- @filePath > The path to where the exported files will be placed, e.g. C:\Users\user\documents (I export to network folders frequently and must use the absolute path)
- @delimiter > The delimiter to be used in the exported report files, e.g. | (pipe)
- @reportName > The name of the final exported report, e.g. Example-Report.csv
- @dataFileName > The name of the exported data’s data file, e.g. exported_data.csv
The Second Section: Creating the Header File
In this second section a command is passed into the command shell that will output a csv file with only the desired data’s headers. Then a new line is added after the headers. Let’s check it out piece by piece.
Here the first bcp command is assembled before being passed to the command shell via xp_cmdshell. This is the resulting output of @sqlCommand:
bcp “SELECT Column_Name FROM ExampleDatabase.information_schema.columns where TABLE_NAME IN (‘#temp_example’)” queryout “C:\Users\user\documents\headers.csv” -S Test-Server -c -t “|” -T -r “|”
- The quotes are one of the tougher things to get right when passing in these commands, that’s why I find it extremely helpful to output the command using a Print statement so I can see exactly what is passed to the command shell. For instance, when setting the @sqlCommand variable the command needs to be wrapped in quotes for SQL to designate it as a string. Those quotes need to be closed and reopened before and after every variable is used. Then the actual SQL query in the command itself needs to be wrapped in quotes, and the table name used for the IN statement needs to be wrapped in quotes as well. The file path and delimiter also need to be wrapped in quotes. As you can see, there are lots of quote characters.
There are a couple of switches included at the end of the command, here is a an explanation of why they are used:
- -c > character format (required)
- -t > field terminator > | (pipe — not actually needed in this particular statement since the query is technically only returning one field)
- -T > trusted connection to the passed in server using integrated security
- -r > row terminator > | (specifying pipe replaces the newline character and acts as a field delimiter once exported. Instead of the headers being exported in one column as one field they are exported in a row with pipe delimiters. Perfect!)
Next, the second command just adds a newline character to the end of of the exported headers file. This is so that the headers and data nicely merge later on. This is the resulting output of @addNewLine:
echo. >> “C:\Users\user\documents\headers.csv”
The Third Section: Creating the Data File
In the third section the second bcp command is assembled before being passed to the command shell via xp_cmdshell. This is the resulting output of @sqlCommand:
bcp ExampleDatabase..#temp_example out “C:\Users\user\documents\exported_data.csv” -S Test-Server -c -t “|” -T
I want to point out the difference between out & queryout in the bcp command at this point. If you are outputting an entire table you would use out, but if your are outputting a query for particular fields then you would use queryout. This difference is displayed in the image above.
Most of the time when I am utilizing this stored procedure to export a report I am using a temp table. Why? Because when I create a custom report it usually ends up as a table-value function so that parameters can be passed in and only particular fields can be queried if desired. The data from the queried function is put into a temp table and exported and then the temp table is dropped. It usually looks something like the image below and is placed in a Job so that I can schedule it to run at certain time intervals.
The Fourth Section: Merging the Header & Data Files
In the fourth section the copy command is utilized to merge the two files. This creates a new file with both the headers and the data. This is the resulting output of @sqlCommand:
copy /b “C:\Users\user\documents\headers.csv” + “C:\Users\user\documents\exported_data.csv” “C:\Users\user\documents\#temp_example_2022–08–30.csv”
After that, the header and data files are deleted by utilizing the del command.
This is the resulting output of @deleteCommand:
del “C:\Users\user\documents\exported_data.csv”
del “C:\Users\user\documents\headers.csv”
The Fifth Section: Deleting Previous Version & Renaming the Report
This fifth section will delete a previous version of the report if it exists. This is not always necessary but comes in handy if a department just wants an updated report dropped into a folder on a daily or weekly basis and does not care about the prior versions. Otherwise, I usually add the date the report was created to the end of the filename and then this section never does anything unless the report is generated within the same day.
Here the if exist and del command are utilized to check if the prior version exists and then deletes it. This is the resulting output of @removePrevious:
if exist “C:\Users\user\documents\Example_Report.csv” del “C:\Users\user\documents\Example_Report.csv”
Lastly, the final report is renamed with the rename command.
This is the resulting output of @renameCommand:
rename “C:\Users\user\documents\#temp_example_2022–08–30.csv” Example_Report.csv
That’s it! Pretty Straightforward as long as you have a general understanding of SQL and the Windows Command Prompt. I hope this article helps you out in some way, shape, or form when it comes to exporting data out of SSMS.
Prefer Excel files (.xlsx) over CSV files? Let me know! Maybe next time I’ll show off a PowerShell script I use to convert the report from .csv to .xlsx.
Thank you for reading! Any feedback is appreciated!
