Supporting Various Data Input/Output with [Update CSV Data]

In the previous article, Automate Data Processing with [Update CSV Data], we introduced how to process standard CSV data using the automated step [Update CSV Data].

However, the true value of [Update CSV Data] is not limited to processing comma-separated values. By using different option settings, you can import a variety of formats beyond just CSV data, such as TSV (tab-separated values), data without headers, and even single-line text values.

Furthermore, during the output phase, you gain precise control over the data formatting. You can toggle quotes, adjust header settings, and even split the output into individual data items. This flexibility allows for seamless data transformation that perfectly aligns with the requirements of subsequent processes.

[Update CSV Data] Relates Articles:

• Automate Data Processing with [Update CSV Data]
• Supporting Various Data Input/Output with [Update CSV Data]
• Automating Data Aggregation with [Update CSV Data]
• Configuring Option Master with [Update CSV Data] (Coming soon)

1. Available Options

1-1. Input Options

The input settings allow you to target data formats other than CSV

• Data Format:
  • Choose between CSV (comma-separated) and TSV (tab-separated)

• Header Row:
  • If you select “No Header (data starts from row 1)”, the first line will be treated as data. Column names in queries will be designated as "col1", "col2", and so on

• Add ID Column:
  • You can automatically add an ID column (1, 2, 3…) to indicate the row number of each record in the input data

1-2. Output Options

The [Save Settings] offer options for meticulous control over the output format.

• Save Format:
  • Select between CSV and TSV. You can even output the data in a different format than the input

• Header Output:
  • Choose whether or not to include a header row in the output data

• Quote Enclosure:
  • Specify whether to enclose each value in the output data with quotes

• Split Save:
  • Options are also available to save each column’s value with line breaks or to save each cell’s value as separate data items
    • A separate article will provide more details on using these options
      • Automating Data Aggregation with [Update CSV Data]
      • Configuring Option Master with [Update CSV Data]

2. Data Formats and Corresponding Option Settings

2-1. Importing List Data Without Header Rows

This supports simple bullet list data (newline-delimited strings), such as lists of names.

Configuration Example

Input Option

• In [C1a: Data Format > Header Row], select “No Header (data starts from row 1)”

Output Option

• In [C4: How to save query results], select “Save the entire table to a single data item in CSV / TSV format”
• In [C4b: Data format when saving], select “No Header (data starts from row 1)”

Query Example

Sort the name list saved in the field named “q_list” alphabetically. When using data without headers, specify columns as “col1”, etc.

SELECT *
FROM "q_list"
ORDER BY "col1"

Use Case

• Process text lists and save them as individual data items.
• Format and re-output String-type (multiple lines) content using queries.

2-2. Importing Single-Line Text Data

This handles data saved in [Single-line Text] data items without header rows, treating it as a single-line CSV data.

Configuration Example

Input Option

• In [C2: Data Item 2 that contains data], specify a single-line text data item.
• In [C2a: Data Format], select “No Header (data starts from row 1)”.

Query Example

Search and extract the “Product ID” column from the “Sales Table” data accessed via [C1b: Table Name when accessed by query]. The search value is retrieved from the field name “q_productId” specified in [C2: Data Item 2 that contains data].

SELECT *
FROM "Sales Table"
WHERE "Product ID" LIKE (SELECT * FROM "q_productId")

Use Case

• Dynamically modify the extraction target by changing the single-line data within each process.

2-3. Importing TSV Format Data

Specify TSV as the input data format to handle tab-separated data.

Configuration Example

Input Option

• Specify “TSV” in [C1a: Data Format > CSV/TSV].

Output Option

• Specify “CSV” in [C4b: Data format when saving > CSV/TSV].

Query Example

All data in the C1a table will be imported and output. The “Product” table name in the query has been defined in [C1b: Table name when accessed by query] for the C1a table.

SELECT *
FROM "Product" 

Use Case

• Convert TSV format data to CSV when CSV is required for external integrations.

2-4. Specifying Quote Wrapping for Output

You can control whether or not values are enclosed in quotes in the output format. For input, the presence of quotes is automatically detected and processed.

Configuration Example

Output Option

• Specify “CSV” in [C4b: Data format when saving > CSV/TSV].
• In [C4c: Wrap column names and data in double quotes], select “Always”.

Query Example

Import and output all data from the “Product” table, using the input/output options to ensure all values are enclosed in double quotes.

SELECT *
FROM "Product"

Use Case

• Ensure compatibility with destination systems that require all values to be enclosed in quotes, or conversely, to be removed.
• Convert data between quoted and unquoted formats as necessary.

3. Summary

The [Update CSV Data] feature’s input options allow it to handle various data formats beyond CSV. For example, single-line text without commas saved in a [String-type (single line)] data item can be treated as single row, single column CSV data.

By combining options such as header presence, delimiter (comma/tab), and quote processing, [Update CSV Data] becomes a versatile hub for processing and format conversion of diverse text data, not just simple tables. Despite its name, take advantage of [Update CSV Data] for a wide range of applications beyond simple CSV processing.