SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 How to save sql output to excel file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Janniek
Starting Member

4 Posts

Posted - 07/26/2013 :  09:02:10  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3573 Posts

Posted - 07/26/2013 :  09:54:34  Show Profile  Reply with Quote
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.
Go to Top of Page

Janniek
Starting Member

4 Posts

Posted - 07/26/2013 :  10:45:42  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3573 Posts

Posted - 07/26/2013 :  10:54:42  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/26/2013 :  11:36:07  Show Profile  Reply with Quote
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
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2011 Posts

Posted - 07/29/2013 :  03:31:35  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

Janniek
Starting Member

4 Posts

Posted - 08/06/2013 :  08:40:49  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3573 Posts

Posted - 08/06/2013 :  08:53:57  Show Profile  Reply with Quote
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?
Go to Top of Page

Janniek
Starting Member

4 Posts

Posted - 08/06/2013 :  09:17:29  Show Profile  Reply with Quote
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)
 



Edited by - Janniek on 08/06/2013 09:18:30
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000