Solved

Column Headers in CSV file

Posted on 2001-07-03
8
2,737 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 142

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
 
LVL 142

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

895 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

15 Experts available now in Live!

Get 1:1 Help Now