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.
For this recipe, you will need a database with outdoor products with the structure explained in Appendix, Data Structures.
Carry out the following steps:
- Create a new transformation.
- Drop a Table input step into the canvas. Enter the following SQL statement:
SELECT innerj.desc_product, categories.category, innerj.price FROM products innerj INNER JOIN categories ON innerj.id_category = categories.id_category
- From the Output category, add a Text file output step.
- In the Filename textbox under the File tab, type or browse to the name of the destination file.
- In the Extension textbox, leave the default value
txt. - Check the Do not create file at start checkbox. This checkbox prevents the creation of the file when there is no data to write to it.
- Under the Content tab, leave the default values.
- Under the Fields tab, fill in the grid as shown in the following screenshot:
- Running the transformation, a new text file will be created containing the list of products.
The Text file output step allows you to generate files. In this recipe, you used it to generate a Comma Separated Values (CSV) file with data coming from a database.
Under the File tab, you entered the path and name of the file. Here you also have several options to include date or time in different formats as part of the name of the file. For this example, you didn't have to use those textboxes.
In the generated file you can see that the first column contains a line with the headers. Those headers are generated when the Header option from the Content tab is checked.
Under the Fields tab of this step, you must include the destination fields, including their types and formats. If you need it, you can include a field more than once.
Tip
If you don't specify any field, the step will write all the fields from the previous step. This could be useful when you don't know the exact names of the fields or when these fields change dynamically.
Under this same tab, the Null column specifies the string that will be written in case of a null value.
Finally, if you specify the Length of each column, a fixed width file will be created.
Here are some considerations that make the process of writing files more flexible.
If you want to change the name of a header, you could insert a Select values step from the Transform category just before the Text file output step. Under the Select & Alter tab, select the fields you want to rename and give them a better description. For example, you could select the desc_product fieldname and rename the field as Product.
In order to send all the other fields toward the Text file output step, you also have to check the Include unspecified fields, ordered by name option.
When you write a file and tell Kettle which fields to write to that file, you have the option of specifying the format to apply to those fields. That is particularly useful when you have numeric or date fields.
In both cases, you may specify a format using a mask of patterns and symbols.
In the case of numeric fields, you can find more information about formats at the following URL:
http://java.sun.com/javase/6/docs/api/java/text/DecimalFormat.html
In the case of date fields, you will find a complete reference at the following URL:
http://java.sun.com/javase/6/docs/api/java/text/SimpleDateFormat.html