April 28, 2014 at 11:15 am
Hi,
I'm sending report based on below DB Mail query from mentioned data format table. But i'm getting invalid format output in csv file.
Table Data:
CREATE TABLE Sales_Data
(
ITEM_CODE varchar(25),
Price float,
Purchased_Date Datetime
)
INSERT INTO Sales_Data (ITEM_CODE,Price,Purchased_Date)
SELECT '000001744260',10,'2014-04-27 12:43:55.000' UNION ALL
SELECT '000001849260',10,'2014-04-08 10:05:20.000' UNION ALL
SELECT '000001045260',10,'2014-04-02 11:56:10.000'
Query Used:
declare @date varchar(8),@filename varchar(8000),@filename1 varchar(50)
set @date =substring( CONVERT(Varchar(8),GETDATE()-1,112),7,2)
set @filename= 'Daily Sales-'+@date+'.csv'
EXEC msdb.dbo.sp_send_dbmail @profile_name= 'MailServer',
@recipients='ABC@gmail.com',
@copy_recipients='CDE@gmail.com',
@subject='Sales Report',
@body='Hi Team, Pls find the attached Sales report',
@query='select cast(ITEM_CODE as Text)IccidPrefix,cast(PRICE as text)PRICE,
PURCHASE_DATE from Report.dbo.Sales_data
where convert(varchar(10),PURCHASE_DATE,20)=convert(varchar(10),getdate()-1,20)',
@attach_query_result_as_file = 1,
@query_attachment_filename = @filename,
@query_result_header = 1,
@query_result_separator = ' ', -- Tab Delimiter
@exclude_query_output = 0,
@append_query_error = 1,
@query_result_no_padding =1;
Received Output Format:
CREATE TABLE Output_Data
(
ITEM_CODE varchar(25),
Price float,
Purchased_Date Datetime
)
INSERT INTO Sales_Data (ITEM_CODE,Price,Purchased_Date)
SELECT '1744260',10,'2014-04-06 12:43:55.000'
Needed Output Format
CREATE TABLE Output_Data
(
ITEM_CODE varchar(25),
Price float,
Purchased_Date Datetime
)
INSERT INTO Sales_Data (ITEM_CODE,Price,Purchased_Date)
SELECT '000001744260',10,'2014-04-06 12:43:55.000'
April 28, 2014 at 12:17 pm
I'm not clear on what you mean by "getting invalid format output in csv file".
However, focusing on the query for a moment:
1. The column name of PURCHASE_DATE doesn't match your column purchased_date.
2. You can't cast a float to text.
3. You're doing a convert of your date column for every single row, which is never efficient.
4. Given the WHERE clause, I'm assuming you want to pull yesterday's sales data.
Instead of this:
select cast(ITEM_CODE as Text) IccidPrefix, cast(PRICE as text) PRICE, PURCHASE_DATE
from Report.dbo.Sales_data
where convert(varchar(10),PURCHASE_DATE,20)=convert(varchar(10),getdate()-1,20)
Try something like the following:
SELECT CAST(ITEM_CODE AS Text) IccidPrefix, CAST(PRICE as Varchar(30)) Price, Purchased_Date
FROM dbo.Sales_data
WHERE purchased_date BETWEEN DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0)
AND DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0);
Does this make sense?
April 29, 2014 at 4:17 am
Hi Ed,
Thanks for your reply. In output sheet i'm not getting the first 4 digit zeros from Item_code. I need output with zeros.
April 29, 2014 at 5:19 am
Are you, by chance, opening the .csv file using Excel? If so, that explains what you're seeing with the leading zeros being dropped.
Your data contains the leading zeros, so the query returns them and most likely writes them out to your text file in CSV format. Excel interprets what it opens, so if you open that text file in Excel, it sees those numeric values and determines that it's numeric so it drops the leading zeros for you. You can confirm this by opening the text file in your favorite text editor and seeing that they're present. If you see them, you've confirmed that the query is running properly.
As for controlling how Excel interprets data, good luck. You can try prefixing the value with a single quote or writing the file out with a .txt extension instead. Text files with a .txt extension don't open in Excel by default. BTW, Excel's interpretation of dates is even worse.
April 29, 2014 at 7:41 am
Thank u...
April 29, 2014 at 7:42 am
No problem. Glad I could help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply