creating csv file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

creating csv file

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-06-14 : 09:55:30

How can I have the following be a csv file? I've tried just renaming the query attachment file name to csv but when you open it all the cells are merged because there are no commas.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ETM Mail',
@recipients = '',
@query = 'select poordlin_sql.ord_no, poordlin_sql.vend_no, apvenfil_sql.vend_name,
(CAST(month(poordlin_sql.request_dt) as varchar)+"/"+
CAST(day(poordlin_sql.request_dt) as varchar)+"/"+
CAST(year(poordlin_sql.request_dt) as varchar))as duedate,poordlin_sql.item_no, poordlin_sql.qty_ordered,
poordlin_sql.qty_received, poordlin_sql.stk_loc
from [001].dbo.poordlin_sql join [001].dbo.apvenfil_sql on poordlin_sql.vend_no = apvenfil_sql.vend_no
join [001].dbo.poordhdr_sql on poordlin_sql.ord_no = poordhdr_sql.ord_no
where poordlin_sql.qty_remaining <> 0 and poordlin_sql.request_dt <= GETDATE()+3 and
poordhdr_sql.ord_status <> "C"
order by poordlin_sql.request_dt',
@subject = 'PO Past Due',
@attach_query_result_as_file = 1 ,
@query_attachment_filename = 'POPastDue.csv';

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-14 : 09:59:47

Add this parameter: @query_result_separator = ','

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-06-14 : 10:02:45

I tried that, but when I open the csv file all the fields are still merged in one cell.

33400601, 548,GRACO OHIO INC ,2/3/2011 ,COM1127 , 46.0000, .0000,01

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-14 : 10:50:37

You'll have to use the Text to Columns option under the Data tab/ribbon.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-15 : 13:54:55

You do formatting the front end and not in the database. This is a fundamental principle. Your mindset is still stuck in COBOL, not SQL,

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources