How do I send query output from mssql-cli on a Linux machine to a text file on that machine in CSV format?

Question

How do I send output from a database query executed in mssql-cli (the Microsoft SQL command line interface application) on a Linux machine to a text file on that machine in CSV format?

The query is to a database on another server. I am already able to connect to that server and get small query result sets back to my screen.

I want to get back much larger query result sets in CSV formatted files that are saved on the same Linux machine on which I've installed mssql-cli.

Answer 1

"mssql-cli" vs. "mssqlcli"

TLDR: If you want JSON output then skip mssql-cli and go for mssqlcli. You need a Windows install of Python3.9 and then run PS C:\> python -m pip install mssqlcli pyyaml==5.4.1. If you want nice a nice interactive SQL console, then try mssqlcli instead. You will have to decide for ONE of them, since they seem to break each other. mssqlcli seems to be abandoned and buggy beta. So use with care.

---

I'm expanding a little on the work of https://stackoverflow.com/a/55656483/4247268 above.

And also I'm deviating completely from the "How to do this on Linux?" question, since I'm doing all of this on python on Windows 10. -- Feel free to comment if you got it to work like this on Linux.

So there are two projects with very similar names: mssql-cli and mssqlcli. One has a hyphen, the other one has no hyphen.

• https://pypi.org/project/mssql-cli/ By Microsoft. Does NOT currently (2022-04-14) work with Python 3.10. Requires Python 3.9. So you can't use the Python3 that Microsoft store offers you. Because that defaults to 3.10. So I manually installed 3.9 from https://www.python.org/downloads/
• https://pypi.org/project/mssqlcli/ Sort-of abandoned project. The pypi website is a mess. It requires python3 -m pip install pyyaml==5.4.1 otherwise with the current v6.0 I got an error: TypeError: load() missing 1 required positional argument: 'Loader' in Google Colab

I had some trouble getting them to work on my Win10 machine in 2022. But eventually I managed to get both to work. But NOT BOTH AT ONCE. Setup was fiddly and unpleasant.

Microsoft's mssql-cli

For the Microsoft's mssql-cli this here in PowerShell worked:

PS C:\> python --version
Python 3.9.12
PS C:\>
PS C:\>
PS C:\> python -m pip install mssql-cli

This worked for me. Afterwards you will have this new command:

PS C:\> Get-Command mssql-cli.bat | Format-List


Name            : mssql-cli.bat
CommandType     : Application
Definition      : C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\Scripts\mssql-cli.bat
Extension       : .bat
Path            : C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\Scripts\mssql-cli.bat
FileVersionInfo : File:             C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\Scripts\mssql-cli.bat
                  InternalName:
                  OriginalFilename:
                  FileVersion:
                  FileDescription:
                  Product:
                  ProductVersion:
                  Debug:            False
                  Patched:          False
                  PreRelease:       False
                  PrivateBuild:     False
                  SpecialBuild:     False
                  Language:

Usage Help: mssql-cli.bat --help

Built-in usage help is decent:

PS C:\> mssql-cli.bat --help
usage: mssql-cli [-h] [-U] [-P] [-d] [-S] [-E] [-v] [--mssqlclirc] [--row-limit] [--less-chatty] [--auto-vertical-output] [-N] [-C] [-l] [-K] [-M] [-a] [-A] [-Q] [-i] [-o] [--enable-sqltoolsservice-logging] [--prompt]

Microsoft SQL Server CLI. Version 1.0.0

optional arguments:
  -h, --help            show this help message and exit
  -U , --username       Username to connect to the database
  -P , --password       If not supplied, defaults to value in environment variable MSSQL_CLI_PASSWORD.
  -d , --database       database name to connect to.
  -S , --server         SQL Server instance name or address.
  -E, --integrated      Use integrated authentication on windows.
  -v, --version         Version of mssql-cli.
  --mssqlclirc          Location of mssqlclirc config file.
  --row-limit           Set threshold for row limit prompt. Use 0 to disable prompt.
  --less-chatty         Skip intro on startup and goodbye on exit.
  --auto-vertical-output
                        Automatically switch to vertical output mode if the result is wider than the terminal width.
  -N, --encrypt         SQL Server uses SSL encryption for all data if the server has a certificate installed.
  -C, --trust-server-certificate
                        The channel will be encrypted while bypassing walking the certificate chain to validate trust.
  -l , --connect-timeout
                        Time in seconds to wait for a connection to the server before terminating request.
  -K , --application-intent
                        Declares the application workload type when connecting to a database in a SQL Server Availability Group.
  -M, --multi-subnet-failover
                        If application is connecting to AlwaysOn AG on different subnets, setting this provides faster detection and connection to currently active server.
  -a , --packet-size    Size in bytes of the network packets used to communicate with SQL Server.
  -A, --dac-connection  Connect to SQL Server using the dedicated administrator connection.
  -Q , --query          Executes a query outputting the results to stdout and exits.
  -i , --input_file     Specifies the file that contains a batch of SQL statements for processing.
  -o , --output_file    Specifies the file that receives output from a query.
  --enable-sqltoolsservice-logging
                        Enables diagnostic logging for the SqlToolsService.
  --prompt              Prompt format (Default: \d>

This has both an interactive and a non-interactive mode.

In non-interactive mode you just fire off the contents of a script via the --input_file parameter like so:

PS C:\> mssql-cli.bat --server db.example.com --username exampleuser --password examplepassword --database exampledatabase --input_file exampleinputfile.sql

+---------+-----------+
| count   | fruitname |
|---------+-----------|
| 30      | apples    |
+---------+-----------+
(1 rows affected)

The output is for human consumption only. Sadly.

I don't see an advantage of this of mssql-cli.bat command over the usual "Invoke-Sqlcmd" commandlet -- BTW: Invoke-Sqlcmd also exists for Linux: https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-manage-powershell-core

However: there is also INTERACTIVE mode. And this is really nice. You get very nice colors, command-line history, animated dropdowns as you type and tab-completion. All inside the PowerShell window.

They used to have an animated GIF to demo this directly on the GitHub page (https://github.com/dbcli/mssql-cli) -- but currently (2022-04-14) that image link is broken.

However this site here also has some animated images to demo the intellisense-like autocompletion feature: https://mssqlquery.com/using-python-mssql-cli-in-sql-server (archived here)

Unfortunately it does not work inside Mobaxterm's "xterm" console. -- Then it asks you to install it directly via the python inside mobaxterm. And I didn't manage to do that. (Non-interactive mode however works just fine inside Mobaxterm's window.)

mssqlcli (without the "-" hyphen)

Since I really wanted some machine-readable output of queries, Microsoft's mssql-cli didn't work for me.

So I tried mssqcli next. Nearly the same name as "mssql-cli". Just the hyphen is missing.

I installed it like this:

PS C:\> python -m pip install mssqlcli pyyaml==5.4.1

And afterwards you will have this new command:

PS C:\> Get-Command mssqlcli | Format-List


Name            : mssqlcli.exe
CommandType     : Application
Definition      : C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\Scripts\mssqlcli.exe
Extension       : .exe
Path            : C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\Scripts\mssqlcli.exe
FileVersionInfo : File:             C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\Scripts\mssqlcli.exe
                  InternalName:
                  OriginalFilename:
                  FileVersion:
                  FileDescription:
                  Product:
                  ProductVersion:
                  Debug:            False
                  Patched:          False
                  PreRelease:       False
                  PrivateBuild:     False
                  SpecialBuild:     False
                  Language:

Usage help: mssqlcli.exe --help

Usage help is very bare bones:

PS C:\> mssqlcli.exe --help
Usage: mssqlcli [OPTIONS] COMMAND [ARGS]...

  Placeholder Function for click group.

Options:
  --version                       Show the version and exit.
  -c, --config-file PATH          Override default config file location
                                  (default: ~/.config/pymssql.yml).
  -o, --output [csv|json|pretty]
  --help                          Show this message and exit.

Commands:
  query           Run a query against an MS-SQL Database.
  template-query  Run Jinga2 templated query on an MS-SQL Database

---

Sidenote: must choose one I'm not sure why, but installing mssqlcli breaks mssql-cli. Error:

PS C:\> mssql-cli.bat --server db.example.com --username exampleuser --password examplepassword --database exampledatabase --input_file exampleinputfile.sql
Traceback (most recent call last):
  File "C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\runpy.py", line 197, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\site-packages\mssqlcli\main.py", line 11, in <module>
    from mssqlcli.config import config_location
ImportError: cannot import name 'config_location' from 'mssqlcli.config' (C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\site-packages\mssqlcli\config.py)

If you want to unbreak it again, then reinstall like this:

PS C:\> python -m pip uninstall -y mssqlcli mssql-cli pyyaml

and then

PS C:\> python -m pip uninstall -y mssql-cli

So I didn't manage to have BOTH installed and working at the same time.

But nevermind, since I only want the json-output-capable of the two anyway: mssql-cli

End Sidenote

---

So let's move on to actually running the thing.

It seems to require db-connection details in yaml file like this:

PS C:\> cat .\my-local-mssqlcli.yml
---
username: "exampleuser"
password: "examplepassword"
database: "exampledatabase"
server: "db.example.com"

(!) WARNING: Update 2022-04-26: the "database" line seems to be ignored. It just defaults to SOME database on that server. I have no idea how to actually select the database.

And it seems to require the query in a file like this:

PS C:\> cat examplequery.sql
SELECT * from FRUITTABLE

And then you run it like this:

PS C:\> mssqlcli.exe --config-file my-local-mssqlcli.yml query examplequery.sql
C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\site-packages\mssqlcli\config.py:49: YAMLLoadWarning: calling yaml.load() without Loader=... is deprecated, as the default Loader is unsafe. Please read https://msg.pyyaml.org/load for full details.
  self.object = yaml.load(f.read())
+-------+-----------+
| count | fruitname |
+-------+-----------+
|   30  |  apples   |
+-------+-----------+

Yes, you will always get this warning about yaml.load().

There are three output options:

    --output [csv|json|pretty]

--output pretty

"pretty" is the default:

PS C:\> mssqlcli.exe --config-file my-local-mssqlcli.yml --output pretty query examplequery.sql
C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\site-packages\mssqlcli\config.py:49: YAMLLoadWarning: calling yaml.load() without Loader=... is deprecated, as the default Loader is unsafe. Please read https://msg.pyyaml.org/load for full details.
  self.object = yaml.load(f.read())
+-------+-----------+
| count | fruitname |
+-------+-----------+
|   30  |  apples   |
+-------+-----------+

--output csv

"csv" looks like this:

PS C:\> mssqlcli.exe --config-file my-local-mssqlcli.yml --output csv query examplequery.sql
C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\site-packages\mssqlcli\config.py:49: YAMLLoadWarning: calling yaml.load() without Loader=... is deprecated, as the default Loader is unsafe. Please read https://msg.pyyaml.org/load for full details.
  self.object = yaml.load(f.read())
count,fruitname
30,apples

--output json

But what I really wanted was "json":

PS C:\> mssqlcli.exe --config-file my-local-mssqlcli.yml --output json query examplequery.sql
C:\Users\yourwindowsuser\AppData\Local\Programs\Python\Python39\lib\site-packages\mssqlcli\config.py:49: YAMLLoadWarning: calling yaml.load() without Loader=... is deprecated, as the default Loader is unsafe. Please read https://msg.pyyaml.org/load for full details.
  self.object = yaml.load(f.read())
{
    "results": [
        {
            "count": 30,
            "fruitname": "apples"
        }
    ]
}

Notice that "count" is correctly displayed as a number type and "fruitname" is displayed as a quoted string type.

Answer 2

To complement @jvd10's answer, here are the possible formats at time of writing. I wouldn't expect them to change often.

# Table format. Possible values: psql, plain, simple, grid, fancy_grid, pipe,
# ascii, double, github, orgtbl, rst, mediawiki, html, latex, latex_booktabs,
# textile, moinmoin, jira, vertical, tsv, csv.
# Recommended: psql, fancy_grid and grid.
table_format = psql

mssql-cli config file uses table_format with arg --output, I expected output_format

Answer 3

mssql-cli uses a config file to control output formatting (and a lot of other things).

To get csv output just create a file at ~/.config/mssqlcli/config containing the following:

table_format = tsv 

The other options recognized are documented in the usage guide available in the main github project here: mssql-cli config options

Answer 4

Microsoft's mssql-cli does not have any such option.

mssql-cli --help output:

Usage: main.py [OPTIONS]
Options:
    -S, --server TEXT       SQL Server instance name or address.
    -U, --username TEXT     Username to connect to the database.
    -W, --password          Force password prompt.
    -E, --integrated        Use integrated authentication on windows.
    -v, --version           Version of mssql-cli.
    -d, --database TEXT     database name to connect to.
    --mssqlclirc TEXT       Location of mssqlclirc config file.
    --row-limit INTEGER     Set threshold for row limit prompt. Use 0 to disable
                            prompt.
    --less-chatty           Skip intro on startup and goodbye on exit.
    --auto-vertical-output  Automatically switch to vertical output mode if the
                            result is wider than the terminal width.
    --help                  Show this message and exit.

There is another, unrelated project called mssqlcli which does have that option.

~ [ mssqlcli --help Usage: mssqlcli [OPTIONS] COMMAND [ARGS]...

Options:
  --version  Show the version and exit.
  -c, --config-file PATH   Override default config file location
                           (default: ~/.config/pymssql.yml).
  -o, --output [json|csv|pretty]
  --help  Show this message and exit.

Commands:
  query           Run a query against an MS-SQL Database.
  template_query

~ [ mssqlcli query --help
Usage: mssqlcli query [OPTIONS] QUERY

Options:
  --help                   Show this message and exit.

~ [ mssqlcli template_query --help
Usage: mssqlcli template_query [OPTIONS] QUERY

Options:
  -v, --variable TEXT   Variable for substitution in template. ex:"-v
                        first_name:russell" to replace {{ first_name }}
  --help                Show this message and exit.

As you can see, with that tool, you can do something like mssqlcli -o csv query 'SELECT * FROM whatever' > big_query.csv . However, that is not the tool you asked about: Microsoft's mssql-cli.

Microsoft's mssql-cli documentation

Unrelated project's documentation