Pentaho Data Integration 4 Cookbook: Over 70 recipes to solve ETL problems using Pentaho Kettle

Files are the most primitive, but also the most used format to store and interchange data. PDI has the ability to read data from all kind of files and different formats. It also allows you to write back to files in different formats as well.

Reading and writing simple files is a very straightforward task. There are several steps under the input and output categories of steps that allow you to do it. You pick the step, configure it quickly, and you are done. However, when the files you have to read or create are not simple—and that happens most of the time—the task of reading or writing can become a tedious exercise if you don't know the tricks. In this chapter, you will learn not only the basics for reading and writing files, but also all the how-tos for dealing with them.

In this recipe, you will learn the use of the Text file input step. In the example, you have to read a simple file with a list of authors' information like the following:

"lastname","firstname","country","birthyear"
"Larsson","Stieg","Swedish",1954
"King","Stephen","American",1947
"Hiaasen","Carl ","American",1953
"Handler","Chelsea ","American",1975
"Ingraham","Laura ","American",1964

Sometimes you have several files to read, all with the same structure, but different data. In this recipe, you will see how to read those files in a single step. The example uses a list of files containing names of museums in Italy.

The simplest files for reading are those where all rows follow the same pattern: Each row has a fixed number of columns, and all columns have the same kind of data in every row. However, it is common to have files where the information does not have that format. In many occasions, the files have little or no structure. Suppose you have a file with roller coaster descriptions, and the file looks like the following:

JOURNEY TO ATLANTIS
SeaWorld Orlando

Journey to Atlantis is a unique thrill ride since it is ...
Roller Coaster Stats
Drop: 60 feet 
Trains: 8 passenger boats
Train Mfg: Mack 

KRAKEN
SeaWorld Orlando

Named after a legendary sea monster, Kraken is a ...
Kraken begins with a plunge from a height of 15-stories ...
Roller Coaster Stats
Height: 151 feet
Drop: 144 feet
Top Speed: 65 mph 
Length: 4,177 feet
Inversions: 7 
Trains: 3 - 32 passenger
Ride Time: 2 minutes, 2 seconds 

KUMBA
Busch Gardens Tampa
...

As you can see, the preceding file is far from being...

When you use one of the Kettle steps meant for reading files, Kettle expects the data organized in rows, where the columns are the fields. Suppose that instead of having a file with that structure, your file has one attribute per row as in the following example:

Mastering Joomla! 1.5 Extension and Framework Development
Published: November 2007
Our price: £30.99

CakePHP 1.3 Application Development Cookbook: RAW
Expected: December 2010
Our price: £24.99

Firebug 1.5: Editing, Debugging, and Monitoring Web Pages
Published: April 2010
Our price: £21.99

jQuery Reference Guide
...

This file contains book information. In the file, each book is described in three rows: one for the title, one for the published or expected publishing date, and one row for the price.

There is no direct way to tell Kettle how to interpret these rows, but a simple transformation can do the trick.

When you use one of the Kettle steps devoted for reading files, Kettle expects one entity per row. For example, if you are reading a file with a list of customers, then Kettle expects one customer per row. Suppose that you have a file organized by rows, where the fields are in different columns, but some of the fields span several rows, as in the following example containing data about roller coasters:

Roller Coaster      Speed     Location                   Year
Kingda Ka           128 mph   Six Flags Great Adventure
                              Jackson, New Jersey        2005
Top Thrill Dragster 120 mph   Cedar Point
                              Sandusky, Ohio             2003
Dodonpa             106.8 mph Fuji-Q Highland
                              FujiYoshida-shi            2001
                              Japan
Steel Dragon 2000   95 mph    Nagashima Spa Land
                              Mie                        2000...

In this recipe, you will learn the use of the Text file output step for writing text files.

Let's assume that you have a database with outdoor products and you want to export a catalog of products to a text file.

A standard file generated with Kettle is a file with several columns, which may vary according to how you configured the Fields tab of the Output step and one row for each row in your dataset, all with the same structure. If you want the file to have a header, the header is automatically created with the names of the fields. What if you want to generate a file somehow different from that?

Suppose that you have a file with a list of topics for a writing examination. When a student has to take the examination, you take that list of topics and generate a sheet like the following:

Student name: Mary Williams
-------------------------------------------------------------
Choose one of the following topics and write a paragraph about it
(write at least 300 words)

1. Should animals be used for medical research?
2. What do you think about the amount of violence on TV?
3. What does your country mean to you?
4. What would happen if there were no televisions?
5. What would...

Sometimes, you don't have the complete name of the file that you intend to read or write in your transformation. That can be because the name of the file depends on a field or on external information. Suppose you receive a text file with information about new books to process. This file is sent to you on a daily basis and the date is part of its name (for example, newBooks_20100927.txt).

"Title","Author","Price","Genre"
"The Da Vinci Code","Dan Brown","25.00","Fiction"
"Breaking Dawn","Stephenie Meyer","21.00","Children"
"Foundation","Isaac Asimov","38.50","Fiction"
"I, Robot","Isaac Asimov","39.99","Fiction"

There are some occasions where you need to include the name of a file as a column in your dataset for further processing. With Kettle, you can do it in a very simple way.

In this example, you have several text files about camping products. Each file belongs to a different category and you know the category from the filename. For example, tents.txt contains tent products. You want to obtain a single dataset with all the products from these files including a field indicating the category of every product.

Swedish Firesteel - Army Model|$19.97
Mountain House #10 Can Freeze-Dried Food|$53.50
Coleman 70-Quart Xtreme Cooler (Blue)|$59.99
Kelsyus Floating Cooler|$26.99
Lodge LCC3...

Kettle provides the Excel input step, in order to read data from Excel files. In this recipe, you will use this step to read an Excel file regarding museums in Italy. The file has a sheet with one column for the name of the museum and other for the city where it is located. The data starts in the C3 cell (as shown in the screenshot in the next section).

Getting ready

For this example, you need an Excel file named museumsItaly.xls with a museums sheet, as shown in the following screenshot:

You can download a sample file from the book's site.

One of the good things about Excel files is that they give you freedom to write anywhere in the sheets, which sometimes is good if you want to prioritize the look and feel. However, that could cause you troubles when it's time to automatically process the data in those files. Suppose that you have an Excel file with values for a couple of variables you'd like to set, as shown in the following screenshot:

In this example, you want to set values for three variables: Year, ProductLine, and Origin. The problem is that you don't know where in the sheet that table is. It can be anywhere, near the upper left corner of the sheet. As you cannot ask Kettle to scan somewhere near the upper next corner, you will learn in this recipe how to get that data with a simple transformation.

Writing an Excel file with Kettle has a lot in common with writing a text file. Except for a couple of settings specific for Excel files, configuring an Excel Output step is quite similar to configuring a Text file output step. One of the differences is that when you write an Excel file, you add a sheet to the file. What if you want to write more than one sheet in the same file?

Suppose you have a datasource containing books and their authors and you want to create an Excel file with two sheets. In the first sheet, you want the authors and in the second, the books' titles. This recipe teaches you how to do this.

When you generate an Excel file, you usually generate it with a single sheet. You can however generate a file with more sheets. With PDI, you can generate an Excel file with several sheets even if you don't know in advance how many sheets you will generate, or the name of those sheets.

In this recipe, you will create such an Excel file. Your file will have book title information separated in different sheets depending of the genre of the books.