Solved

Export Query data to text file

Posted on 2013-01-25
26
1,160 Views
Last Modified: 2013-02-01
Does anyone have a statement that will query the data base and export the data AND the column headers to a text file / csv ? I have xp_cmdshell on if I need it. I can't seem to google up anything that helps.
0
Comment
Question by:GD_GRAY
  • 12
  • 8
  • 2
  • +2
26 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Using SQLCMD in a SQL query:

exec master..xp_cmdshell N'sqlcmd -E -Q"select * from server_name.dba_name.schema_name.table_name" -h-1 -s"," -W -o"c:\output.txt"', no_output



--using BCP with column names and you can do both XLS and/or CSV - just change the extension:
bcp "select 'ProductID', 'Name', 'ProductNumber' union all select convert(varchar(10), ProductID), Name, ProductNumber from AdventureWorks2008R2.Production.Product" queryout "c:\temp\product.xls" -c -T


--SQL 2005 only
EXEC master..sp_makewebtask
      @outputfile = 'c:\testing.xls',
      @query = 'Select * from dbName.schemaName.tableName',
      @colheaders =1,
      @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'



--exec SP
exec master..xp_cmdshell N'sqlcmd -E -Q"set nocount on; exec mc..my_test;" -s"," -W -o"c:\output.txt"', no_output

--for TEXT fields use BCP!!!

http://msdn.microsoft.com/en-us/library/aa174646(SQL.80).aspx

exec master..xp_cmdshell N'sqlcmd -E -Q"bcp "set nocount on; select FICHIER from server.database.dbo.table WHERE pk1= 154 AND pk2= 14071" queryout "c:\outputTEST.txt" -N -q -T >nul', no_output
exec master..xp_cmdshell N'bcp "set nocount on; SELECT top 10 ClientID,MerchantID,Name,Company,Email,Address1,City,State,Country from mc..clients with (nolock);" queryout "c:\outputTEST.txt" -N -q -T >nul'--, no_output
0
 

Author Comment

by:GD_GRAY
Comment Utility
It must be me, I cant get any of them to work ? ?
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
This should work in SQL 2005 if that's what you are using:

--SQL 2005 only
EXEC master..sp_makewebtask
      @outputfile = 'c:\testing.csv',
      @query = 'Select * from dbName.schemaName.tableName',
      @colheaders =1,
      @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

Only thing is that C:\testing.cvs is ON the server where you run the statement not the client from where you run the query.

This below must be ok as well - what errors are you getting?

exec master..xp_cmdshell N'sqlcmd -E -Q"select * from server_name.dba_name.schema_name.table_name" -h-1 -s"," -W -o"c:\output.csv"'
0
 

Author Comment

by:GD_GRAY
Comment Utility
Error:   Incorrect syntax near '-'. from

exec master..xp_cmdshell N'sqlcmd -E -Q"select * from server_name.dba_name.schema_name.table_name" -h-1 -s"," -W -o"c:\output.csv"'

But I have MSSQL 2008
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
Did the
exec master..xp_cmdshell N'sqlcmd -E -Q"select * from server_name.dba_name.schema_name.table_name" -h-1 -s"," -W -o"c:\output.txt"', no_output

Open in new window


fail?
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Hi

How often do you need this to run?

In SSMS select grid, right click, select copy include headers, paste in excel, saves as csv

Hath
  David
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
There are two commands that can write to disk.

BCP and SQLCMD (replaces OCP)

They are available from the Windows command line as well. So, you could write a CMD or BAT file to execute if you wanted.

But, as a once off, then best to use SSMS as David has suggested above.

You can also use SSIS - there is an import/export wizard and including column names is a simple check box. You can save that package and re-use as needed. See: http://msdn.microsoft.com/en-us/library/ms141209.aspx and http://msdn.microsoft.com/en-us/library/ms140052.aspx  (and a lot on the left hand side of the page to read).

But back to T-SQL...

The column headers are held in a table information_schema.columns so you can export those fist and then append the data (use the copy command in command line to concatenate files). Or, use a stored procedure to do that. Advantage is you can individually format columns depending on data types and make a more compliant export.

There are some other options like CLR and OA, can even open a spreadsheet and update that (ie copy a template to a "work" area, populate, copy to distribution area).

Anyway, a few ideas for you to consider, and I'll see if I can ressurect a generic export SP (written before relying on SSIS).
0
 

Author Comment

by:GD_GRAY
Comment Utility
Jimpen:  yes it fail's some what, The  output.txt file is printed but the only thing in it is the ERROR :: Incorrect syntax near '-' So it's 50/50...

Mark:   The script will be ran every night so that the text file is in the folder each morning before the staff clocks in. Once I can get it to work I am going to have to turn it into a SP and set a job up to run it.

David: sorry the end user will not have access to the sql SSIS interface.

Thank all of you for your help, If I have to I can make a work around and make another table with the column headers and than UNION the two but I cant even get anything to send the query to a text file.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
You dont have to make another table... There is one.

select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'your table name goes here' order by ordinal_position

Open in new window


And you can export that directly to disk and then copy/concatenate the "header" and the "data" into a new file.

SSIS jobs can be scheduled, the users dont necessaily need access to SSIS if it is going to be an overnight job...
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
So, the export into c:\ee\test_header.csv for the above information schema for a table named [your_table] in database [your_DB] on a server [your_server\your_sql] the the command would be :

exec xp_cmdshell 'bcp "declare @c varchar(8000);select @c = isnull(@c+'','','''')+column_name from your_DB.information_schema.columns where table_name=''your_table'' order by ordinal_position; select @c;" queryout "C:\ee\test_header.csv" -Syour_server\your_sql -c -T -CACP'

Open in new window

0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
The SQLCMD context is below.

The first step is to get the query down that you know it works consistently.

Then follow up with the output via the SQLCMD context.

--------------------------------------------------
Microsoft (R) SQL Server Command Line Tool
Version 10.50.1600.1 NT x64
Copyright (c) Microsoft Corporation.  All rights reserved.

usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, enviroment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Well, still here...

The sqlcmd can export headers, but it also underlines those headers, you could remove them I guess using some commandline line magic (so long as the content doesnt contain dashes)

the -h param controls headers, and rather than -h-1 (which suppresses) just leave it out to get default headers e.g.

exec xp_cmdshell N'sqlcmd -E -Q"select * from your_db..your_table" -s"," -Syour_server\your_sql -W -o"c:\output.txt"'

Open in new window


and then that command line magic, something like (at the cmd prompt)

find /v "-----,----" c:\output.txt >> c:\output.csv

Open in new window


trouble is, it doesnt handle embedded delimiter characters within a string.

Or, I can try to dig out that sp...
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Well, that was fun... Couldnt find it (relatively new machine totally new databases), so decided to write a little procedure.

1st step is to create the procedure, then test interactively, then use xp_cmdshell to export from the stored procedure.

Because we are using bcp then characters like " (double quote) upset the command string, so, have enable the two digit representation as an option. char(34) happens to be the double quote, so, in the BCP command we use 34 as the encapsulation character.

Any way, here goes :

4 parameters
1) table or view name to export
2) delimiter between columns
3) encapsulation character for string type columns
4) replace character (like commas in case you dont want encapsulation)

params 2,3,4 can be either the single character like , " ,
Or the two digit representation of that character as mentioned above

create procedure usp_format_csv (@table_name varchar(200), @delimiter_char varchar(2), @encapsulate_char varchar(2), @replace_char varchar(2))
as
begin

  declare @delimiter varchar(1)
  declare @encapsulate varchar(1)
  declare @replace varchar(1)

  select @delimiter   = case when isnumeric(@delimiter_char) = 1 and len(@delimiter_char) = 2 then char(@delimiter_char) else isnull(@delimiter_char,'') end
  select @encapsulate = case when isnumeric(@encapsulate_char) = 1 and len(@encapsulate_char) = 2 then char(@encapsulate_char) else isnull(@encapsulate_char,'') end
  select @replace     = case when isnumeric(@replace_char) = 1 and len(@replace_char) = 2 then char(@replace_char) else isnull(@replace_char,'') end

 
--Generate column names for header row
--Normally only include data_type not in ('ntext','text','image')

   declare @c varchar(max)
   select @c = isnull(@c+'+'''+@delimiter+'''+','')+''''+@encapsulate+'''+'''+column_name+'''+'''+@encapsulate+'''' from information_schema.columns where table_name=@table_name order by ordinal_position 

--Generate select column names

   declare @s varchar(max)
   select @s = isnull(@s+'+'''+@delimiter+'''+','') + 
          case when data_type in ('char','nchar','varchar','nvarchar','text','ntext','date','time','smalldatetime','datetime','datetime2','datetimeoffset','XML') 
--               and  len(@encapsulate) > 0
               then  ''''+@encapsulate+'''+'
               else ''
          end +
          case when len(@replace) > 0 then 'replace( ' 
               else ''
          end +
          case when data_type like '%date%' or data_type like '%time%' then 'convert(varchar,['+column_name+'],120)'
               when data_type like '%text%' or data_type = 'XML' then 'convert(varchar(max),['+column_name+'])'
               when data_type like 'n%char' then 'convert(varchar(max),['+column_name+'])'
               when data_type like '%char%' then '['+column_name+']'
               else 'convert(varchar(255),['+column_name+'])'
          end +
          case when len(@replace) > 0 then ','''+@replace+''','''')'
               else ''
          end +
          case when data_type in ('char','nchar','varchar','nvarchar','text','ntext','date','time','smalldatetime','datetime','datetime2','datetimeoffset','XML') 
 --              and  len(@encapsulate) > 0
               then  '+'''+@encapsulate+''''
               else ''
          end 
   from information_schema.columns where table_name=@table_name order by ordinal_position		        

   set @s = replace('Select ' +@c+ ' as str union all select '+@s+ ' from ' +@table_name,'+''''+','+')

   exec (@s)

end

Open in new window


and now to test the formatter with a table

exec usp_format_csv 'tbl_EE_Export_TESTER',',','34',''

Open in new window


and finally to use it to export to a disk file

exec master..xp_cmdshell 'bcp "exec your_db..usp_export_csv ''tbl_EE_Export_TESTER'','','',''34'','',''" queryout "c:\output_test.csv" -Syour_server\your_sql -c -T -CACP'

Open in new window

0
 

Author Comment

by:GD_GRAY
Comment Utility
Mark,
Odd thing,

I setup the sp and ran the test and it works fine, now when I try and do the export it tells me " Could not find stored procedure 'usp_export_csv' "

The sp is in the database both in master and in the one I'm working with ??
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Could be your default database that you log into, you need to have it in the same DB as the table you are about to export (although I could add in a DB name as a param).

So, you could add in the -d param to your BCP command line.

e.g.  -Syour_server\your_sql -dyour_db -c
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
D'oh....


I changed the name from usp_export_csv to usp_format_csv  except in the BCP example used the old name. Sorry about that.
0
 

Author Comment

by:GD_GRAY
Comment Utility
My bad, I over thought that one. Now I get:

"Procedure or function 'usp_format_csv' expects parameter '@delimiter_char', which was not supplied."

I'm looking for the two digit representation on google but no luck so far. I assume if I find it I should place it

exec master..xp_cmdshell 'bcp "exec your_db..usp_export_csv ''tbl_EE_Export_TESTER'',''***HERE***'',''34'','',''" queryout "c:\output_test.csv" -Syour_server\your_sql -c -T -CACP'
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
The delimiter example is a comma already,

it is the second parameter, but, when you have single quotes within a string then you need to escape it with another single quote.

so the parameters in a SQL query window would normally look like :

'tbl_EE_EXPORT_TESTER', ',','34',''

or

@table_name='tbl_EE_EXPORT_TESTER',
@delimiter_char=',',
@encapsulate_char ='34',
@replace_char = ''

Now, within a string we need to escape the single quote with another so it becomes

''tbl_EE_EXPORT_TESTER'', '','',''34'',''''

The trap is that the delimiter is a comma so it is easy to get misaligned. In the above, it is the second comma that is the delimiter.

And the ascii code for a comma is 44

(you can do a select ascii(',') to get that numeric value)

so, the above string could also be passed as parameters like :

''tbl_EE_EXPORT_TESTER'', ''44'',''34'',''''

which makes it a little bit more obvious.

" exec your_db..usp_format_csv ''tbl_EE_Export_TESTER'',''44'',''34'','''' "
0
 

Author Comment

by:GD_GRAY
Comment Utility
Now I get: Error = [Microsoft][SQL Server Native Client 10.0]BCP host-files must contain at least one column

The table has 63 columns all with data
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Right, and possibly a NULL value in at least one column on each row ?

Reason being, it concatenates columns, and any column with a NULL will make a NULL.

The usp_format_csv procedure is not very robust at the moment (like I said I quickly created it and will do more work on it).

Does it work OK interactively ?

If it did, then we still have a small problem with the BCP command.
0
 

Author Comment

by:GD_GRAY
Comment Utility
"Does it work OK interactively ?  Yes after I ran an update to replace the null with 0 than it shows all the rows, I took out the 0 and it still works, but the exec BCP still see's no columns and gives the error.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
OK, version below is a more robust ...

So, lets go through the processes...

1) create or alter as per the code below

2) must be in the same database as the table

3) table cannot be a temp table

4) BCP command parameters :
4.a) general format = BCP "the query" queryout "output file" -params
4.b) "the query" = "exec your_db..usp_format_csv ''your_table_name'',''44'',''34'',''''"
4.c) "output file" = "c:\output.csv"  
4.d) parameters (case sensitive)
4.d.1) -S = the server name and instance e.g. -Syour_svr\your_sql
4.d.2) -d = the database name e.g. -dyour_db
4.d.3) -T = trusted connection otherwise use -Uusername and -Ppassword
4.d.4) -c = char mode
4.d.5) -C = code page ACP = Ansi windows e.g. -CACP

5) Output file must be a writable location for SQL Server Service account
6) The Query is a string starting and ending with double quote, and inside those, the procedure params use two single quotes (not a single double quote).

alter procedure usp_format_csv (@table_name varchar(200), @delimiter_char varchar(2), @encapsulate_char varchar(2), @replace_char varchar(2))
as
begin

  declare @delimiter varchar(1)
  declare @encapsulate varchar(1)
  declare @replace varchar(1)

  select @delimiter   = case when isnumeric(@delimiter_char) = 1 and len(@delimiter_char) = 2 then char(@delimiter_char) else isnull(@delimiter_char,'') end
  select @encapsulate = case when isnumeric(@encapsulate_char) = 1 and len(@encapsulate_char) = 2 then char(@encapsulate_char) else isnull(@encapsulate_char,'') end
  select @replace     = case when isnumeric(@replace_char) = 1 and len(@replace_char) = 2 then char(@replace_char) else isnull(@replace_char,'') end

 
--Generate column names for header row
--Normally only include data_type not in ('ntext','text','image')
   declare @c varchar(max)
   select @c = isnull(@c+'+'''+@delimiter+'''+','')+''''+@encapsulate+'''+'''+column_name+'''+'''+@encapsulate+'''' from information_schema.columns where table_name=@table_name order by ordinal_position 

--Generate select column names

   declare @s varchar(max)
   select @s = isnull(@s+'+'''+@delimiter+'''+','') + ' case when ['+column_name+'] is NULL then '''' else ' +
          case when data_type in ('char','nchar','varchar','nvarchar','text','ntext','date','time','smalldatetime','datetime','datetime2','datetimeoffset','XML') 
--               and  len(@encapsulate) > 0
               then  ''''+@encapsulate+'''+'
               else ''
          end +
          case when len(@replace) > 0 then 'replace( ' 
               else ''
          end + 
          case when data_type like '%date%' or data_type like '%time%' then 'convert(varchar,['+column_name+'],120)'
               when data_type like '%text%' or data_type = 'XML' then 'convert(varchar(max),['+column_name+'])'
               when data_type like 'n%char' then 'convert(varchar(max),['+column_name+'])'
               when data_type like '%char%' then '['+column_name+']'
               else 'convert(varchar(255),['+column_name+'])'
          end +
          case when len(@replace) > 0 then ','''+@replace+''','''')'
               else ''
          end +
          case when data_type in ('char','nchar','varchar','nvarchar','text','ntext','date','time','smalldatetime','datetime','datetime2','datetimeoffset','XML') 
 --              and  len(@encapsulate) > 0
               then  '+'''+@encapsulate+''''
               else ''
          end + ' end'
   from information_schema.columns where table_name=@table_name order by ordinal_position		        

   set @s = replace('Select ' +@c+ ' as str union all select '+@s+ ' from ' +@table_name,'+''''+','+')

   if @s is NULL select 'Table Name not found, or, Column issues prevent export at this time. Database ' +left(db_name(),255) + '.' +isnull(@table_name,'No Table') + ' ' + left( isnull((select count(*) from information_schema.COLUMNS where table_name = @table_name),0),6) + ' Columns found.'
   else
   exec (@s)

end

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Ummmm... You said you have SQL 2008, but what is the compatability mode of the database ?

There is a problem with SQL2005 and stored procedures using dynamic SQL : http://support.microsoft.com/kb/952734
0
 

Author Closing Comment

by:GD_GRAY
Comment Utility
I have no idea how much work that was for you, but I do know how much I appreciate it. Now if  I only had the words to express it. Thank you so very much. !!!
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
An absolute pleasure...

And a bit of work, but was going to write an Article about it sometime, thought that "now" was an appropriate time :) So, thank you for giving me the opportunity !
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now