SQL2005 - export to Excel

Posted on 2007-12-06
Last Modified: 2008-03-27
How do you export an SQL 2005 table to an Excel spreadsheet?  It was easy in 2003 but can't figure it out with 2005.  For that matter, I need to be able to import, as well.
Question by:dcass
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1

Expert Comment

ID: 20425228
Use Import Export Wizard or a DTS package,
DTS has been renamed as SQL Server Integration Services (SSIS) in SQL 2005

Expert Comment

ID: 20426296
SSIS is teh newer version of DTS in SQL 2005.
You need to create  project in VS 2.0 of type Integration services.
In the dataflow page, add two components one for Source of type OLEDB Source and other destination whihc is your Xcel destination.
Double click the componenets and set thier properties like the data source, table name etc.
save the project and run it...

hope this will help you move ahead.

Author Comment

ID: 20427862
So there is no tab for import/export in SQL 2005.  I have to install Visual Studio 2.0?
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


Author Comment

ID: 20428087
can I not just set up a query - select var1, var2 from datatable and then have some way to tell it to export to an Excel file and click Execute?

Expert Comment

ID: 20431541
if you wont to export values from the table then you can do that in excel.
for this you have to setup ODBC connection  betweeen your system and SQL server,
Open excel and call the table from there

In Excel 2007
 Data\from other sources\from SQL server
Connect to Database Server\{enter Servername}\{windows authentication}
Select the database
connect to specific table {NEXT}

Which version of Excel you using, Hope this helps {as excel 2003 } is similar too

Author Comment

ID: 20434030
I have excel 2003 - will try.

Accepted Solution

macentrap earned 500 total points
ID: 20435142
>Excel 2003
>Open Excel
> Data
> Import External Data
> New Database Query
> Select my System DSN from the list of databases

Will you be okay setting up ODBC connection?

Expert Comment

ID: 20439650
I think if you dont have sqlserver 2005 better go with importing from excel option.
macentrap have suggested the steps for the same.

Expert Comment

ID: 21227829
This seems rather an 'import to excel' solution rather than an 'export to excel' solution.  The effect is the same but the tools one works with are different.  This throws a search for working with MS SQL tools off.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question