By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,357 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

converting SQLTable to Excel Sheet

P: n/a
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.
Mar 27 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
(na********@gmail.com) writes:
On Mar 27, 5:08*pm, nalamve...@gmail.com 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
Mar 27 '08 #2

P: n/a
On Mar 28, 3:57*am, Erland Sommarskog <esq...@sommarskog.sewrote:
*(nalamve...@gmail.com) writes:
On Mar 27, 5:08*pm, nalamve...@gmail.com 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, esq...@sommarskog.se

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.
Mar 28 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.