Excel Writer

Working with Excel ..

Workshop - Microsoft Excel Writer

Steel Wheels intends to automate their half-yearly Sales and Expenses Report. The process for extracting, transforming, and loading (ETL) data has been delineated into several workflows. Upon the completion of these workflows, data will be automatically populated in a predefined Excel template.

In this workshop, you will configure the following steps:

Excel Writer
Block Step

Create a new Transformation

Any one of these actions opens a new Transformation tab for you to begin designing your transformation.

By clicking File > New > Transformation
By using the CTRL-N hot key

Excel Template

The various stages of the transformation write data to a template.xlsx. The template has 2 worksheets:

Sales Chart - this worksheet creates a 3D stacked graph
SourceData - worksheet.

Open the following template in Excel:

C:\Workshop--Data-Integration\Labs\Module 3 - Data Sources\Flat Files\Lab - Excel Writer\template.xlsx

SourceData - the datasheet. Transformations write to the required cells that are used to create the graph.

➡️ Next: Write Year

Write Year

The first workflow is to write the current Year to the SourceData worksheet in the template.xlsx

You can obviously change the Year value ..

Generate rows - Year

Generate rows outputs a specified number of rows. By default, the rows are empty; however, they can contain several static fields. This step is used primarily for testing purposes. It may be useful for generating a fixed number of rows, for example, you want exactly 12 rows for 12 months. Sometimes you may use Generate Rows to generate one row that is an initiating point for your transformation.

Start Pentaho Data Integration.

Windows - PowerShell

Set-Location C:\Pentaho\design-tools\data-integration
.\spoon.bat

Linux

cd
cd ~/Pentaho/design-tools/data-integration
./spoon.sh

Drag the ‘Generate Rows’ step onto the canvas.
Double-click on the step, and configure the following properties:

Close Step.

➡️ Next: Excel Writer - Year

Summary

Generates a record that holds the Year value – 2023 – in the year stream field.
The Excel template will also need to be formatted yyyy to interpret the Date.

Write Sales

The second workflow is to repeat the workflow for Sales, with the addition of a ‘Blockstep’, which ensures the ‘Write Year’ workflow has been completed, before writing to the template.xlsx.

Text file input - Read Sales

The Text File Input step is used to read data from a variety of different text-file types. The most commonly used formats include Comma Separated Values (CSV files) generated by spreadsheets and fixed width flat files.

The Text File Input step provides you with the ability to specify a list of files to read, or a list of directories with wild cards in the form of regular expressions. In addition, you can accept filenames from a previous step making filename handling more even more generic.

Drag the ‘Text file input’ step onto the canvas.
Double-click on the step, and configure the following properties:

Click on the Content tab, and configure the following properties:

Ensure the Header is selected.
No empty rows
Mixed Format

Click on the Fields tab, and click on ‘Get Fields’ button:

Returns the Header values as stream fields.

Click OK.

➡️ Next: Block Step - Wait Year

PreviousExcel NextXML

Last updated 15 days ago

Was this helpful?

Excel Writer

Workshop - Microsoft Excel Writer

Create a new Transformation

Excel Template

Write Year

Generate rows - Year

Windows - PowerShell

Linux

Summary

Excel Writer - Year

Write Sales

Text file input - Read Sales

Block until Step Finish - Wait Year

Excel Writer - Write Sales

Write Expenses

Read Expenses

Block Step - sales

Run Transformation