insert headers into sql query results

Question

insert headers into sql query results

mbhout22

SSC-Addicted

Points: 404
More actions
September 17, 2009 at 10:00 am

#141751

Hi ,
I am new to TSql and need to add dummy headers to my sql result before i export it to a csv format.
i can have a header which can be a noddy as '1' or 'header' as the macro that need to read my result start reading data from the 3rd row
Many thanks,
Christian

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

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

dbo.benyos SSCommitted Points: 1944 More actions · Answer 1

If that's the way your tool reds CSV I suggest you find fome other tool, but as per your question, you may append dummy lines in the following way: Suppose you have a list of names and their phone numbers, returned by this fictious query:

Select PersonName, Phone From MyPhoneBook

You could add your 2 dummy lines in the following way:

Select '' AS PersonName, '' AS Phone

UNION ALL

Select '', ''

UNION ALL

Select PersonName, Phone From MyPhoneBook

Tal Ben Yosef
www.TalBenYosef.com
(visit my LinkedIn profile)

mbhout22 SSC-Addicted Points: 404 More actions · Answer 2

Thanks for you response, this will work for my initial question. Unfortunatly i have about 70 columns, is there a short way of doinf this without clobbering the sql codes with empty strings?

i know that its can be done in excel but was wondering if there is a sql statement that does it inside sql server

dbo.benyos SSCommitted Points: 1944 More actions · Answer 3

It really depends on many parameters, but as a rule of thumb - check if you need to apply this fix in many views or procedures and if all solutions are the same. If so, you might wanna forget about touching the SQL code and opting for external solutions that wil simply add those 2 empty lines in the beginning of your file.

Some of the available tools for that could be:

1. CMD --> type 2 empty lines and then your original files into a new CSV file (however, you might need the commas. I don't know which tool are you using to read your CSV files)

2. LOGPARSER --> You can use Microsoft's LogParser to do the same thing you wanted SQL to

3. Use CSCRIPT (JS / VBSCRIPT) to append replicate the first two lines

etc...

Tal Ben Yosef
www.TalBenYosef.com
(visit my LinkedIn profile)

mbhout22 SSC-Addicted Points: 404 More actions · Answer 4

Thanks Ben,

I am using SSIS, i can use vbcrlf cmd in access but we are trying to move away from that.

thanks,

Chris

dbo.benyos SSCommitted Points: 1944 More actions · Answer 5

Although I owe much of my com[puter life to mictosoft Access, I think it's a smart decision to move away from that.

You're either gonna deal with the CSV producer or the consumer....

One more choice came to my mind: Unix SED command. It has a Win32 port. It's really powerful!

Sed man page

Tal Ben Yosef
www.TalBenYosef.com
(visit my LinkedIn profile)

mbhout22 SSC-Addicted Points: 404 More actions · Answer 6

Thanks Ben,

i guess for the time being i will stick with access before experiemnting with other products.

Thank you for your contribution

C