converting SQLTable to Excel Sheet

[nalamveena@gmail.com]

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]

Re: converting SQLTable to Excel Sheet

(nalamveena@gma il.com) writes:

On Mar 27, 5:08 pm, nalamve...@gmai l.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
##TempExportDat a2" queryout "' + @fullFileName + '" -c -t"," -CRAW'

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

-- execute BCP
Exec Employees..xp_c mdshell '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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[nalamveena@gmail.com]

Re: converting SQLTable to Excel Sheet

On Mar 28, 3:57 am, Erland Sommarskog <esq...@sommars kog.sewrote:

 (nalamve...@gma il.com) writes:

On Mar 27, 5:08 pm, nalamve...@gmai l.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
##TempExportDat a2" queryout "' + @fullFileName + '" -c -t"," -CRAW'

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

-- execute BCP
Exec Employees..xp_c mdshell  '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...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/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.