Re: Is there a mac version of Excel reshaper plug-in? Bill Lyons Jun 5, 2015 8:46 AM ( in response to Brian Sharratt ) I don't believe so, but with Tableau 9.0, the reshaper add-in is obsolete. 9.0's Data Source Editor and Data Interpreter are far more flexible, simpler and faster.
![]() By: Ray Barley | Updated: 2009-05-28 | Comments (12) | Related: More >Integration Services Data Flow Transformations
Problem
I have some sales forecast data that I get from the business users in an Excel spreadsheet. I need to load this data into a SQL Server database table. The forecast contains product categories on the rows and the sales forecast for each month is on the columns. I'd like to use SQL Server Integration Services (SSIS) to perform this recurring task. How can I transform this data into a table that has Category, Month and Sales Forecast columns?
Solution
SSIS has a Data Flow Transformation called Unpivot which can do exactly what you need. Let's assume that your spreadsheet looks like this:
Although not shown above, there are 12 columns for forecast data, January through December. Assume we want to load the data from the Excel spreadsheet into the following table:
We'll create a simple SSIS package to process the Excel spreadsheet. The package will have the following Data Flow:
The following are the main points about the above data flow:
Before we start the explanation of the Unpivot, let's add a data viewer after the Unpivot and run the package. The data viewer allows us to see the contents of the data flow. To add a data viewer, right click the line connecting the Unpivot and Script Component, select Data Viewers, Add, then Grid. The data viewer output is shown below:
![]()
As you can see the Unpivot component has performed the transformation that we need, taking the Sales Forecast values in the columns and turning them into rows.
The Unpivot Transformation Editor is shown below:
The Available Input Columns grid contains the list of columns in the data flow as read from the spreadsheet. The CATEGORY column has Pass Through checked which means the column value simply passes through this component unchanged. You can see the CATEGORY column in the data viewer output above. The columns that have the checkbox to their left checked are unpivoted; i.e. these columns become rows. All of the checked input columns are being transformed to the SalesForecast column, one per row, as shown in the Destination Column above. Referring back to the data viewer output, you can see the SalesForecast column. The Pivot key value column name is a new column that is added to the data flow; the value of this column is specified in the Pivot Key Value column. The Pivot Key Value allows you to specify the value of your choice for each column in the original spreadsheet. The Pivot Key Value shown is the first day of the month specified in the Input Column. You can see the MonthDay column in the data viewer output above.
The Script Component has the following single line of code that prepends the package variable ForecastYear to the MonthDay column in the data flow to assign a string value to the output column ForecastDate that can be implicitly converted to a DATETIME:
To test executing the package, use the following DTEXEC command line, specifying a value for the ForecastYear package variable (add the appropriate path before the package name):
Note that there is a semi-colon separating the package variable and the value. Also the variable name (ForecastYear) is case-sensitive.
Next Steps
Last Updated: 2009-05-28 About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.
View all my tips
Related Resources ![]() ![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2023
Categories |