Re: converting SQLTable to Excel Sheet

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.
.

Relevant Pages

  • Re: converting SQLTable to Excel Sheet
    ... open it the datetime column and int columns are not getting recognised. ... and Excel is not likely to understand that. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Problems with Exporting to Excel
    ... My problem lies in the export from SQL Server to Excel. ... Send .xls via mail ... Sometimes the pipes fail giving error that the document is in use ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS and unicode data types
    ... my varchar columns are at most 50 characters. ... destination column in the SQL database destination is only varcharor ... How do I get an Excel column of datatype ... It left SQL Server as a VARCHAR and went into Excel, ...
    (microsoft.public.sqlserver.dts)
  • Use Single Sign on to connect to sql server
    ... connection in an excel file that connects using an odc file connecting using ... The logon account information that you configure here must match the logon ... Name: SQL Server ... If I'm connecting using a sql username, do i need to use the domain piece ...
    (microsoft.public.sharepoint.portalserver.development)
  • Re: Excel 2007 connection
    ... The Feature pack also contains the SQL Server Client ... You should install it. ... so I installed them both as I do for Excel 2003. ...
    (microsoft.public.sqlserver.olap)