EXAMPLE - Quote Parameter

This example demonstrates how to use quote parameter for more sophisticated splitting of columns of data using the split transform.

Source:

In this example, the following CSV data, which contains contact information, is imported into the application:

LastName,FirstName,Role,Company,Address,Status
Wagner,Melody,VP of Engineering,Example.com,"123 Main Street, Oakland, CA 94601",Prospect
Gruber,Hans,"Director, IT",Example.com,"456 Broadway, Burlingame, CA, 94401",Customer
Franks,Mandy,"Sr. Manager, Analytics",Tricorp,"789 Market Street, San Francisco, CA, 94105",Customer

Transformationn:

When this data is pulled into the application, some initial parsing is performed for you:

When you open the Recipe Panel, you should see the following transforms:

column1

\n

\"

\"

column1

On pattern

','

5

\"

The first transform splits the raw source data into separate rows in the carriage return character (\r), ignoring all values between the double-quote characters. Note that this value must be escaped. The double-quote character does not require escaping. While there are no carriage returns within the actual data, the application recognizes that these double-quotes are identifying single values and adds the quote value.

The second transform splits each row of data into separate columns. Since it is comma-separated data, the application recognizes that this value is the column delimiter, so the on value is set to the comma character (,). In this case, the quoting is necessary, as there are commas in the values in column4 and column6, which are easy to clean up.

To finish clean up of the dataset, you can promote the first row to be your column headers:

Use row(s) as column names

Use a single row to name columns

1

You can remove the quotes now. Note that the following applies to two columns:

Address,Role

'\"'

''

true

Now, you can split up the Address column. You can highlight one of the commas and the space after it in the column, but make sure that your final statement looks like the following:

column1

On pattern

','

2

Notice that there is some dirtiness to the resulting Address3 column:

Use the following to remove the comma. In this case, it's important to leave the space between the two values in the column, so the on value should only be a comma. Below, the width value is two single quotes:

Address3

','

''

true

You can now split the Address3 column on the space delimiter:

Address3

by delimiter

' '

2

Results:

After you rename the columns, you should see the following: