Export data to Excel

Question

Post reply

Export data to Excel

Dipak-387207

Mr or Mrs. 500

Points: 566
More actions
February 21, 2008 at 2:56 pm

#127987

I have one table for export to excel but there is one column values begining with 0000 and i am using BCP for out data to excel but when it's complete it is not showing 0000 in excel sheet.

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

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

That is because it thinks the type is an integer. Try casting it as a varchar.

SELECT CONVERT(VARCHAR(10),MYCOLUMN)

or

You could even try to pad and convert the column

SELECT RIGHT('0000000000' + CONVERT(VARCHAR(10),MYCOLUMN),10)

My blog: http://jahaines.blogspot.com

Dipak-387207 Mr or Mrs. 500 Points: 566 More actions · Answer 2

Its not working, here i will show you an example

create table file1(Loan_Number varchar(10))

insert into file1 values('0000012345')

insert into file1 values('0000045678')

insert into file1 values('0000009876')

Now here is the query

Exec Master..xp_cmdshell 'bcp "SELECT loan_number from test.dbo.file1" queryout C:\test.xls -c -T -RRaw'

Now you can not see 00000 values in Excel sheet, now i want is this possible through T-SQL?

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 3

Hi Dipak,

The problem is in excel it is considering it as an integer and thus is removing the preceding zeros.

>Change the cell format in excel to text before you run the bcp utility.

OR

>add single quote as prefix to the loan_number in your select query.

Regards,

Ahmad

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Dipak-387207 Mr or Mrs. 500 Points: 566 More actions · Answer 4

If suppose I want to see every time new excel sheet then how can we change the Text format in Excel sheet, because everyday its looking new file from SQL Server, then how can we change the text format?

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 5

Hi Dipak,

Then try the next option......:)

Regards,
[font="Verdana"]Sqlfrenzy[/font]