Comand Line Tools to Export SQL Server Query to CSV
Tags: sql, database, productivity, tooling
Published: 2023-05-01 10:00:00

## 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](https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility). Once you have `sqlcmd` installed, you can begin using it by opening the command prompt or PowerShell and entering the following command:

```powershell
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

```powershell
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.

```powershell
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.

```powershell
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:

```sql
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

```powershell
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.

```powershell
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, 😉