Text File Input

Onboarding text files ..

Some of the Orders data that Steel Wheels process are in a text format. In this guided demo, you will flatten the list, create capture groups, replace text, and finally format the order_value.

In this Lab, you will configure the following steps:

• Text File Input

• Flattener

• RegEx Evaluation

• Replace in String

• Select values

The lab gives you an idea of some of the steps required to load flat files into a database table.

Lets take a look at the data, which will give us an idea of how to approach a possible solution.

• each line is a record

• 3rd line is 2 records: 'order status' & 'order date'

• Order Value: in $

• white space

So what do we need to do to get this into a database table?

• Flatten rows

• Extract values and associated with new data stream fields

• String cut

• Format fields - Date / Order Value

---

To create a new transformation

1. In Spoon, click File > New > Transformation.

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

• By clicking New, then Transformation

• By using the CTRL-N hot key

File: /home/installer/Workshop--Data-Integration/Module 2 - Working with Flat Files/Lab 1 - Read Text File

1. Text File Input

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.

1. Drag the ‘Text File Input’ step onto the canvas.

2. Double-click on the step, and configure the following properties:

Because the sample file is located in the same directory where the transformation resides, a good approach to naming the file in a way that is location independent is to use a system variable to parameterize the directory name where the file is located. In our case, the complete filename is:

${Internal.Transformation.Filename.Directory}/orders.txt

3. Click on the ‘Content’ tab and configure the following properties:

4. Click on ‘Get Fields’ button.

Click on the ‘Fields’ tab and notice the following properties:

The dataset is associated with ‘Field1’ with a data type of String, in the data stream.

5. Close the Step.

➡️ Next: Flatten the rows

2. Flattener

The Flattener step allows you flatten data sequentially.

1. Drag the ‘Flattener’ step onto the canvas.

2. Create a hop from the read order list step.

3. Double-click on the step, and configure the following properties:

4. Close the step.

The data has now been flattened into records. This step enables you to define new target fields that match the number of repeating records. So Target field 1 will map to repeating record 1, and so on..

➡️ Next: Create capture Groups using RegEx

3. RegEx Evaluation

This step type allows you to match the String value of an input field against a text pattern defined by a regular expression. Optionally, you can use the regular expression step to extract substrings from the input text field matching a portion of the text pattern into new output fields. This is known as "capturing".

In our example, we’re going to extract and create two capture groups order_status and order_date based on the regex expression: (Delivered|Returned):(.+)

1. Drag the ‘RegEx Evaluation’ step on to the canvas.

2. Create a hop from the ‘flatten rows’ step.

3. Double-click on the step, and configure the following properties:

You will also need to set Trim: both for each field. This will ensure all white space is removed and the exact length of the field is returned.

4. Close the step.

➡️ Next: Replace text & white space

---

Summary

• This RegEx uses 2 constructs, denoted by the brackets, and separated by a full colon.

• (Delivered | Returned) – match against Delivered or Returned.

• (.+) matches any character

• You can Test regEx to see if the capture groups are correctly defined.

A good introduction can be found at:

regex101: build, test, and debug regexregex101

Link to: Online RegEx engine

4. Replace in String

Replace in string is a simple search and replace. It also supports regular expressions and group references. Group references are picked up in the replace by string as $n where n is the number of the group.

Time to tidy up the order_value stream field data. In this step, you replace the Order Value: with ‘nothing’.

1. Drag the ‘Replace in String’ step onto the canvas.

2. Create a hop from the ‘parse delivered’ step.

3. Double-click on the step, and configure the following properties:

4. Close the step.

Ensure you have correctly entered the Search: Order Value: $[white space here]

➡️ Next: Tidy up and format datastream fields

5. Select Values

The Select Values step is useful for selecting, removing, renaming, changing data types and configuring the length and precision of the fields on the stream. These operations are organized into different categories:

• Select and Alter — Specify the exact order and name in which the fields should be placed in the output rows

• Remove — Specify the fields that should be removed from the output rows

• Meta-data - Change the name, type, length and precision (the metadata) of one or more fields

1. Drag the Select values step onto the canvas.

2. Create a hop from the ‘discard texts’ step.

3. Double-click on the step, and configure the following properties:

Fieldname	Data Type	Format
order_value	Number	#.00
order_date	Date	MMM yyy

➡️ Next: Finally execute the transformation

6. RUN

Run Transformation

1. Click the Run button in the Canvas Toolbar.

2. Click on the Preview tab.