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
Solved

Column Headers in CSV file

Posted on 2001-07-03
8
2,741 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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

828 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