September 12, 2011 at 3:56 am
Hi there,
I am running a query on a table and the output is in a CSV file. However the formatting isn't right in that the data is overlapping (for want of a better word!) and when it gets opened in an excel file the columns of data aren't straight.
exec msdb.dbo.sp_send_dbmail
@profile_name=[Public Profile],
@recipients='customer.services@email.co.uk;person@email.co.uk',
@subject = 'Query Results ',
@query = "Select * from Database.dbo.MakeModelCheckResutls Where DATEDIFF(day, checkdate, GETDATE()) = 0 order by Accountid, registration",
@attach_query_result_as_file = 1,
@query_attachment_filename ='Results.csv'
Can anyone assist with how I sort the format out? I have googled lots but nothing is really coming up with the right answer.
Many thanks in advance if more infromation is needed then let me know.
September 12, 2011 at 7:53 am
Carefull when you import. I guess you csv is delimited with "'," and you filed like name with value " james, bla bla" caused the overlap. When you convert specific quote for these field and you will be ok.
September 15, 2011 at 5:59 am
Thanks Pouliot Philippe I am trying out the DTS package route as I think that will work better for this.
The formatting thing is a nightmare!
September 15, 2011 at 6:06 pm
Pouliot is right. If you have commas in any of the fields in the table , then the data will be misplaced in the output.
So try to replace commas with blank space or some other character and then do the export
Thank You,
Best Regards,
SQLBuddy
September 16, 2011 at 4:35 am
Removed duplicate post!
September 16, 2011 at 4:35 am
Thanks SQLBuddy, I am looking at the query output and I can't see any commas. How would I go about getting rid of these in my query running? I am not having much luck on the output in the DTS package either format wise.
Any help/pointers are appreciated.
September 16, 2011 at 8:16 am
Maybe if you put the exemple of the line wasn't correct, it help to find the error. ANd with correct line for compare issue.
September 16, 2011 at 5:00 pm
Please check all the records.. You will find the comma at least in one field (usually description columns) and the data in the output file will display fine until that point and then it will be out of sync....
If not try to write a basic SSIS package with a data flow task and then using a Sendmail task deliver the output file to your email..
If there is no specific requirement of using the csv file, try to export the data to a tab delimited file (tsv) which should fix the issue..
With SSIS , error logging will be simple and clear and you can troubleshoot it easily..
Check this link to write a basic SSIS package..
Thank You,
Best Regards,
SQLBuddy
September 17, 2011 at 11:40 am
As you are not specifying the parameter @query_result_separator, the separator in your attached file will be space, not comma, so it isn't a CSV file. If your data doesn't contain commas, you can just add ",@query_results_separator=','" to your parameter list for sp_send_dbmail and then you'll have a CSV file.
If the data does include commas, you will also need to change the query so that it either changes them to something else (maybe char(32) or char(184), depends on taste and on the font th erecipients of the email will use in excel) or enclose the individual attributes in double quotes so that the commas are taken as part of the string data instead of as separators.
Tom
September 21, 2011 at 4:52 am
Apologies all, I am not ignoring you and I am very grateful for your feedback it's been a manic few days!
I will read through and then post back what solution worked best.
Many thanks again for your help....
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply