Simple way to export SQL Server data to Text Files

By:   |   Comments (24)   |   Related: 1 | 2 | 3 | 4 | More > Import and Export


Problem

Since the transition from DTS to SSIS, I have found myself looking for alternative ways to do simple tasks such as exporting data from SQL Server into text files. SSIS is a far more powerful tool than DTS, but I generally try to stay away from it for simple operations.  With the combination of a few T-SQL commands this tip shows you a simple way to export data to text files.

Solution

One of the ways I have worked around this is by using bcp with xp_cmdshell. It's fast, easy and I can integrate it right into my code.

The first thing you have to do is make sure xp_cmdshell is enabled. You can do this in one of two ways.

1. You can use sp_configure and execute the following script.

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE 

2. You can use the Surface Area Configuration Tool.

Select Surface Area Configuration for Features and check the Enable xp_cmdshell checkbox.

surface area configuration

Now that you have xp_cmdshell enabled you are ready to export your files.

Here is a sample command that will export the results of the sysfiles table to a comma delimited file called bcptest.txt. Just copy and paste this into a query window and execute the query.

EXEC xp_cmdshell 'bcp "SELECT * FROM sysfiles" queryout "C:\bcptest.txt" -T -c -t,' 

Note: BCP is a command line utility and xp_cmdshell is only required to use it within a SQL Batch.

This is the output for the above command when run in the "master" database.

notepad

The parameters that were used are:

  • The queryout option allows you to specify a query to export. This could be as simple as the query we have are as complicated as you want.  You can also create a view and select the data from a view.
  • The file name where the results will be stored is placed after the queryout option.
  • The -T parameter specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. You could use -P (password) and -U (user) if you want to use a SQL Server login.
  • The -c specifies the character dataype will be used for each field.
  • The -t parameter allows you to specify a field delimiter. The character after -t will be used to separate the data fields. If -t is removed, tab will be used as the default delimiter.

Another parameter you may need to use is -S to specify the server name.  If you have a named instance, you will need to use this parameter.  Here is an example connecting to server "DEVELOP" and instance "DEV1".

EXEC xp_cmdshell 'bcp "select name, type_desc, create_date from sys.objects" queryout "C:\bcptest2.txt" -T -SDEVELOP\DEV1 -c -t,' 

Also be aware that the case of the parameter such as -t and-T makes a difference, so make sure you have correct case for the parameter you are using.  You can find more options for BCP here:  http://msdn.microsoft.com/en-us/library/ms162802.aspx

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ken Simmons Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Wednesday, December 6, 2017 - 2:42:57 PM - George Gibbs Back To Top (73691)

I tried the code but received this message. Does that mean my ODBC Driver is incompatible? 

SQLState = S1000, NativeError = 0

Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file

NULL

 


Friday, February 19, 2016 - 4:47:53 PM - DAVID POWELL Back To Top (40735)

Exporting To Text Files - With Field Headers. Automated in a script ?

 

dp

 


Thursday, March 12, 2015 - 8:57:45 AM - Carlos Back To Top (36507)

 

Hello, I was wondering if someone could help me here.  I am sure that this will be simple to many of you.. But, im new to SQL.. I did the below query and it works just fine from SSMS/MGT Studio .. But, I want to run the query and at the same time write out the results to a local or network location.. How can I acomplished this?  Please if you dont want explaining it using my query below i would really appreciate it.. Thanks, all.

 

DROP

 

 

 

 

 

 

TABLEdbo.BakerTaylorFTPLog

 

 

 

 

 

 

 

 

GO

 

 

 

 

 

 

 

---- Build Table

 

 

 

 

 

 

 

CREATE

 

 

 

 

 

 

TABLEdbo.BakerTaylorFTPLog

 

 

 

 

 

 

 

 

(

 

 

 

 

 

 

 

date

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

time

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

c_ip

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

cs_username

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

s_sitename

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

s_computername

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

s_ip

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

s_port

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

cs_method

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

cs_uri_stem

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

cs_uri_query

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

sc_status

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

sc_win32_status

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

sc_bytes

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

cs_bytes

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

time_taken

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

cs_version

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

cs_host

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

cs_User_Agent

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

cs_Cookie

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

cs_Referer

 

 

 

 

 

 

VARCHAR(MAX),

 

 

 

 

 

 

 

 

);

 

 

 

 

 

 

 

----Loading the FTP Log File for Baker-Taylor.com Server's into a Table..

 

 

 

 

 

 

 

 

 

BULK

 

 

 

 

 

 

INSERTdbo.BakerTaylorFTPLogFROM'U:\WINNT\system32\LogFiles\MSFTPSVC1\ex150310.log'WITH (FIELDTERMINATOR=' ',FIRSTROW= 5,ROWTERMINATOR='\n')

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SELECT

 

 

 

 

 

 

*FROMBakerTaylorFTPLog

 

 

 

 

 

 

 

 

WHERE

 

 

 

 

 

 

cs_username='8888888';

 


Saturday, October 11, 2014 - 10:56:04 AM - akshay Back To Top (34932)

Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
NULL

 

this is the output when i run this query

 

EXEC xp_cmdshell 'bcp "SELECT * FROM " dbo.data1 "d:\bcptest.txt" -T -c -t,'

 

but not copying data to text file

 


Monday, March 24, 2014 - 6:31:38 PM - chimpinano Back To Top (29861)
EXEC DATABASE_NAME..xp_cmdshell 'bcp "select ''id'' as id, ''detail'' as detail, ''creation_date'' as creation_date UNION ALL select convert(varchar,id), detail, convert(varchar,creation_date) from DATABASE_NAME.dbo.TABLE" queryout "C:\file.txt" -c -t, -r \n -T -S "ServerName"'
 

Friday, February 7, 2014 - 4:51:03 PM - Sam Mcgee Back To Top (29369)

Try this:

 

bcp [Database].[dbo].[tableName]  out  c:\test\name.txt -c -S PCPSRVCNV002\MSSQLSERVER2012 -U sa -P password

 

This work fine in command line

Thanks


Tuesday, October 22, 2013 - 4:24:18 AM - Roni Back To Top (27215)

I tried this code too, but the results appear as follow:

SQLState = 08001, NativeError = -1

Error = [Microsoft][SQL Server Native Client 10.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. 

SQLState = 08001, NativeError = -1

Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config

ured to allow remote connections. For more information see SQL Server Books Online.

SQLState = S1T00, NativeError = 0

Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired

NULL


Wednesday, July 24, 2013 - 3:36:27 AM - Maha Back To Top (25972)
I tried this code and got error output SQLState = 08001, NativeError = -1 Error = [Microsoft][SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. SQLState = HYT00, NativeError = 0 Error = [Microsoft][SQL Native Client]Login timeout expired SQLState = 08001, NativeError = -1 Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connecti ons. NULL

Wednesday, June 26, 2013 - 7:58:03 PM - Mark Childers Back To Top (25585)

Ken:  Thanks for posting.  This worked great and your instructions were accurate and concise.  I searched the entire internet and no one had an anwer as good and efficient as yours.  You're the man.  This will help me to put more automation into some scripts I am using.   


Wednesday, May 8, 2013 - 3:09:11 AM - Rajasekhar Pasalapudi Back To Top (23790)
Try the following syntax. Exec xp_cmdshell 'bcp "SELECT * FROM [TESTDB].[dbo].[MYTEST]" queryout "d:\creatFile.txt" -T -c' Include [DBName].[dbo].[tablename] and add Exec xp_cmdshell (if you are executing from SSMS) if above things didn't worked, check bcp enabled or not, if not try following command to enable xp_cmdshell then execute BCP command) here are the commands EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE Exec xp_cmdshell 'bcp "SELECT * FROM [TESTDB].[dbo].[MYTEST]" queryout "d:\creatFile.txt" -T -c' Note: Important thing is if you are exporting specific columns from the table then all the columns should be in a row (i.e. no carriage returns (enter) spaces won't work )

Thursday, April 4, 2013 - 5:27:59 PM - victor Back To Top (23174)

I need ur help. Im working with sql server 2012 express edition. I need to export results from a query into a txt file. I have a query in a .sql file. I just wanna open it and execute. How can i use bcp? I tried with:

bcp "select * from myTable" queryout d:\test.txt -c -T

And SQL server says

Incorrect syntax near 'queryout'

What am i doing wrong?

 

PD

Sorry for my english i speak spanish.

 


Tuesday, January 15, 2013 - 11:15:46 AM - Greg Robidoux Back To Top (21461)

@KaptKos - yes you could copy and paste the data from SSMS to a text file, but if you have a large data set instead of selecting the data twice you could write out directly to a text file which will be much faster than using copy and paste.


Tuesday, January 15, 2013 - 10:59:13 AM - KaptKos Back To Top (21460)

Why not just perform the query in Mgt Studio and then export it or cut and paste into a text file?  DUH!?!?!


Tuesday, January 15, 2013 - 9:23:41 AM - Orlando Colamatteo Back To Top (21457)

I wish Microsoft would implement this suggestion so we are not compelled to enable xp_cmdshell as a way to export data from T-SQL:

http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=224026

As a side note Microsfot recommends using the SQLCLR as a replacement for xp_cmdshell, which I can almost see the writing on the wall, will be deprecated in a future version of SQL Server.


Saturday, December 29, 2012 - 12:11:28 PM - KARTHIKEYAN S Back To Top (21186)

HI ALL

I HAVE ONE TXT FILE,ITS CONTINT 10 COLUMN .I WANT IMPORT 2 COLUMN IN SQL SERVER  WITH BULK INSERT ITS POSSIBLE.KINDLY HELP ME.

 

THANK YOU


Thursday, December 6, 2012 - 10:23:08 AM - Scott Coleman Back To Top (20782)

Sorry, I meant Results-to-File mode in the first line of my previous comment.


Thursday, December 6, 2012 - 10:22:14 AM - Scott Coleman Back To Top (20781)

The simplest way to export data to a text file is probably use Results-to-Text mode in management studio.

Scripts executed by SQLCMD can include ":OUT <filename>" commands to write to a file.

BCP is a very useful and powerful utility, but I wouldn't put it at the top of my "simple" list.

And you would have to have a better reason than simple file export before enabling xp_cmdshell.  If BCP is the preferred export method, the BCP command could be put in a SQL Agent job and then that could be invoked on demand.  Or a SQLCMD script could be used to run BCP with a "!!BCP ..." line, no xp_cmdshell required.


Tuesday, October 30, 2012 - 12:02:55 PM - Orlando Colamatteo Back To Top (20151)

I am surprised that not one mention was made about the risks of enabling xp_cmdshell, the privileges required to utilize it or  references on how to properly secure it.

A much safer and more robust option is to use SSIS for exporting data from a table to a file. Alternatively you could invoke bcp directly from a PowerShell script. There are other ways as well that do not involve enabling xp_cmdshell. Ideally we should find a way to do this work without ever enabling xp_cmdshell.


Friday, August 17, 2012 - 1:37:00 PM - Krishna Back To Top (19096)

Hello,

How do I extract data from SQL Server table with a hexadicimal delimiter.

In specific, I wanted to extract a table in a delimited file format with hex of '19' (Reall value would be: ^Y) as my delimiter.

Thanks,

Krishna


Thursday, May 3, 2012 - 3:01:41 AM - Theo Back To Top (17249)

we have designed few tables in the Sql server 2005, DoctorDetails as one of them. It has attributes (DoctorID, FirstName, LastName, Address, Phone_Num, Employment_Type, WardID and Specialization). so i am facing a trouble with a question that says "In a text file , store the details of all doctors who have managed a particular ward in the current month. Make use of the required Tools to perform the data transfer.


Wednesday, March 14, 2012 - 6:24:26 AM - Mike Back To Top (16380)

I'm having trouble with special characters. Do you have any idea on how to prevent a problem like this?

For example, when I open the resulting text file with Notepad2, I get € instead of Ç


Wednesday, October 5, 2011 - 11:35:54 AM - GlennC Back To Top (14777)

Excellent tip, well illustrated.  It worked the first time, thanks!


Wednesday, December 17, 2008 - 1:55:53 PM - Dr DBA Back To Top (2416)

Sort of works with SQL 2005 and SQL 2008


Tuesday, December 9, 2008 - 1:09:23 AM - mrhappi Back To Top (2371)

 http://www.sqlservercentral.com/scripts/Miscellaneous/31136/

 here's an stored proc that will export and script the object as well...