Column Headers in CSV file

I've been playing with bcp and xp_sendmessage to try and get certain data sent out as an attached CSV file.  Some of the users want to analyse data in Excel.

If I use bcp I can't get the column headers with the data, and if I put the query in xp_sendmessage and send the results as an attachment the file is not in the format that they want.

Either I want to be able to get the column names in bcp or I want to create a decent formatted file in xp_sendmessage.

Can I do this?

Matt.
LVL 2
UncleMattAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
Try this to workaround...

SELECT col1, col2, col3
FROM (
SELECT 1 as t, 'col1' as Col1, 'col2' as Col2, 'col3' as Col3
UNION ALL
SELECT 2 , col1,col2, col3 FROM YourTable
) as L
order by t, col1

Cheers

0
 
UncleMattAuthor Commented:
you might have noticed the mistake, for xp_sendmessage read xp_sendmail.  duh!!!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You could prepare your QUERY to include the column headers as additional row...

SELECT 'col1' as Col1, 'col2' as Col2, 'col3' as Col3
UNION ALL
SELECT col1,col2, col3 FROM YourTable

Cheers
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
UncleMattAuthor Commented:
I tried that, the query I had didn't like union (It had derived queries inside with order by's)  I'm going to try and rewrite the query and try this again.

Doesn't union order the results, therefore leaving the possibility of the column headers not beiong at the top?

Matt.
0
 
dewpatCommented:
To create your Excel-file, use sp_addlinkedserver and sp_addlinkedsrvlogin, and you can use it as a normal table. So it's posiible to insert the results into a temp-table and dump (insert Results select * from #Results) into the excel-document.
p.e.
EXEC sp_addlinkedserver 'Results',
    'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    'D:\Temp\Results\Results.xls',
    NULL,
    'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'Results', false, NULL, NULL, NULL
GO
see BOL for more info.

0
 
UncleMattAuthor Commented:
dewpat,

Will that work for just a CSV file, I've only used Excel as an example, I don't know what they will be using their end, so I'm trying to get it into a format that they can't moan about.

Matt.
0
 
UncleMattAuthor Commented:
After some messing about and some changes to the query, I've got what I'm after.

cheers Angel

Matt.
0
 
UncleMattAuthor Commented:
see above.  duh!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.