Pentaho Data Integration 4 Cookbook -- Table of Contents -- Pentaho Data Integration 4 Cookbook -- Credits -- About the Authors -- About the Reviewers -- www.PacktPub.com -- Support files, eBooks, discount offers and more -- Why Subscribe? -- Free Access for Packt account holders -- Preface -- What this book covers -- What you need for this book -- Who this book is for -- Conventions -- Reader feedback -- Customer support -- Downloading the example code -- Errata -- Piracy -- Questions -- 1. Working with Databases -- Introduction -- Sample databases -- Pentaho BI platform databases -- Connecting to a database -- Getting ready -- How to do it... -- How it works... -- There's more... -- Avoiding creating the same database connection over and over again -- Avoiding modifying jobs and transformations every time a connection changes -- Specifying advanced connection properties -- Connecting to a database not supported by Kettle -- Checking the database connection at run-time -- Getting data from a database -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Getting data from a database by providing parameters -- Getting ready -- How to do it... -- How it works... -- There's more... -- Parameters coming in more than one row -- Executing the SELECT statement several times, each for a different set of parameters -- See also -- Getting data from a database by running a query built at runtime -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Inserting or updating rows in a table -- Getting ready -- How to do it... -- How it works... -- There's more... -- Alternative solution if you just want to insert records -- Alternative solution if you just want to update rows -- Alternative way for inserting and updating -- See also.

Inserting new rows where a simple primary key has to be generated -- Getting ready -- How to do it... -- How it works... -- There's more... -- Using the Combination lookup/update for looking up -- See also -- Inserting new rows where the primary key has to be generated based on stored values -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Deleting data from a table -- Getting ready -- How to do it... -- How it works... -- See also -- Creating or altering a database table from PDI (design time) -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Creating or altering a database table from PDI (runtime) -- How to do it... -- How it works... -- There's more... -- See also -- Inserting, deleting, or updating a table depending on a field -- Getting ready -- How to do it... -- How it works... -- There's more... -- Insert, update, and delete all-in-one -- Synchronizing after merge -- See also -- Changing the database connection at runtime -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Loading a parent-child table -- Getting ready -- How to do it... -- How it works... -- See also -- 2. Reading and Writing Files -- Introduction -- Reading a simple file -- Getting ready -- How to do it... -- How it works... -- There's more... -- Alternative notation for a separator -- About file format and encoding -- About data types and formats -- Altering the names, order, or metadata of the fields coming from the file -- Reading files with fixed width fields -- Reading several files at the same time -- Getting ready -- How to do it... -- How it works... -- There's more... -- Reading unstructured files -- Getting ready -- How to do it... -- How it works... -- There's more... -- Master/detail files -- Log files -- Reading files having one field by row.

Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Reading files with some fields occupying two or more rows -- Getting ready -- How to do it... -- How it works... -- See also -- Writing a simple file -- Getting ready -- How to do it... -- How it works... -- There's more... -- Changing headers -- Giving the output fields a format -- Writing an unstructured file -- Getting ready -- How to do it... -- How it works... -- There's more... -- Providing the name of a file (for reading or writing) dynamically -- Getting ready -- How to do it... -- How it works... -- There's more... -- Get System Info -- Generating several files simultaneously with the same structure, but different names -- Using the name of a file (or part of it) as a field -- Getting ready -- How to do it... -- How it works... -- Reading an Excel file -- Getting ready -- How to do it... -- How it works... -- See also -- Getting the value of specific cells in an Excel file -- Getting ready -- How to do it... -- How it works... -- There's more... -- Labels and values horizontally arranged -- Looking for a given cell -- Writing an Excel file with several sheets -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Writing an Excel file with a dynamic number of sheets -- Getting ready -- How to do it... -- How it works... -- See also -- 3. Manipulating XML Structures -- Introduction -- Reading simple XML files -- Getting ready -- How to do it... -- How it works... -- There's more... -- XML data in a field -- XML file name in a field -- ECMAScript for XML -- See also -- Specifying fields by using XPath notation -- Getting ready -- How to do it... -- How it works... -- There's more... -- Getting data from a different path -- Getting data selectively -- Getting more than one node when the nodes share their XPath notation.

Saving time when specifying XPath -- Validating well-formed XML files -- Getting ready -- How to do it... -- How it works... -- See also -- Validating an XML file against DTD definitions -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Validating an XML file against an XSD schema -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Generating a simple XML document -- Getting ready -- How to do it... -- How it works... -- There's more... -- Generating fields with XML structures -- See also -- Generating complex XML structures -- Getting ready -- How to do it... -- How it works... -- See also -- Generating an HTML page using XML and XSL transformations -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- 4. File Management -- Introduction -- Copying or moving one or more files -- Getting ready -- How to do it... -- How it works... -- There's more... -- Moving files -- Detecting the existence of the files before copying them -- Creating folders -- See also -- Deleting one or more files -- Getting ready -- How to do it... -- How it works... -- There's more... -- Figuring out which files have been deleted -- See also -- Getting files from a remote server -- Getting ready -- How to do it... -- How it works... -- There's more... -- Specifying files to transfer -- Some considerations about connecting to an FTP server -- Access via SFTP -- Access via FTPS -- Getting information about the files being transferred -- See also -- Putting files on a remote server -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Copying or moving a custom list of files -- Getting ready -- How to do it... -- How it works... -- See also -- Deleting a custom list of files -- Getting ready -- How to do it... -- How it works...

See also -- Comparing files and folders -- Getting ready -- How to do it... -- How it works... -- There's more... -- Comparing folders -- Working with ZIP files -- Getting ready -- How to do it... -- How it works... -- There's more... -- Avoiding zipping files -- Avoiding unzipping files -- See also -- 5. Looking for Data -- Introduction -- Looking for values in a database table -- Getting ready -- How to do it... -- How it works... -- There's more... -- Taking some action when the lookup fails -- Taking some action when there are too many results -- Looking for non-existent data -- See also -- Looking for values in a database (with complex conditions or multiple tables involved) -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Looking for values in a database with extreme flexibility -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Looking for values in a variety of sources -- Getting ready -- How to do it... -- How it works... -- There's more... -- Looking for alternatives when the Stream Lookup step doesn't meet your needs -- Speeding up your transformation -- Using the Value Mapper step for looking up from a short list of values -- See also -- Looking for values by proximity -- Getting ready -- How to do it... -- How it works... -- There's more... -- Looking for values consuming a web service -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Looking for values over an intranet or Internet -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- 6. Understanding Data Flows -- Introduction -- Splitting a stream into two or more streams based on a condition -- Getting ready -- How to do it... -- How it works... -- There's more... -- Avoiding the use of Dummy steps.

Comparing against the value of a Kettle variable.