Solved

Column Headers in CSV file

Posted on 2001-07-03
8
2,744 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 2

Author Comment

by:UncleMatt
ID: 6248139
you might have noticed the mistake, for xp_sendmessage read xp_sendmail.  duh!!!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6248210
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
ID: 6248312
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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
ID: 6248324
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
 
LVL 1

Expert Comment

by:dewpat
ID: 6248422
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
ID: 6248499
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
ID: 6248728
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
ID: 6248733
see above.  duh!!!
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

728 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