Exporting SQL Server Results to CSV With bcp and sqlcmd

bcp is the fastest way to get rows out of SQL Server and onto disk, and it is the wrong tool for making a CSV file, which is the part nobody tells you on the first day. The first time you point it at a table you get a file with no header, no quotes and no escaping, and the first customer name with a comma in it splits into two columns. The speed is real. So is the missing half of the format. This article shows the command, then the four things you add around it so the output reads as a proper CSV: a header row, quoting, sane dates and an encoding a spreadsheet will accept. For the general problem of exporting from any engine, start with exporting query results to CSV on a schedule, and for the PostgreSQL and MySQL versions of this job see psql and the MySQL client.

What bcp does and does not do

bcp stands for bulk copy program. It moves rows between a table, or the result of a query, and a flat file, in either direction. Its design predates CSV as a convention, so it thinks in terms of a field terminator and a row terminator and nothing else. If a value contains the terminator, bcp writes it anyway, and the file is no longer parseable by anything that follows RFC 4180.

That gives a short list of things bcp will not do for you:

• It does not write a header row. The first line of the file is the first data row.

• It does not quote any field, so a comma, a double quote or a line break inside a value is written raw.

• It does not format values. A datetime is written in the default style for the connection, a money value may carry four decimals, and a bit is 0 or 1.

• It does not tell a NULL from an empty string. In character mode both come out as nothing between two terminators.

Everything else it does well. It streams, so memory stays flat on a 50 million row table, and it writes in batches without a client library getting in the way. The trade is that you do the formatting in SQL, where you can see it, rather than in a flag you have to remember.

The command

Here is a small table to export. Imagine dbo.orders with an integer id, a customer name that sometimes holds a comma, a date and a decimal total.

CREATE TABLE dbo.orders (
    order_id    int           NOT NULL PRIMARY KEY,
    customer    nvarchar(100) NOT NULL,
    placed_on   date          NOT NULL,
    total       decimal(10,2) NULL,
    note        nvarchar(200) NULL
);

The plain export of a whole table uses out:

bcp dbo.orders out orders.csv -S localhost -d shop -U sa -P 'change-me' -c -t, -r "\n" -u

-S is the server, -d the database, -U and -P the login, -c character mode, -t, a comma as the field terminator and -r "\n" a line feed as the row terminator. -u tells the tool to trust the server certificate. You need it with ODBC Driver 18, whose default is to encrypt and verify, when the instance runs on a self-signed certificate, which a development container always does. In production you want a real certificate and no -u. For a trusted Windows login, drop -U and -P and use -T.

The same command with queryout exports a query, which is what you want almost every time:

bcp "SELECT order_id, customer, placed_on, total, note FROM shop.dbo.orders ORDER BY order_id" \
    queryout orders.csv -S localhost -U sa -P 'change-me' -c -t, -r "\n" -u

With a query you name the database in the statement, since -d is optional there. The ordering matters more than it looks: a CSV that you will diff tomorrow should come out in the same order today, and bcp makes no promise about order without an ORDER BY.

Run against three rows, the file looks like this:

1001,Acme Ltd,2024-03-04,1234.50,
1002,Smith, Jones and Co,2024-03-05,89.90,rush order
1003,Dupont "Fils",2024-03-05,,gift wrap

Look at the second line. It has five fields on paper and six commas, and a reader will count six fields. The third line has bare quotes inside an unquoted field. Neither problem is bcp misbehaving. It did what it was asked and the format needs the rest.

Quote the fields in the query

The fix is to build the quoting into the SELECT. A text column is wrapped in double quotes, and any double quote inside it is doubled. That is exactly the rule in RFC 4180, and T-SQL can say it in one expression:

SELECT
    order_id,
    '"' + REPLACE(customer, '"', '""') + '"' AS customer,
    CONVERT(char(10), placed_on, 23) AS placed_on,
    total,
    '"' + REPLACE(ISNULL(note, ''), '"', '""') + '"' AS note
FROM shop.dbo.orders
ORDER BY order_id;

Two details are easy to get wrong. First, do not use QUOTENAME(customer, '"') for this. It was built for identifiers, it refuses input longer than 128 characters by returning NULL, and a NULL in the middle of the output becomes an empty field with no warning. Second, quote the text columns and leave the numeric ones alone. Quoting every column is legal, and some readers will then treat a number as a string, which you may not want.

After the change the file reads:

1001,"Acme Ltd",2024-03-04,1234.50,""
1002,"Smith, Jones and Co",2024-03-05,89.90,"rush order"
1003,"Dupont ""Fils""",2024-03-05,,"gift wrap"

Every line now has five fields to any CSV parser, and the embedded comma and embedded quotes survive. A value with a line break inside it is also fine, because the quotes protect it, although a reader that splits on lines first will still break. How CSV quoting works covers what each reader does with that case.

The header row

bcp has no flag for a header. There are three honest ways to get one.

The first is to write the header yourself and append the data. It is two commands, it is explicit, and it has no ordering trick to go wrong:

echo 'order_id,customer,placed_on,total,note' > orders.csv
bcp "SELECT ..." queryout orders.body -S localhost -U sa -P 'change-me' -c -t, -r "\n" -u
cat orders.body >> orders.csv
rm orders.body

On Windows the same idea works with echo and type. The cost is that the header is typed by hand, so a column added to the query needs a matching edit here. If that bothers you, generate the header from the metadata: SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('dbo.orders') ORDER BY column_id gives the column names for a plain table export.

The second way is a UNION ALL that puts a row of text first. It works only if every column is cast to a string and you force the header to sort first, for example with a literal sort key column that you then leave out. It keeps everything in one statement, and it makes the query ugly enough that the header and the column list drift apart the first time someone adds a column.

The third is to skip bcp for small results and use sqlcmd, which does print a header and can be told to separate by a comma:

sqlcmd -S localhost -U sa -P 'change-me' -C -d shop -W -s"," \
       -Q "SET NOCOUNT ON; SELECT order_id, customer FROM dbo.orders" -o orders.csv

-W trims trailing spaces, -s"," sets the separator and -C trusts the certificate. The result has a dashed line under the header, ----,--------, which you remove with sed '2d' or avoid with -h -1, which drops headers altogether. Quoting is still your job. I use sqlcmd for a few hundred rows that a person will read, and bcp for anything a program will load.

Dates, decimals and NULLs

A CSV that another system loads should have dates in an unambiguous form. CONVERT(char(10), placed_on, 23) gives 2024-03-04, and style 126 gives the ISO 8601 form 2024-03-04T10:15:30.000 for a datetime2. Style numbers are an old and odd corner of T-SQL, so keep a note of the two you use. The default output of bcp for a datetime is 2024-03-04 10:15:30.000, which is fine for most loaders and ambiguous for none, but it carries three digits of precision you may not want. See writing dates and booleans so other tools read them for the target formats.

Decimals come out with their declared scale, so decimal(10,2) writes 89.90. A float is written with up to 15 digits and may show 1E-05. If a column is a quantity that downstream code reads as a number, cast a float to decimal in the query rather than let the exponent through.

NULLs need a decision. In character mode bcp writes nothing for a NULL, so 1003,"Dupont ""Fils""",2024-03-05,,"gift wrap" is a row with a NULL total. That is usually the convention you want, because an unquoted empty field is conventionally a missing value. The trap is the text column, where ISNULL(note, '') turns a NULL into "". If you want NULL and empty to differ, write NULL as an empty field and the empty string as "":

CASE WHEN note IS NULL THEN '' ELSE '"' + REPLACE(note, '"', '""') + '"' END AS note

Whether the reader on the other end honors that difference depends on the reader. Handling NULL and empty strings in PostgreSQL shows one that does.

Encoding and line endings

Three switches decide the bytes. -c writes single-byte characters in the code page of the client, and anything outside that page turns into ?. -w writes Unicode as UTF-16 little-endian, which Excel opens and most CSV libraries do not guess. -C 65001 combined with -c writes UTF-8, and it is the switch you want. It is available from SQL Server 2016 on, and the Microsoft documentation lists it as a code page option of the tool.

bcp "SELECT ..." queryout orders.csv -S localhost -U sa -P 'change-me' -c -C 65001 -t, -r "\r\n" -u

bcp does not write a byte order mark. If the readers are Windows people double-clicking in Excel, you may need one; see the UTF-8 BOM in CSV files for when it helps. The row terminator \r\n is what RFC 4180 specifies and what Excel writes, while \n is what Linux tools prefer and every modern parser accepts. Line endings in CSV files explains why a mismatch shows up as a stray \r at the end of the last column.

Checking what you wrote

Before sending the file anywhere, look at it with something that is not SQL Server. Count the lines and the fields:

wc -l orders.csv
python3 -c "import csv,sys; r=list(csv.reader(open('orders.csv',newline='',encoding='utf-8'))); print(len(r), {len(x) for x in r})"

The second line prints the number of records and the set of field counts. A healthy export prints 4 {5} for three orders and a header. Any other set means a field with a raw terminator in it, and the line number of the first odd row is the one to fix in the query. Also check the byte count against what the table holds: SELECT COUNT(*) FROM dbo.orders should equal the line count minus one, unless some values contain quoted line breaks, in which case it is the parsed record count that must match.

When bcp is the wrong tool

Use something else when the result is small, when the consumer is a person, or when the data is hostile.

A few hundred rows are quicker through sqlcmd or PowerShell's Invoke-Sqlcmd | Export-Csv, which quotes every value and writes a header for you. It is slower on large results, because it builds objects, and it is a good trade at that size.

Text with arbitrary content, such as free-form notes, needs more than doubled quotes. Embedded NUL bytes and lone carriage returns in a column exported through bcp can break the line count, so run REPLACE(note, CHAR(13), ' ') for fields where you do not care about the original breaks, or clean the file afterwards.

For a table with hundreds of millions of rows, the serial single file is the limit, not the tool. Export in ranges, one bcp process per key range, and name the pieces so they sort. Exporting large tables to CSV covers the splitting.

If the destination is another SQL Server, skip CSV and use native format, bcp ... -n, which keeps types exactly and is smaller. CSV earns its place when the consumer is not SQL Server, and for loading one back in, BULK INSERT reads the file you have just written. For other engines, the same article series covers Oracle with SQLcl and SQLite. The primary reference for every option above is the bcp utility page on Microsoft Learn, and the sqlcmd utility page covers the alternative.