How to export SQL data to Excel SpreadSheet using SQL Query?

Question

Hi

Im using this query to select ,calculate and format data like  Refer here for more understanding:-

Select  DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0) As RowDateTime,   Avg(Meter1) As Meter1,   Avg(Meter2) As Meter2,   Avg(Meter3) As Meter3 From    TableName Group By DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0)

I want the output of the query to be written in the excel Sheet.

Your help will be highly appreciated.

Answer 1

use SQL Server Integration Services (SSIS)

Answer 2

 

 

hi

try this query and remember first put your database name

Exec master..xp_cmdshell 'bcp "use dbname; Select  DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0) As RowDateTime,
  Avg(Meter1) As Meter1,
  Avg(Meter2) As Meter2,
  Avg(Meter3) As Meter3
From    TableName
Group By DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0)
" queryout "e:\test.xls" -c -CRAW'

your out put would be e:\test.xls

hope it will help you

 

 

Answer 3

Derek Comingore - RSC wrote:

use SQL Server Integration Services (SSIS)

Hi, Thanks for the reply but im using Visual Studio Express and it doesn't have (SSIS).

Any Ideas?

Answer 4

Arif Hasan wrote:

hi try this query and remember first put your database name Exec master..xp_cmdshell 'bcp "use dbname; Select  DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0) As RowDateTime,   Avg(Meter1) As Meter1,   Avg(Meter2) As Meter2,   Avg(Meter3) As Meter3 From    TableName Group By DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0) " queryout "e:\test.xls" -c -CRAW' your out put would be e:\test.xls hope it will help you

Thanks Arif

This is the code im using but it doesnt work :-

Exec master..xp_cmdshell 'bcp "USE [Test]; SELECT DATEADD(Hour, DATEDIFF(Hour, 0, [RowDateTime]), 0) AS [RowDateTime], AVG([X407_Fuel_Mass_Cons_Filtered]) AS [X407_Fuel_Mass_Cons_Filtered], AVG([X407_Engine_Power_Filtered]) AS [X407_Engine_Power_Filtered], AVG([X407_DTF]) AS [X407_DTF], AVG([X407_PLA]) AS [X407_PLA], AVG([X407_PLAS]) AS [X407_PLAS], SUM([X407_ERS]) AS [X407_ERS], AVG([PL_LUS]) AS [PL_LUS], AVG([PL_US]) AS [PL_US], AVG() AS , AVG([X407_P_Pow]) AS [X407_P_Pow] FROM [dbo].[Reporting__KON001_X407] GROUP BY DATEADD(Hour, DATEDIFF(Hour, 0, RowDateTime), 0) ORDER BY [RowDateTime] " queryout "c:\test.xls" -c -CRAW'

the output is below but there is no excel file :-

usage: bcp {dbtable | query} {in | out | queryout | format} datafile   [-m maxerrors]            [-f formatfile]          [-e errfile]   [-F firstrow]             [-L lastrow]             [-b batchsize]   [-n native type]          [-c character type]      [-w wide character type]   [-N keep non-text native] [-V file format version] [-q quoted identifier]   [-C code page specifier]  [-t field terminator]    [-r row terminator]   [-i inputfile]            [-o outfile]             [-a packetsize]   [-S server name]          [-U username]            [-P password]   [-T trusted connection]   [-v version]             [-R regional enable]   [-k keep null values]     [-E keep identity values]   [-h "load hints"]         [-x generate xml format file] NULL

Any Ideas?

Answer 5

hi,

you can connect to sql server from ms excell

open a new excel sheet

then click on data. import external data.

then goto new database query

regards

 

Answer 6

Thats right, but I want to automate this not manual.

Any Ideas please help?

Answer 7

I got it, I used a Bulk Copy Program (BCP) like:-

bcp Test.dbo.HourlyData out C:\Test.xls -c -t\t -T -Sserver\Intsance

Answer 8

This works good.

exec master..xp_cmdshell'bcp "select * from dbname.owner.tablename" queryout C:\queryoutput.xls -o "C:\querycommanddetails.txt" -T -c -C RAW'

Answer 9

This works good.

exec master..xp_cmdshell 'bcp "select * from dbname.owner.tablename" queryout C:\queryoutput.xls -o "C:\querycommanddetails.txt" -T -c -C RAW'

Answer 10

exec master..xp_cmdshell 'bcp "select * from dbname.owner.tablename" queryout C:\queryoutput.xls -o "C:\querycommanddetails.txt" -T -c -C RAW

I used this query and its showing output as NULL...

Please help me where is the issue,Its urgent!!!

Answer 11

Yes, NULL is the expected output. xp_cmdshell returns a result set consisting of the lines printed from the command you supply. If there is no output, you get a single-row result set with the value NULL.

Normally, BCP prints the number of rows exported/imported and some other information, but you have opted to use the -o option to redirect the output to a file. Remove the -o option, if you want to see the output from BCP in the result set.

Or... since you are piggybacking on an old thread, start a new thread and tell us what you really want to achieve.

---

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

Answer 12

it worked! thank you for posting this solution!