Export SQL Server data to an Excel file

Question

Export SQL Server data to an Excel file

HBK_4700

SSCrazy Eights

Points: 8932
More actions
March 17, 2010 at 11:51 am

#156790

I need to export some tables data to an excel file. Please let me know steps. Thanks!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

The easiest way to do this is right-click the database name in Management Studio, pick Tasks then Export Data. But that's only good if you just want to do it one time. If you'll need to do it more regularly, you might want to take a look at SSIS, which is more complicated to set up, but much more efficient at automating this kind of task.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

andresito Ten Centuries Points: 1032 More actions · Answer 2

The nice thing about doing the export using the SQL Server Export Wizard is that you can save the export as an SSIS package (it is one of your options at the end of the wizard). So...if you are unfamiliar with creating an SSIS package which results in data being written to an Excel file, the wizard will write it for you and you can then look at how the wizard created the file to familiarize yourself with SSIS.

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 3

andresito (3/17/2010)
The nice thing about doing the export using the SQL Server Export Wizard is that you can save the export as an SSIS package (it is one of your options at the end of the wizard). So...if you are unfamiliar with creating an SSIS package which results in data being written to an Excel file, the wizard will write it for you and you can then look at how the wizard created the file to familiarize yourself with SSIS.

I tried to export the data using import/export, thrown below error

saving to sql - description not found

andresito Ten Centuries Points: 1032 More actions · Answer 4

andresito

Ten Centuries

Points: 1032

March 17, 2010 at 1:48 pm

#1135528

What step were you able to get to in the wizard?

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 5

andresito (3/17/2010)
What step were you able to get to in the wizard?

step: SAVING

error: no description found

I did below steps

right click on database and tasks--> export

source sqlser

destination excel FILE

selected few table

save as SSIS package

Krishna Reddy-295451 SSC-Addicted Points: 452 More actions · Answer 6

Let us know What is the SQL Server Version you are using and in the final wizard before saving, are u saving the package in Sql server or file system. enter the description in the final step and verify once again.

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 7

still throwing the same error, I am using SQL Server 2005 dev edition 9.0.4273

Krishna Reddy-295451 SSC-Addicted Points: 452 More actions · Answer 8

Can add the screen shot of your final step Saving. Are you saving the package to FileSystem or to SQL Server

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 9

I am trying to save package to sql server. Please find the error as an attachment

Ron McCullough SSC Guru Points: 63877 More actions · Answer 10

Attach your jpg file ... scroll to bottom of this window, in the "Post Options" frame. click on the button labeled "Edit Attachments" browse to whatever drive on your local machine that has the file. Once located then click on the button labeled "Upload".

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 11

HBK_4700

SSCrazy Eights

Points: 8932

March 18, 2010 at 4:50 pm

#1136377

I have uploaded the screenshots...Thanks

Ron McCullough SSC Guru Points: 63877 More actions · Answer 12

Following GSquared's advice, and by some good fortune a major article in today's SSC (3/19/2010) Read this article and see if it helps.

http://www.databasejournal.com/features/mssql/article.php/3864246/SSIS-Packages---the-Simplest-Way-to-Create-Them.htm%5B/url%5D

Not let me repeat not, being a SSIS expert my meager recommendation is about all I can do to lend you some assistance.

If the article does NOT help repost attempting to most specific about the problem(s) you may have happening.

If the article does help post back so others may learn.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

andresito Ten Centuries Points: 1032 More actions · Answer 13

laddu4700 (3/18/2010)
I have uploaded the screenshots...Thanks

Looks like the package is executing successfully, except for saving the SSIS, which is what you want, so you can look at how it packages the Excel file creation that you are trying to do. The only change I would make to what you are doing is to select "File System" instead of "SQL Server" (I'm referring to SSIS_Package_Screenshot.JPG). This will prompt you to save the package locally (on your own machine). After you have done this, open Visual Studio (or BIDS, whatever you have), create or open an Integration Services Project. In Solution Explorer, right-click on SSIS Packages, select "Add Existing Package", select File System, and browse to the package you just saved. Open the file and take a look at the Data Flow. Hopefully, that answers your question.