Re: converting SQLTable to Excel ***

On Mar 28, 3:57 am, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
 (nalamve...@xxxxxxxxx) writes:
On Mar 27, 5:08 pm, nalamve...@xxxxxxxxx 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...@xxxxxxxxxxxxx

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-programming/sql-server-excel-workbench/

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