Export to CSV file with column names listed!

Question

Export to CSV file with column names listed!

Kamol Murodov

SSC Veteran

Points: 272
More actions
September 17, 2007 at 1:32 pm

#197846

How to extract column names from SP? I need it for BCP export to CSV file with column names. Or is there any better solution to export SP's result set to CSV file with column names listed?
Thank you in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

Have BCP do a Queryout from a sproc or a view that does a union between some column names and data... keep in mind that all of the data must be converted to character data to do that.

Same thing works in OSQL with QUOTENAME, as well.

--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)

Kamol Murodov SSC Veteran Points: 272 More actions · Answer 2

Thanks, Jeff...The problem with UNION is that I can't modify existing SP cause it is used to populate gridViews and I don't need column names listed in gridView.

I am not sure if QUOTENAME can help me much since I am gonna use existing SP and not planning to modify them. OSQL could be a nice approach except it is putting some dashed line ("---") below column headers. Does anyone know how to rid off these dashes but still keep column header?

I need to use existing SP, preferable without modifying them (definitely without listing column names in record set). CSV should look like this:

id, name, phoneNumber

1, Joe, 21343

2, Ann, 25533

3, Jim, 25677

etc...

Jeff Scott-375773 SSC Enthusiast Points: 144 More actions · Answer 3

If you're using a connection url to create the report try appending &rc:noHeader=false onto it. And you'll have to change either the names of the textboxes in the table or the header values to be the headers you want in the csv.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

Then, have the batch precreate a file with the header names (using ECHO) and use OSQL to call the proc using the >> DOS append symbol instead of the -o parameter...

--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)

Kamol Murodov SSC Veteran Points: 272 More actions · Answer 5

I don't really understand "&rc:noHeader=false" code, and I am not sure what do you mean by "to change either the names of the textboxes in the table or the header values to be the headers you want in the csv". Can you please explain in more details. All i want is to get CSV file from existing SP on MS SQL Server.

Kamol Murodov SSC Veteran Points: 272 More actions · Answer 6

So, how do i recreate header names from existing SP? Should i write separate SP for each of my existing SP just to echo header names to file? What if my existing SP columns list changes? Should I go back and change my header populating SP to change again? Of course i could use OSQL to echo just column names but it will also put "dashed line" below them. If someone explained how to get rid off those dashes that would help ALOT.

BTW, can BCP utility append to file?