locked
How to export SQL data to Excel SpreadSheet using SQL Query? RRS feed

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

    Wednesday, August 2, 2006 1:03 PM

Answers

  • 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
     

    Thursday, August 3, 2006 9:04 AM

All replies

  • use SQL Server Integration Services (SSIS)
    • Proposed as answer by Naomi N Wednesday, December 25, 2013 7:59 PM
    Wednesday, August 2, 2006 1:32 PM
  •  

     

    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

     

     

    Wednesday, August 2, 2006 1:35 PM
  •  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?

    Wednesday, August 2, 2006 1:35 PM
  •  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(Angel) AS Angel,
    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?

    Wednesday, August 2, 2006 2:29 PM
  • 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

     

    Wednesday, August 2, 2006 2:51 PM
  • Thats right, but I want to automate this not manual.

    Any Ideas please help?

    Thursday, August 3, 2006 6:06 AM
  • 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
     

    Thursday, August 3, 2006 9:04 AM
  • 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'

    Monday, December 17, 2012 8:07 PM
  • 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'

    Monday, December 17, 2012 8:08 PM
  • 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!!!

    • Proposed as answer by amirali vahidinia Wednesday, December 25, 2013 5:43 AM
    • Unproposed as answer by amirali vahidinia Wednesday, December 25, 2013 5:44 AM
    • Proposed as answer by SM- Wednesday, December 25, 2013 6:43 AM
    • Unproposed as answer by SM- Wednesday, December 25, 2013 7:15 AM
    Wednesday, December 25, 2013 4:54 AM
  • 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, esquel@sommarskog.se
    • Proposed as answer by Naomi N Wednesday, December 25, 2013 8:00 PM
    Wednesday, December 25, 2013 9:07 AM
  • it worked! thank you for posting this solution!
    Wednesday, March 19, 2014 4:08 PM