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.

11 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

  9. Smithg7 said

    There is visibly a bundle to realize about this. I assume you made certain nice points in features also. aedgecegkbdgddck

  10. sekhar said

    Hi brother,
    while executing below statement im getting below error

    declare @sql varchar(8000)

    SELECT @sql = ‘bcp “select * from MM_DEV_T_D2_P..app_case” queryout E:\files\files\1.txt -c -t’+'”|”‘+’ -T -S @@servername’

    exec master..xp_cmdshell @sql

    —–err msg output

    SQLState = 08001, NativeError = 53
    Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53].
    SQLState = 08001, NativeError = 53
    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.
    SQLState = S1T00, NativeError = 0
    Error = [Microsoft][SQL Native Client]Login timeout expired
    NULL

    plz help on this.

    • thiagsundar said

      Hi Brother, I think the issue is remote connectivity. Please start the sql server configuration manager (should be under start/microsoft sql server), under network configuration make sure tcp/ip and named pipes connections are enabled. If not then enable and restart sql server service. Also check if sql browser service is running in services.msc. Hope this helps!…

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: