Export Data to Text File

EXPORT SQL SERVER DATA TO TEXT FILE

It is real simple to export data to text file from SQL Server. There are quite few ways that this task can be accomplished.

1

OPENROWSET

2

BCP

3

SSIS

First we will se how to use the “openrowset”. You can either export data from the output of a “select” statement or from variables as static values with in the proc.

insert openrowset (‘Microsoft.Jet.OLEDB.4.0′, ‘Text;Database=C:\Temp’,filename#txt)

Select col_1 from table_a

insert openrowset (‘Microsoft.Jet.OLEDB.4.0′, ‘Text;Database=C:\Temp’,filename#txt)

values(@col_1)

The caveat is that you need to have the text file available with the same name exactly in that particular folder with the column headers that you are going to export. This is required because SQL Server open the text file as remote data source.

The second way would be to use the bcp utility within the proc. First let’s see how to implement this.

declare @sql varchar(8000)

SELECT @sql = ‘bcp “select * from talbe_a” queryout c:\temp\filename.txt -c -t, -T -S <servername>’

exec master..xp_cmdshell @sql

By doing this way you do not need the requirement of file existence in that folder as “BCP” creates the file. But wait; there is a caveat in this way too, that the Sql Server instance needs to be set with few options. This can be implemented using Sql Server surface configuration or by using sp_configure proc.

EXEC sp_configure ‘show advanced options’, 1

GO

reconfigure

EXEC sp_configure ‘xp_cmdshell’, 1

GO

reconfigure

The third way would be to create a DTS package using Sql Server Integration Service.

I hope this would help to some degree in getting the data exported to text file.

8 Comments »

  1. [...] Export Data to Text File [...]

  2. san said

    hi

    insert openrowset (‘Microsoft.Jet.OLEDB.4.0′, ‘Text;Database=C:\Documents and Settings\sarathn.LGXTST\Desktop’,test#txt)

    Select item, on_hand_qty as onhand from location_inventory where on_hand_qty = ’5′

    i used this statement . it gives error
    “Server: Msg 213, Level 16, State 5, Line 1
    Insert Error: Column name or number of supplied values does not match table definition.”

    the text contians two header words
    item [tab] onhand

    pls help

  3. san said

    k now its exporting properly in the sql analyzer but if code it in vb it says path is invalid even though it is valid and working in analzer.

    pls revert

  4. shanu said

    How do I export data from sql to text file in ANSI format with | as row delimiter

  5. thiagsundar said

    Hi Shanu,
    You can use anyone of the following bcp command to export to ascii file.

    For pipe separated file then from your sql server management studio run this

    declare @sql varchar(8000)

    SELECT @sql = ‘bcp “select * from [dbname]..[table name]” queryout c:\temp\mytest.txt -c -t’+’”|”‘+’ -T -S [server name]‘

    exec master..xp_cmdshell @sql

    For comma separated file then from your sql server management studio run this

    declare @sql varchar(8000)

    SELECT @sql = ‘bcp “select * from [dbname]..[table name]” queryout c:\temp\mytest.txt -c -t, -T -S [server name]‘

    exec master..xp_cmdshell @sql

    If you are using copy paste then make sure your quotes within the commands are okay. Otherwise remove then and add it again.

  6. Thanks for writing this article. I used the 2nd/bcp method and it worked great!

  7. ekokoe said

    thanks bro, its worked :)

  8. Jeff said

    Howdy… You can avoid enabling the command shell if you can put the BCP command as a job step that uses the Operating System (CmdExec). We have security protocol that prevents us from doing it and this is the workaround

RSS feed for comments on this post · TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: