Solved

export to csv formatting

Posted on 2010-09-23
14
465 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
ID: 33743543
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 33743655
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
ID: 33743824
On CSV file - you may need to add the options: -c and -t to your BCP output
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 16

Expert Comment

by:carsRST
ID: 33744010
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
ID: 33744061
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
ID: 33744071
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
ID: 33744104
See here for a basic example.  Compare to your BCP ouput.

http://www32.brinkster.com/srisamp/sqlArticles/article_4.htm
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33744121
>>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
ID: 33744162
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
ID: 33744201
I don't just want to generate, carsRST.  I need to email it, as an attachment.  
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 33744304
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
ID: 33745102
I got it.  Thank you for the input.  
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33745478
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
ID: 33745527
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
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.
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

770 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