Generate Datatable from Text

This activity creates a DataTable in a structured format from the provided raw text. It helps organize the data and allows users to customize it as needed.

Properties

Fixed formatting option

Column Sizes: Specifies the width of the column if required. E.g., “12,13,14”. This option will be enabled only when the “Fixed width column” is used.

Formatting Options

Column Separator: Specifies how columns in the input data are separated. Options available in the dropdown:

Space – Separates columns with a space.
Tab – Separates columns with a tab.
New Line – Places each column on a new line.
Comma (,) – Separates columns with a comma.
Colon (:) – Separates columns with a colon.
Semicolon (;) – Separates columns with a semicolon.
Equals (=) – Separates columns with an equals sign.

Csv Parsing: Enable this option when the input data are separated by comma values. Set True to enable the option and False to skip it. You can also configure them from the Generate Table Wizard by checking the corresponding boxes.

New Line Separator: Specifies how rows in the input data are separated. Options available:

Space – Separates rows with a space.
Tab – Separates rows with a tab.
New Line – Places each row on a new line.

Input

Input: Specifies the input data in the sample input area. Accepts values in String format.

You can provide text values either as a hardcoded string or as a variable.

Note: File paths are not allowed as input.

Misc

Display Name: The display name of the activity. This can be customized to make workflows easier to understand and troubleshoot.

SkipOnError: Accepts a Boolean value (True or False).

True: Continues executing the workflow even if an error occurs.

False: Stops the workflow when an error occurs.

None: If left blank, the default behavior is the same as False.

Output

DataTable: Returns the output as a DataTable, converting the provided input into a structured table.

Table Options

(For the properties below, set True to enable the option and False to skip it. You can also configure them from the Generate Table Wizard by checking the corresponding boxes.)

Auto Detect Types: Automatically detects the data types of column values.

Ignore First Column: Ignores the values in the first column of the input data.

Include Column Headers: Uses the first row of the input as column headers.

Limitations

The column separator and new line separator accept values in String format in the property window, such as:

1. “comma” or “,”
2. ““[newline]”

When using symbols, only single characters (; , =) are allowed. Therefore, values like \n or \t are not supported.

Example

Let’s look at an example of how to generate a DataTable from text. Assume we have a text file containing unstructured data about the number of tickets. Using the “Generate datatable from text” activity, we are going to create a structured table.

Follow the steps below:

1. Drag and drop the Read Text activity into the workflow.

a. In the File path property, provide the path of the input text file.
b. In the Output section, declare a variable to store the extracted text.

2. Add the Generate DataTable from Text activity into the workflow.

a. In the Input property, assign the variable created in the Read Text activity.
b. Set the CSV Parsing property to True, and in the Column separator property, specify Comma.
c. In the Output section, declare a variable to store the resulting DataTable.

3. Finally, add a Table Viewer activity to the workflow and provide the DataTable variable created above as input.

The bot extracts the content from the text file and passes it to the Generate DataTable activity to create a DataTable from scratch. Click here to view the sample file.

Using Generate Table Wizard for formatting

The Generate Table Wizard feature allows users to easily create and preview a DataTable from a given sample input. It provides options to define the table’s structure, format, and alignment, ensuring the generated DataTable is accurate and ready for use in the workflow.

To access the wizard, double-click on the activity and select the “Options” button, now you can enter your input data and customize the formatting.

Key Steps:

1. Enter your sample data or paste existing content into the input area.
2. Use the available formatting tools to adjust column alignment, headers, and delimiters for structured output.
3. Click the Preview button to visualize how your DataTable will appear before generating it.
4. Once completed with the formatting, save the changes to generate a clean and properly structured DataTable.

Additional Notes:

1. Refer to the Properties panel for detailed descriptions of the options available in the Wizard.
2. If the format is set to CSV, only the Column Separator option will be available.
3. If the format is set to Custom, both Column Separator and New Line Separator options will be available.
4. If the format is set to Fixed Width Columns, the Column Widths option will be available.

Troubleshooting steps

Using Custom Data in Generate DataTable from Text

1. If your input text does not follow a standard format like CSV, you can define your own custom separators.
2. Use the Column Separator property to specify how values in each row are divided (e.g., ;, or tab).
3. Use the New Line Separator property to specify how each row of data is separated (e.g., \n for line breaks).
4. You can set these values directly in the Properties panel or through the Generate DataTable Wizard.

Example:
If your text looks like this:

ID Name Department 

1 John Finance 

2 Emma HR 

a. Set Column Separator = space
b. Set New Line Separator = \n

This will generate a structured DataTable with the correct columns and rows.

Handling Improperly Formatted Text (via Variable)

When you pass text from a variable into the Generate DataTable from Text activity, the output depends on how the text is formatted. If the format is not proper (e.g., inconsistent separators, extra spaces, or line breaks), the DataTable may not generate correctly.

How to Correct It:

1. Ensure the text in the variable uses a consistent structure (e.g., all values separated by commas, pipes, or tabs).
2. Use activities to Replace, Trim to remove unwanted spaces, extra lines, or incorrect characters.
3. If your text doesn’t use commas, configure the Column Separator and New Line Separator properties with the correct delimiters.

Note: You don’t need to customize every time manually. Once you define separators or clean the text, the activity will generate the table consistently.

1. “Column” already belongs to the datatable – occurs when,

a. Your input text or raw CSV data contains the same column name more than once.
b. The Generate DataTable from Text activity automatically tries to create DataTable columns, but DataTables don’t allow duplicate names.

How to correct it:

a. Always make sure the input data has unique column names.
b. If the source data contains duplicate columns, rename or manually check them before converting into a DataTable.