how to export sql output to excel with text format

Asked Aug 12 '13 at 05:53

Active Aug 12 '13 at 05:55

Viewed 2,134 times

I have requirement that, Need to export the sql output data to excel sheet. To acheive this i am executing the below query,

xp_cmdshell'sqlcmd -s  -U aaaa -P bbbbb -d dbao -Q " set nocount on;select * from table_tmp" -s "," -o D:\temp_table.xls

The query is executing fine but the issue which i am facing while executing that query is,

I am loosing the extract format of the columns

E.g., 0000012345 is stored as 12345 ( since it is numeric value xls by default removing preceeding zero's.

Is there a way to load the data in excel with format as text.

Thanks.

edited Aug 12 '13 at 05:55

asked Aug 12 '13 at 05:53

baski

Why can't you morph that columns content into a string inside the SELECT command? – arkascha Aug 12 '13 at 05:57
Is you're output a csv, and you're opening in excel? – lordkain Aug 12 '13 at 05:58
I am opening in excel only. – baski Aug 12 '13 at 05:59
and if youre opening in notepad, then you do or don't see the precending zeros? – lordkain Aug 12 '13 at 06:02
1

Just follow [this](http://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd) ,hope it helps. – Mask Aug 12 '13 at 06:02
I did not try opening that file in notepad. And i think if we open a xls in notepad it will not be in readable format. – baski Aug 12 '13 at 06:06
I think @Mask's link will help... Go for it... – NREZ Aug 12 '13 at 06:17
Ifyou can maybe possible to make the columnvalues surrounded with " Like "00125" now excel know this is a string – lordkain Aug 12 '13 at 06:20
@Mask . the link which you provided is really help full. but i am not sure which option will make data to store in text format. is that -C or -V – baski Aug 12 '13 at 06:29

0 Answers0