Solved

export to csv formatting

Posted on 2010-09-23
14
471 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

740 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