Export with Headers

Question

Export with Headers

Steve Jones - SSC Editor

SSC Guru

Points: 736504
More actions
November 18, 2010 at 2:23 pm

#231782

How can you export data from a query, using bcp, SQLCMD, etc. and include the column headers at the top? The export would be CSV or other delimited text file.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

Steve Jones - SSC Editor (11/18/2010)
How can you export data from a query, using bcp, SQLCMD, etc. and include the column headers at the top? The export would be CSV or other delimited text file.

Unless someone else has an absolute need to write this one, I'll be happy to take this one on. It won't include anything about SSIS, though. Might be better if that were a separate article, anyway.

Lemme know. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736504

November 26, 2010 at 1:59 pm

#1255897

It would be good to have an SSIS and non/SSIS version

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Since I don't actually know how to spell "SSIS", I'll take the non-SSIS version.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gmby SSCarpal Tunnel Points: 4511 More actions · Answer 4

I doubt whether a complete article is needed for SSIS. The connection managers for Excel and flat files presents a check box to determine whether you want the first row to be column names.

jg

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

For the non-SSIS version, what language are you using? Because if it's not PowerShell, I might want to do one using PowerShell.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

Grant Fritchey (12/2/2010)
For the non-SSIS version, what language are you using? Because if it's not PowerShell, I might want to do one using PowerShell.

Steve asked for "using bcp, SQLCMD", but I think one on PowerShell should come out first. And, since I don't actually know how to spell "PowerShell", I'd be tickled to see a "Grant Fritchey" article on such a thing. I can write on something else.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 7

Grant Fritchey (12/2/2010)
For the non-SSIS version, what language are you using? Because if it's not PowerShell, I might want to do one using PowerShell.

That would be quite cool!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 8

Jeff Moden (12/2/2010)
Grant Fritchey (12/2/2010)
For the non-SSIS version, what language are you using? Because if it's not PowerShell, I might want to do one using PowerShell.
Steve asked for "using bcp, SQLCMD", but I think one on PowerShell should come out first. And, since I don't actually know how to spell "PowerShell", I'd be tickled to see a "Grant Fritchey" article on such a thing. I can write on something else.

Let's see what the boss says. I don't know why you couldn't do both.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gift Peddie SSC Guru Points: 73570 More actions · Answer 9

gmby (12/1/2010)
I doubt whether a complete article is needed for SSIS. The connection managers for Excel and flat files presents a check box to determine whether you want the first row to be column names.
jg

The above just means you don't know what you can do with SSIS; I have almost 1,000 lines of code using SSIS. You can turn header on and off, encapsulations on and off and delimiter agnostic using configurations.

Kind regards,
Gift Peddie

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 10

Be good to have one in Powershell, and while we're at it, anyone want SQLAzure?

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 11

Steve Jones - SSC Editor (12/3/2010)
Be good to have one in Powershell, and while we're at it, anyone want SQLAzure?

Right, I'll do one in PowerShell, but no promises on deliver time. Jeff "Jack Rabbit" Moden should go ahead with his. I'll be along shortly.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 12

Steve Jones - SSC Editor

SSC Guru

Points: 736504

December 3, 2010 at 9:21 am

#1258488

No hurry on any of these.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 13

Steve Jones - SSC Editor (12/3/2010)
No hurry on any of these.

I guess that's a good thing. Grant, did you ever get yours done? I know I'm way overdue on this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 14

Jeff Moden (2/10/2013)
Steve Jones - SSC Editor (12/3/2010)
No hurry on any of these.
I guess that's a good thing. Grant, did you ever get yours done? I know I'm way overdue on this.

Forgot all about this. I'll add it to my queue.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning