converting SQLTable to Excel Sheet

[nalamveena]

I am able to export to excel through a stored procedure.But when i
open it the datetime column and int columns are not getting recognised.

[Erland Sommarskog]

(na********@gmail.com) writes:

On Mar 27, 5:08*pm, [e-mail] wrote:

>I am able to export to excel through a stored procedure.But when i
open it the datetime column and int columns are not getting recognised.

This is the code i wrote:

I have difficulties to understand where you procedure ends, and which
are the test stuff you do. What I can see, you build a BCP command with
queryout:

-- build full BCP query
select @sql = 'bcp "' + @dbName + ' select * from
##TempExportData2" queryout "' + @fullFileName + '" -c -t"," -CRAW'

But I can't see that you execute it. I see this:

-- execute BCP
Exec Employees..xp_cmdshell 'bcp "Employees.dbo.EmpDetails" OUT "d:
\test5.xls" -Slocalhost -T -n -r\n -t"|" -q'

There you export the full table. Furthermore, you use native format
(-n), and Excel is not likely to understand that.

Also, I don't think you should export to an .xls file. I don't know
Excel that well, I would think that Excel expects a binary file when
you have .xls. Shouldn't you use .csv instead?

I've also seen people export to Excel using OPENROWSET or OPENQUERY,
but I have not done this myself.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[nalamveena]

On Mar 28, 3:57*am, Erland Sommarskog <[e-mail]:

*([e-mail]) writes:

On Mar 27, 5:08*pm, [e-mail] wrote:

I am able to export to excel through a stored procedure.But when i
open it the datetime column and int columns are not getting recognised.

This is the code i wrote:

I have difficulties to understand where you procedure ends, and which
are the test stuff you do. What I can see, you build a BCP command with
queryout:

-- build full BCP query
select * *@sql = 'bcp "' + @dbName + ' select * from
##TempExportData2" queryout "' + @fullFileName + '" -c -t"," -CRAW'

But I can't see that you execute it. I see this:

-- execute BCP
Exec Employees..xp_cmdshell *'bcp "Employees.dbo.EmpDetails" OUT "d:
\test5.xls" -Slocalhost -T -n -r\n -t"|" -q'

There you export the full table. Furthermore, you use native format
(-n), and Excel is not likely to understand that.

Also, I don't think you should export to an .xls file. I don't know
Excel that well, I would think that Excel expects a binary file when
you have .xls. Shouldn't you use .csv instead?

I've also seen people export to Excel using OPENROWSET or OPENQUERY,
but I have not done this myself.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I have tried using openrowset aswell openquery.But i am getting an
error in
server name. My servername has '-' symbol.
I took the code from the following link:
http://www.simple-talk.com/sql/t-sql...cel-workbench/

I am a beginner in this field. I dont know how to convert from csv to
xls.
Please guide me thorugh this.