Solved

export to csv formatting

Posted on 2010-09-23
14
458 Views
Last Modified: 2012-06-21
simply need to send the output of a query as an email attachment.  i can send it just fine (sp_send_dbmail), but the csv format is terrible. see here --

The values per record are like this:  AA   100  -200   200  -2000  -400  400   2000   AAA  9

The export to csv mushes them all into one value, separating each with a '?', like this:    
    AA?100?-200?200?-2000?-400?400?2000?AAA?9

I've got numerous other jobs running just like this, sending csv attachments daily, without problem.  I can't find the error in this.  

Also, when trying to define the file with the datestamp, like this:
   SET @file = 'D:\Reports\filename_'+convert(char(8),getdate(),112)+'.csv'

It fails with this:
  Msg 22051, Level 16, State 1, Line 0
  Attachment file D:\Reports\filename.csv_20100923.csv is invalid.

Yet if I pull out the date, I write the file in just fine.   (except that the output blows)

See my post below.  The @query is formatted like that to allow for a header line in the output.  When run in SSMS, the output is perfect, EXCEPT that I really need to order the symbols underneath the headerline.  any ideas on how to do that?

Please and thank you, in advance.
DECLARE @file VARCHAR(100),

        @query VARCHAR(5000),

        @subject NVARCHAR(1000)



SET @file = 'D:\Reports\filename.csv'

SET @query = '

bcp "SELECT 

Symbol,MaxPos,MinVega,MaxVega,MinDelta,AutoMinDelta,AutoMaxDelta,MaxDelta,EndPoint,stdSize

FROM view WHERE maxpos = ''maxpos'' 

UNION 

SELECT Symbol,MaxPos,MinVega,MaxVega,MinDelta,AutoMinDelta,AutoMaxDelta,MaxDelta,EndPoint,stdSize

FROM view WHERE maxpos <> ''maxpos'' 

ORDER BY ''maxpos'' DESC" 

queryout "'+@file+'" -w -Sservername -T'



SET @subject = N'my subject - ' + CONVERT(NVARCHAR(11),GetDate(),121) 



EXEC msdb.dbo.sp_send_dbmail @recipients ='me.com',

@Subject=@subject,

@body_format='HTML',

@file_attachments=@file



EXEC master..xp_cmdshell @query

Open in new window

0
Comment
Question by:dbaSQL
  • 7
  • 7
14 Comments
 
LVL 16

Expert Comment

by:carsRST
Comment Utility
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
This piece is nearly there, it's just a matter of the formatting, and my other two issues with the date in the filename, and the ordering of the values.

Any other EE ideas?
0
 
LVL 16

Expert Comment

by:carsRST
Comment Utility
On CSV file - you may need to add the options: -c and -t to your BCP output
0
 
LVL 16

Expert Comment

by:carsRST
Comment Utility
Few things on file attachment....

it could a permissions issue while trying to attach.  You may try to make a copy of the file and attach the copy.

You also may try UNC vs "d:\..."

Or could be the process under which your sql is running (on the server) doesn't have permissions to the file.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
If it were a permissions issue, I wouldn't be able to write the file at all.  I"m writing it just fine... the format is not working. All values per record are smushed into one cell in the csv.
0
 
LVL 16

Expert Comment

by:carsRST
Comment Utility
Adjust your BCP to include the options -t -c

Also, try copying the file out to see if the ouput process still has a hold of the file.
0
 
LVL 16

Accepted Solution

by:
carsRST earned 500 total points
Comment Utility
See here for a basic example.  Compare to your BCP ouput.

http://www32.brinkster.com/srisamp/sqlArticles/article_4.htm
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 16

Expert Comment

by:carsRST
Comment Utility
>>Also, try copying the file out to see if the ouput process still has a hold of the file.

Expand....copy the file after creating.  Attach the copied file, as opposed to the original.

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
I've already tried the -t and -c.  Even changing the query to simply exec a proc, or just a raw select -- the output from all of them is the same.

this is a cut/paste from the csv:
AA      100      -200      200      -2000      -400      400      2000      AAA      9

but, that came out of one cell.   just the first cell, all the way down, every record is formatted like that, just looking at the csv, it looks like this:

AA?100?-200?200?-2000?-400?400?2000?AAA?9

it's not really a '?'.  it's a '?' inside of a tiny little box, in between each value
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
I don't just want to generate, carsRST.  I need to email it, as an attachment.  
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
I got it working, finally, using the example from this post:  ID: 33744104

The file is formatted correctly, I should say.  Now I am working on the email attachment.
0
 
LVL 17

Author Closing Comment

by:dbaSQL
Comment Utility
I got it.  Thank you for the input.  
0
 
LVL 16

Expert Comment

by:carsRST
Comment Utility
Thank you for the points, although I'm sure your trial and error played a huge part in it.  :)

Do you mind, for my knowledge, what you did to get the email working?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
Sure, I declared @date and @filename, and set @filename = 'D:\......_'+@date+'.csv'.
Then my @bcpCommand referenced @filename with -c -t"," -Sservername -T

And after the EXEC of @bcpCommand, I executed sp_send_dbmail with @filie_attachments = @filename.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 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

12 Experts available now in Live!

Get 1:1 Help Now