Solved

Column Headers in CSV file

Posted on 2001-07-03
8
2,736 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:UncleMatt
  • 5
  • 2
8 Comments
 
LVL 2

Author Comment

by:UncleMatt
Comment Utility
you might have noticed the mistake, for xp_sendmessage read xp_sendmail.  duh!!!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 2

Author Comment

by:UncleMatt
Comment Utility
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 1

Expert Comment

by:dewpat
Comment Utility
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
 
LVL 2

Author Comment

by:UncleMatt
Comment Utility
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
 
LVL 2

Author Comment

by:UncleMatt
Comment Utility
After some messing about and some changes to the query, I've got what I'm after.

cheers Angel

Matt.
0
 
LVL 2

Author Comment

by:UncleMatt
Comment Utility
see above.  duh!!!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now