Parsing text

The Text Parsing action allows you to map substrings in a text field that either match a pattern or are delimited by a common separator to new or existing fields in a target table. You can:

• Parse the text field based on a fixed delimiter or a Regular Expressions (REGEX)
• Use the Preview grid to view the contents of the input text field and the processed substrings for a subset of records
• Map each substring to new or existing fields using the substring mapping area

On the Configuration tab:

1. Define a filter to limit the affected records.

2. Drag from Table Fields to identify the field being parsed.

3. Define the parsing technique.

4. Optionally, use the Base Field Name to define the substring name prefix.

5. Generate fields based on the defined substrings.

6. Edit substring properties as needed.

7. Refresh to view parsed substrings as fields.

8. Preview data or SQL results.

Possible applications include:

• Separate a text field into multiple fields using a fixed delimiter (comma, period, etc.).
• Match multiple occurrences of a Regular Expressions (REGEX), and map each occurrence to new or existing fields
• Match a Regular Expressions (REGEX), while grouping different elements within the expression, and map each element to new or existing fields

Parse text in a field

1. Drag the Text Parsing icon onto the design surface.
2. On the Connections tab , select the database and table.
   • For Database Connection, select the database that contains the table with the field being parsed, or choose New Database Connection to establish a new connection.
   • Table Name – Enter the name of the table.
3. On the Configuration tab :
   • Specify the field to be parsed, the parsing mechanism, and the fields resulting from the parse. Note that you must define at least one resulting field:
   • Drag the field to be parsed from Table Fields to Input Field.
     • Indicate how the field will be parsed.
     • Use Separator - If the field includes a standard separator, select this choice and then choose the separator from the list. The field will be parsed wherever the separator is encountered.
     • Other - If the field includes a non-standard separator, select this choice and then enter the separator character(s) in this field. The field will be parsed wherever the separator is encountered.
     • Apply Regular Expression - Select this choice to parse the field based on a Regular Expressions (REGEX), and then enter the expression here based on the rules defined for REGEX expressions.

       Refer to Regular Expressions (REGEX) for more information on regular expressions. Data Guru supports Regular Expressions as used in Microsoft’s .NET Framework. More information on accepted syntax can be found at -

       https://msdn.microsoft.com/en-us/library/az24scfc%28v=vs.110%29.aspx

   • Define the fields resulting from the parse -
     • For Substring 1, drag a field from Table Fields to use an existing field, or drag the New Field choice to create a new field.
     • To quickly add fields with names sharing a common prefix, enter a Base Field Name.
     • Click the Add icon for additional fields. If you have entered a Base Field Name, a new Substring is automatically created with that prefix, and appended with an underscore and a number matching that of the substring (e.g., BaseField_1); otherwise, enter the name of the new substring in the icon provided.
     • Click the Edit icon next to a field to edit its name and other properties.

       

     • Click Generate to create a field for each defined substring, based on the substring text and parsing technique used.
     • Click Refresh for a preview of the fields containing the separated data, based on the input string and selected separator.
     • Use the Filter area to build an expression that more narrowly defines the data to be parsed. Expressions can be created directly in the Filter area, or you can use the Expressions.
     • Click Preview to view the data or the SQL statement created from this action. Refer to Preview for information.

Text Parsing data example

The following examples show results from the three basic types of parsing: