Link to home
Start Free TrialLog in
Avatar of BigBadWolf_000
BigBadWolf_000Flag for United States of America

asked on

SQLCMD export to csv with headers and no dashes on second line

I am using sqlcmd (2008 SQL server) to automate query export to csv file...problem is it adds a "---------" line after the header row, I want the dashed line removed. If I use the -h -1 switches, it removes both the header and the dashed line, I need the header.....Thanks
Avatar of edtechdba
edtechdba
Flag of United States of America image

How about using a command to delete the line with the "----"?

I use SSIS packages often to export data from SQL Server into flat files, such as a CSV. This works well and will export the headers and data as appropriate.

It also may be of interest to you to check out the BCP utility to perform this function (see link below).
http://msdn.microsoft.com/en-us/library/ms162802.aspx
Avatar of BigBadWolf_000

ASKER

Thanks, but i know i can delete the line using vbs or bat. If there is syntax for bcp to insert a header row, then maybe, but, i want to do it in sql, i know there is a way to create a first row header and then use the -h -1....i just need thecorrect syntax for 'union all'
Avatar of Marten Rune
From stackoverflow:

"The only thing I can think of is removing the header using the -h -1 switch and adding the column names as the first row to the SQL Query:

SELECT 'FirstName' as FirstName, 'LastName' as LastName
UNION
SELECT top 5 FirstName, LastName FROM Person.Contact
Note that if there are other data types then (var)char, you need to convert the field using : CAST(MyColumnName AS varchar(32)) as MyColumnName"
http://stackoverflow.com/questions/2362229/how-to-supress-hyphens-in-sqlcmd

Regards Marten
martenrune: I have seen all the google searches
How do I use it  to get correct results?
My Select includes

Cast (varchar within)
Case (varchar within)
'' (for a inserted empty  column)

Union or Union all does not like the varchar references
Well, how are you running your sqlcmd - is it via T-SQL (xp_cmdshell) or via the command line ?

If T-SQL then we have a couple of options like creating a stored-procedure...

If command line then we can escape within sqlcmd to execute a op sys command using the ":!!" option.

If you could let me know a preferred direction, can offer a suggestion or two...
Oh, and sqlcmd will always put the "---" in there.
sqlcmd -h -1 in conjunction with an UNION ALL to create a header line has many, many potential issues (reliable sort order is one of them, having to use varchars everywhere another) .
Sometimes you have to stop being stubborn, and switch to something more simple and managable. There are so many options, like using PowerShell, for which exporting data into CSV is a cakewalk. Mixing sqlcmd and a batch or VBS to remove unwanted lines is common usage (I do that myself occassionaly), but there are many things which can still go wrong with it. Nevertheless, for starters I would stay with that (and call a batch file from inside sqlcmd to strip the dashes).
try this:

1. output headers only using TOP 0 to a a file
2. strip the ---'s from this file and store as the header file
3. output the wanted data without headers to a data file
4. append header and data files to a combined file
although "select *" is shown here, the fields should be specified by name

REM 1. Output column headers; via TOP 0
sqlcmd -S YourServer -Q "set nocount on;select top 0 * from YourDB.YourSchema.TheTableOrView" -o "TheTableOrViewColsOnly.csv" -s "," -W

REM 2. Strip the ---'s from this file
findstr /R /C:"^[^-]*$" TheTableOrViewColsOnly.csv > TheTableOrViewHeader.csv

REM 3. Output data without headers
sqlcmd -S YourServer -Q "set nocount on;select * from YourDB.YourSchema.TheTableOrView" -o "TheTableOrViewData.csv" -h -1 -s "," -W

REM 4. Append header and data to combined file
type TheTableOrViewHeader.csv TheTableOrViewData.csv > TheTableOrViewDataCombined.csv

Open in new window

Just as easy to export to text as sqlcmd with headers then do

find /V "--,--" my_sqlcmd_outputfile.txt > my_csv_file.csv

And from earlier entries, the -h param with no headers must be -h-1 (without the space before -1)

But think the asker is looking for a SQL solution. And really, there are date formats, embedded commas etc that need to be handled properly.

So, really, the only sure way is to create a stored procedure and manage the strongly typed data columns.

for example...
1) create stored procedure to format output
2) test stored procedure
3) use BCP or SQLCMD -h-1 to execute stored procedure
4) use xp_cmdshell to launch the process

Note : small "gotcha" when specifying delimiters / separators / encapsulation characters in step 3. Need to allow for CHAR(nn) representation otherwise the sqlcmd or bcp will fail in the xp_cmdshell because of (double) quotes. The procedure below accommodates.

--
-- step 1 create stored procedure
--
if object_id('usp_format_csv','P') is not null drop procedure usp_format_csv
go

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

-- notes char(34) = double_quote char(44) = comma

  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 convert(varchar(max),'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.') as str
   else
   exec (@s) -- with result sets ((str varchar(max)))
   
end
go
-- 
-- step 2 - test stored procedure
--
exec usp_format_csv 'tbl_EE_Export_TESTER',',','"',''    -- using character literals
-- or 
exec usp_format_csv 'tbl_EE_Export_TESTER','44','34',''  -- using char(nn) values
--
-- step 3 and 4 - export
--
-- using BCP

exec master..xp_cmdshell 'bcp "exec your_db..usp_format_csv ''tbl_EE_Export_TESTER'',''44'',''34'','''' " queryout "c:\tester_b.csv" -SYOUR_SQL_SERVER -dYOUR_DB -c -T -CACP'

-- using sqlcmd

exec master..xp_cmdshell 'sqlcmd -Q"set nocount on; exec your_db..usp_format_csv ''tbl_EE_Export_TESTER'',''44'',''34'','''' " -h-1 -o"c:\tester_s.csv" -E -SCOMPUTER-VAIO\SQL2012 -dYOUR_DB -W'

-- obviously change the server and db / login credentials and output file as required

Open in new window

mark_wills: cmd line, I need the -------- stripped but header retained. I believe this cannot be done within sqlcmd, however can be via union. I know I can append using the >> along with -h -1 in sqlcmd, but i don't need append.

mark_wills: Yeah, if stored procedures is gonna be the only way then I will try it...was tring to avoid it :)

Qlemo: I hear you and am using vbs/excel to strip the -----. But, that is a workaround and 'stubborness' is the mother of all discovery and creation and learning ;))

PortletPaul: Thanks Paul, I can acheive the same with vbs...however, I want to do it in SQL native

Thanks All!
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Mark,

Your line is buggy. Either you pipe sqlcmd output thru find, or you write an output file to parse by find ;-). So either:
sqlcmd -Q"your query here"  -o"c:\tester_t.txt" -E -Syour_servername -dyour_dbname -W & find /V "---" < tester_t.txt > tester_t.csv

Open in new window

or
sqlcmd -Q"your query here" -E -Syour_servername -dyour_dbname -W | find /V "---" >tester_t.csv

Open in new window

Note that find has the "feature" to write out the filename, even if only one file is provided. Hence the < .
Hmmm... it does work OK, because the -o created the file (so yes, not really a pipe at all). But understand / agree with the above comments about "pipe"... However it is not "buggy" per se, and works just fine as a single command line. Besides which, I was recommending a different approach - using a function.
If you don't absolutely _have_ to use SQLCmd then have you considered using SSIS? This functionality is built-in.

hth

Mike