How to save sql output to excel file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

How to save sql output to excel file

Author

Topic

Janniek
Starting Member

4 Posts

Posted - 2013-07-26 : 09:02:10

Hello,

I got a SQL command that I need to execute every day. Afther executing I need to copy/paste the grid results to a new Excel file.

There is a problem with copying the data. The newline \n are deleted.

So now I need to save the results without copy/pasting it.

The result need to be .xls or .xlsx.

The server is on a 64bit machine.
I use SQL manager 2008 to execute the SQL command.

This is the SQL command:

SELECT DISTINCT ART.artNr AS Artnr, ART.artProductNummer AS Prodnr, ART.artEAN AS EAN, ART.artOmsComm AS Omschrijving, ART.artVerkpPrijsInc AS OFBprijs, 
ART.artAdvVerkpPrijs AS Adviesprijs_MPLprijs, ART.artInkpPriis AS Inkpprijs, AVL.avlAantal AS Voorraad, AVL.avlMagLok AS Maglok, ART.artOms AS Brochure, 
[tbl artikel subgroep].artsgOmsch, tblArtikelSsg.ssgNaam
FROM [tbl artikel] AS ART INNER JOIN
tblArtVrdOpLokatie AS AVL ON ART.artId = AVL.avlArtId INNER JOIN
tblArtikelLeverancier ON ART.artId = tblArtikelLeverancier.arlartId INNER JOIN
[tbl artikel subgroep] ON ART.artArtsgId = [tbl artikel subgroep].artsgId LEFT OUTER JOIN
tblArtikelSsg ON ART.artSsgId = tblArtikelSsg.ssgId

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-26 : 09:54:34

There are a few different ways to export data to Excel - see here: http://support.microsoft.com/kb/321686

Perhaps the easiest way to automate it would be to use the SSIS or Import/Export Wizard to create an SSIS package and schedule it for execution at the appropriate time each day using SQL Server Agent.

Janniek
Starting Member

4 Posts

Posted - 2013-07-26 : 10:45:42

quote:
Originally posted by James K

There are a few different ways to export data to Excel - see here: http://support.microsoft.com/kb/321686

Perhaps the easiest way to automate it would be to use the SSIS or Import/Export Wizard to create an SSIS package and schedule it for execution at the appropriate time each day using SQL Server Agent.

Yes, the SQL import and export wizzard (DTSWIZZARD.exe)does not show a destenation like Excel or Acces

The link you send me is the way arount I need SQL to excel.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-26 : 10:54:42

Had it backward - there are similar pages on MSDN for exporting - http://msdn.microsoft.com/en-us/library/cc952922(v=sql.100).aspx

Not sure why you don't see Excel as a destination. Do you have Excel on the box? I don't think that is a requirement, but I don't know.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-26 : 11:36:07

see this and double check if you're following the same steps

http://dotnetslackers.com/articles/sql/Importing-MS-Excel-data-to-SQL-Server-2008.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-07-29 : 03:31:35

Another option - particuarly as it's a regular job - is to export the output from SQL Server to Excel using powershell. This will give you excellent granular control

http://www.sqlserver-dba.com/2013/05/sql-server-export-to-excel-with-powershell.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Janniek
Starting Member

4 Posts

Posted - 2013-08-06 : 08:40:49

quote:
Originally posted by visakh16

see this and double check if you're following the same steps

http://dotnetslackers.com/articles/sql/Importing-MS-Excel-data-to-SQL-Server-2008.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I need it backwards. I need SQL to Excel.

I know how the inport/export wizard works. But it won't show Excel as output

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-06 : 08:53:57

I am not able to think of a reason why you don't have Excel as a destination in Import/Exort Wizard. I just tried to export from a box that does not have Microsoft office installed - it just has SQL 2012 installed and I am able to see Excel as a destination. What destinations do you see? Do you see a flat file destination?

Janniek
Starting Member

4 Posts

Posted - 2013-08-06 : 09:17:29

quote:
Originally posted by James K

I am not able to think of a reason why you don't have Excel as a destination in Import/Exort Wizard. I just tried to export from a box that does not have Microsoft office installed - it just has SQL 2012 installed and I am able to see Excel as a destination. What destinations do you see? Do you see a flat file destination?

I got it fixed!!! when i open the Import/Export wizard from the SQL manager it shows the excel dest.

I got 2 warnings but do they mean trouble?

this isthe message i get after executing:

The execution was successful

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Saving (Success)

- Prepare for Execute (Success)

- Pre-execute (Success)

- Executing (Warning)
Messages
Warning: Preparation SQL Task 1: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (SQL Server Import and Export Wizard)
 
Warning: Preparation SQL Task 1: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (SQL Server Import and Export Wizard)
 

- Copying to `Query` (Success)
8848 rows transferred 

- Post-execute (Success)
Messages
Information 0x4004300b: Data Flow Task 1: "component "Destination - Query" (52)" wrote 8848 rows.
 (SQL Server Import and Export Wizard)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources