Use BCP to create a CSV (comma delimited) file from a table.

1

June 3, 2021 by Kenneth Fisher

This is a pretty handy little tool in your arsenal. I’ve talked about using bcp to transfer data from one instance to another before and this is another really great use for bcp. If you haven’t used it before bcp stands for Bulk Copy Protocol and is a command line tool for transferring data in and out of SQL Server. In this case you can use this command to generate a csv file from DBName.SchemaName.TableName:

bcp DBName.SchemaName.TableName out Path\FileName.csv -S ServerName -T -c -t,

Pretty easy right? Here are a few of the details.

  • The flags are case sensative! -T and -t mean two completely different things.
  • You can include -d dbname to specify which database you are referencing. In this case I put a three part table name so I didn’t need it.
  • You can replace out with queryout and use a query instead of the TableName.
  • -T means use trusted authentication. You could use -U and -P to include a SQL Ids user name and password instead. You can’t use them both at the same time.
  • -t is the field terminator. I.e. if you used -t| you’ll get a pipe delimited file.
  • -c gives you a readable character output. The default is tab delimited which is why I have the -t,. The default row terminator is \r\n (carriage return, line feed) but can be changed using -r.

There are a ton of other flags and ways to use bcp. I recommend taking a look and at least giving yourself an overview of this highly useful export tool.

One thought on “Use BCP to create a CSV (comma delimited) file from a table.

  1. […] Kenneth Fisher shows how to use the bcp command to create a file from a table: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,537 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: