Solved

How can I export data from  SQL2005 files to an excel sheet?

Posted on 2008-11-03
4
461 Views
Last Modified: 2013-11-16
Hi people,

I have a memory stick of 4 GB which contains a liitle data base in SQL 2005. This device was given to me in order to extract all data and put  data in an excel sheet. I have no experience at all with SQL and I do not know how to archive that.all the data can access on the device but I do not know how to export it as I could not find any options to do so.
I attached two pictures to give you an idea of what I see when accessing the device and data on it.

I look forward to your reply!
SQL2005.bmp
SQL-Inventory-1.bmp
0
Comment
Question by:WLMN
  • 2
  • 2
4 Comments
 
LVL 31

Accepted Solution

by:
James Murrell earned 500 total points
Comment Utility
this should help  http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1306800,00.html

Step 1: Create a SQL Server BI project and add a Data Flow Task

In this section, you will be creating a Business Intelligence project and changing the name of the default package object. Since it is data-related, you will be adding a Data Flow Task. You will also be adding a DataReader component to the data flow.

Create a business intelligence project Ch 5 as described in Chapter 2 or Chapter 3.
Change the default name of package from Package.dtsx to TableToXls.dtsx.
Drag and drop a Data Flow Task from the Toolbox onto the Control Flow page.
Click open the Data Flow tab, which displays the Data Flow page.
Now, you will be able to access the Data Flow Items of the Toolbox consisting of Data Flow Sources, Data Flow Destinations, and Data Flow Transformations (refer to Chapter 1).

Drag and drop a DataReader Source from the Data Flow Sources group onto the Data Flow page.
Return to Top

Step 2: Configure the DataReader's Connection Manager

Configuring the DataReader source that connects to the local SQL Server 2005 has been described in earlier chapters. Here, only a couple of the images relevant to this chapter will be shown.

Right-click inside the Connection Managers page below the Canvas, and from the drop-down choose New ADO.Net Connection&.
If you are continuing with this chapter after Chapter 4, you will see the Configure ADO.NET Connection Manager screen displaying the previously configured connection manager. If you need to create a new one, follow the steps shown in the previous chapter.

Click on the OK button in the Configure ADO.NET Connection Manager window.
A connection manager, Localhost.MyNorthwind.sa, will be added to the Connection Manager's page.

Return to Top

Step 3: Set up the DataReader Source for SQL Server data

Right-click the DataReader Source, in the drop-down menu.
Choose the Edit& menu item in the drop-down menu.
This opens the Advanced Editor for DataReader Source. At first, you need to indicate a connection manager that the DataReader can use.

In the Advanced Editor for DataReader Source that gets displayed click on the Connection Managers tab.
Click on an empty area (in grey) below the list heading, Connection Manager.
Here, you will see the connection manager that you added in step 1.

Choose this Connection Manager.
Next, click on the Component Properties tab to open the properties of the DataReader component.
Here, you will notice that this requires an SQLCommand (the only empty field now).

Click on the ellipsis button along its side to display a text editor where you can type in your SQLCommand.
You may also directly type-in the SQL Command:

SELECT CustomerID, CompanyName, Address, City, PostalCode
FROM Customers

Click on the Refresh button.
This query will allow the DataReader to read the data from the five columns. A sample of the table data is shown in the following screenshot, taken from SQL Server 2005 Management Studio. If you recall, these were the same columns that were used in the previous chapter as well.



Click on the Column Mappings tab.
This will open the Column Mappings page showing the columns that are the output of the DataReader.

In the last tab on this editor, Input and Output properties, you can add/ remove items from the External Columns, the Output Columns and the DataReader Error output. For this tutorial, no modifications are made.

Click on the OK button in the above window.
This completes the configuration of the DataReader which brings five columns from the SQL 2005 Server.

Return to Top

Step 4: Install a Character Map for SQL Server data transformation

The Character Map transformation is described in Chapter 1, but here you will be experimenting with this transformation. The transformation manipulates the text string that is coming to it and outputs the manipulated string. For example, in the screenshot we have just seen above, the CompanyName has mixed case. Using this transformation, we will capitalize all the characters that appear in the CompanyName column before it is written to an Excel FileAlfreds Futterkiste will become ALFREDS FUTTERKISTE, etc.

Drag and drop a Character Map data flow item from the Data Flow Transformations Group in the Toolbox onto the Data Flow page of the Canvas.
Right-click on the DataReader Source and from the drop-down click on the Add Path menu item.
From the displayed window, Data Flow, choose Character Map for the To: field as shown.


Click OK in the above window, and the following window is displayed.


Here, you need to indicate the output from this component, from the drop-down shown.

Choose DataReader Output Source from the drop-down.
At present, you will not be dealing with any errors in this tutorial. When you choose the above option, the OK button gets enabled.

Click on the OK button in the above window.
This establishes the path from the Data Reader Source to the Character Map data flow component. The path is established but it still needs configuration.

Right-click the Character Map component and from the drop-down menu choose, Edit.
This opens the Character Map Editor, as shown in the following screenshot. Place a check mark for the Company Name column.


If the default as shown in the above were to be chosen, then an extra column will be added to the output. We choose the option In-place change from the drop-down.

Click on the cell, New Column, under Destination in the above window. From the drop-down choose, In-place change.
Click just below the Operation list-header and from the drop-down list choose Upper Case as shown in the following screenshot.
The output alias remains the same as it is an in-line change.



Click on the button OK in the pick-up list and to the OK button in the Character Map Transformation Editor.
This completes the Character Map configuration.

Return to Top

Step 5: Add an Excel destination and create path to Character Map

In this step, we will add an Excel Destination. We will then establish a path from the Character Map to Excel Destination.

Add an Excel Destination component from the Data Flow Destinations group in the toolbox to the Data Flow page.
This can be accomplished either by double-clicking the component in the Toolbox or a drag-and-drop operation.

Right-click Character Map and from the drop-down menu choose Add Path.
This opens up the window, Data Flow, which allows you to establish a data flow path, and displays the "From:" location as Character Map.

Click on the drop-down along "To:", which shows both the Excel Destination as well as the DataReader Source.
Choose the Excel Destination and click OK to the screen.
This opens up the Input Output Selection window that shows the available output and input windows. The Output window is empty whereas the input shows Excel Destination Input. The path should connect from the Character Map to the Excel Destination Input.

Choose the above options and click on the OK button on this screen.
You will see a green line connecting the Character Map Data Flow Component to the Excel Destination, as shown in the next screenshot. Alternately, the process of establishing the path can be simplified by just picking the green dangling line from Character Map and dropping it onto the Excel Destination object on the Data Flow page. As seen in the next screenshot, you may also edit the path after it is created by right-clicking on this green line and choosing the Edit& drop-down menu item.



Return to Top

Step 6: Configure the Microsoft Excel Destination component

The data is on its way through the path, represented by the green line in the previous step. The Excel Destination also requires a connection manager.

The Excel Destination connects to an MS Excel on your hard dive using the connection properties defined in a connection manager.

Right-click the Excel Destination and from the drop-down menu choose Edit.
This displays the Excel Destination Editor. Excel requires an OLE DB connection manager and if there are no configured connection managers (by you or a previous user) the drop-down will be empty.

Click on the New& button.
The Excel Connection Manager window gets displayed as shown in the next screenshot. Here, you need to use the Browse button and pick the Excel file as the destination. The data will be written to the destination when the package is run.

Open Windows Explorer and create an Excel file in the C:drive. For this tutorial, TableToXls.xls is chosen.
Besides connecting to an existing file, the Excel Connection Manager supports creating a file on the folder of your choice in the machine using the Browse& button.

Now browse to the newly created file using the Browse& button and choose this file.
Click on the OK button in the Excel Connection Manager window.


For the Data Access Mode, accept the default, Table or View.
You have to indicate the name of the Excel sheet that will be used. (Do not click on the drop-down for locating the sheet. The drop-down will show the three Excel sheets that are found in a newly created Excel worksheet file; all of them having just one column each.)

Click on the New& button.
You are creating a new Excel sheet. This pops-up a Create Table window showing the columns that are being piped into the component, as shown in the following screenshot.



Click on the OK button, shown in the previous screenshot.
A new Excel Sheet, Excel Destination, will be added to the TableToXLS.xls file. If you now open and review this file (TableToXls.xls) you will see the column headers are added to this sheet.

Click on the Mappings in the left-hand-side pane of the Excel Destination Editor, which shows the mappings from the input to the output.
This shows all the columns from the Character Map Data Flow Component being written to the destination file, as shown in the following screenshot.



Click on the OK button in this window. The package is now completely configured and ready for execution.
Return to Top

Step 7: Test data transfer from SQL Server table to Excel Spreadsheet

Right-click the TableToXls.dtsx in the Solution Explorer and from the drop-down choose, Execute Package.
All three components on the Data Flow page turn green, indicating that the package executed successfully without errors. You may review the Progress tab in the 'Canvas', which shows all the details of the execution of the package.

Now open up the TableToXLs.xls file and review.
A few rows of data are shown in the next screenshot. Notice that the Character Map Data Transformation component has capitalized all the characters in the CompanyName column.


0
 

Author Comment

by:WLMN
Comment Utility
Hi,
Thank you for the above information.
In order to try the above steps I would like to know what i need to have installed on my computer in order to archive what i want.
 I've installed SQL 2005 but I do not know how to create a Business Intelligence project ! Do I need to install to create business intelligence project?
0
 
LVL 31

Expert Comment

by:James Murrell
Comment Utility
0
 

Author Comment

by:WLMN
Comment Utility
Hi,

The thing is I've installed SQL 2005 but for some reason I do not have installed or cound find on my conputer SQL Server Integration Services. If I go to "All Programs" - "Microsoft SQL server 2005" i only have one option " Configuration Tools".
So....What do I need to do to install or access SQL Server Integration Services in order to run Business Intelligence project?
Sorry people but this my first time when dealing with that!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now