blog
Friday, March 17, 2023

Comand Line Tools to Export SQL Server Query to CSV

database
windows
tutorial

Introduction

Microsoft SQL Server is a widely used relational database management system that allows organizations to efficiently store and manage their data. To manage SQL Server, many people use SQL Server Management Studio (SSMS), which provides a graphical user interface (GUI) for running SQL queries, exporting data, managing roles, and performing other related tasks. However, some users may prefer command-line interface (CLI) tools for simple and repetitive tasks, rather than relying on the GUI.

If you find yourself frequently exporting query results to CSV using SSMS, it may not be the most efficient way to accomplish this task. This is where we need command-line interface (CLI) tools, as they allow you to create scripts and automate repetitive tasks using a something like scheduler or cron job. To export SQL Server query results using CLI tools, there are several options available, including sqlcmd, Invoke-Sqlcmd, and bcp. We will take a closer look at each of these tools and provide instructions on how to use them for exporting SQL Server query results.

SQLCMD

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files through command prompt, Query Editor in SQLCMD mode, Windows script file, and SQL Server Agent job.

sqlcmd comes pre-installed with SQL Server, so if you’ve installed SQL Server on your machine, you should already have it. However, if you need to install sqlcmd without installing SQL Server, you can do so by downloading and installing the Microsoft Command Line Utilities for SQL Server, which can be found here. Once you have sqlcmd installed, you can begin using it by opening the command prompt or PowerShell and entering the following command:

sqlcmd -S <sqlserver instance> -d <database> -u <db_username> -p <password> -o <output name> -Q <query> -s";" -w 700 -W -h-1
  • -u <username> -p <password>, use username and password authentication. To use Windows Authentication, remove these options and change to -E to use Trusted Connection
  • -w 700, specifies the screen width for output (between 8 and 65536), when the output line exceeds the specified column width, it wraps on to the next line.
  • -s";", set delimiter to ;, you can change it to , or any characters you want.
  • -W, removes trailing spaces from a colums.
  • -h-1, removes the header columns.

Here’s an example of sqlcmd command

sqlcmd -S .\SQLEXPRESS -d StudentDB -E -Q "SELECT * FROM students" -o "student.csv" -u "myusername" -p "mystrongpassword" -s"," -w 700

And here the csv files exported from that command:

name;class
----;-----
Jhon Doe;11
Jhonny;11
Jonathan;12

(3 rows affected)

This command exports the results of the SELECT * FROM students query to a CSV file named student.csv. However, the output CSV file generated by sqlcmd may not be valid for some use cases. For example, at the end of file we see the number of affected rows, which we don’t need it in csv files. To remove (xx rows affected), just add SET NOCOUNT ON; before the query (SET NOCOUNT ON;SELECT * FROM students).

Additionally, the column header has a dashed line between the column name and the data, which can cause issues when trying to import the CSV file into other applications. To remove it, you can manually edit the output or add -h -1 option to remove the column names and the dashed line, but you must add it manual or by using additional query.

The next things is the width size of the query result. When you have a super long row you must specify the width for the output to prevent it from warpping to the next line. It will become problem when you have a long enough data on each row.

BCP (Bulk Copy Program)

The bulk copy program utility (bcp) bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files

The next tool is bcp or bulk copy program, this tool also comes with SQL Server or Microsft Command Line Utilities For SQL Server.

bcp <query> queryout <filename> -S <sqlinstance> -d <databasename> -c -t ";" -U <username> -P <password>
  • -c, remove prompt to set datatype each field, and use char instead.
  • -t ";", set delimiter to ;.
  • -u <username> -p <password>, use username and password authentication. To use Windows Authentication, remove these options and change to -T to use Trusted Connection.

Here’s an example of bcp to get all data from students table to csv.

bcp "SELECT * FROM students" queryout "students.csv" -d "StudentsDB" -S .\MSSQL -T -t ";" -c

And here’s the output

Jhon Doe;11
Jhonny;11
Jonathan;12

In my opinion, the output generated by bcp is better than the output generated by sqlcmd. However, one downside of bcp is that it does not include the column names in the first row of the exported file. To include the column names in the file, you need to manually export the table names to a separate file and then combine it with the query result. To get all the columns from a table, you can use a query below:

select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='your_table_names'

Invoke-Sqlcmd (powershell)

The Invoke-Sqlcmd cmdlet runs a script containing the languages and commands supported by the SQL Server SQLCMD utility.

Invoke-Sqlcmd requires PowerShell not CMD. To export the query to csv, you need to use both Invoke-Sqlcmd and Export-Csv. Before continue, you must install this PowerShell Module. Open PowerShell as admininistrator and enter the following command

Install-Module -Name SqlServer

ALternatively, you can install it for the current user only by Install-Module SQLServer -Scope CurrentUser, which eliminate the need for elevated permissions.

Invoke-Sqlcmd -Query <query> -ServerInstance <server> -u <username> -p <password> |
Export-Csv -Path <output_path> -NoTypeInformation -Delimiter ";"

By default, Invoke-Sqlcmd use Windows Authentication (e.g MACHINE\USER), to use username and password authentication, just change it to -u <username> -p <password>.

Result from Invoke-Sqlcmd will be piped to Export-Csv. In my opinion, result from this method is better compared to the previous two methods, because the output has column name and not limiting by screen width. However, one downside is just all columns from the result are set to char data type, but this is not a big problem for me.

Conclusion

To export SQL Server Query in command line,powershell or cmd, we can use sqlcmd, Invoke-Sqlcmd, and bcp, and will give the best result from each command with some drawbacks. From the 3 command, I prefer using Invoke-Sqlcmd because it does give result with column name automatically, not limiting by screnn width, but this methid will set all the column to char.

To export SQL Server query from command line, either PowerShell or CMD, we have many tools, three of them are sqlcmd, Invoke-Sqlcmd, and bcp. Each of these commands has its own advantages and disadvantages. I personally prefer using Invoke-Sqlcmd because it automatically includes column names and does not limit the results by screen width. However, it does have a limitation of setting all columns to char.

If I have time to make an automated script, I would prefer to write it manually using Python btw, 😉