Headerless Data Is OK! Fully Understand the Input/Output Options of [Update CSV Data]

The newly added and provided [Update CSV Data] function in Ver. 16.2 has had additional functionality added in the Ver. 17.0 and Ver. 17.1 updates, significantly enhancing the input flexibility and output control.
This not only makes it easy to process Table-type data, but also to convert and save between various data items such as String, Numeric, Multi-line strings, etc. Processes that could not be achieved with [Update Data], or that previously required writing complex EL expressions, can now be easily achieved by using [Update CSV Data].

1. Available Options

The options that can be specified in [Update CSV Data] are summarized below. They are divided into those related to input (reading data) and those related to output (saving to data items).

1-1. Input Options

Data Format

• CSV / TSV
  • Choose between CSV and TSV format
• Presence of a Header
  • Choose one of the following:
    • No Header (data starts from row 1)
      • It can target String-type (single line) data and String-type (multiple lines) data that is not in CSV/TSV format.
    • Header included (row 1: column names, data starts from row 2)

Add an ID column to the table (Row numbers such as 1, 2, 3 … are automatically assigned)

• Toggle switch
  • On / Off

This makes it easier to handle headerless CSVs and simple string lists.

1-2. Output Options

How to save query results

• Save the entire table to a single data item in CSV / TSV format
  • Data item that will save query results 
    • (Select String-type multi-line)
  • Data format when saving
    • CSV / TSV
      • Choose between CSV and TSV format
    • Presence of a Header
      • Choose one of the following:
        • No Header (data starts from row 1)
          • When saving to a String-type (single line) or when a header line is not required
        • Header included (row 1: column names, data starts from row 2)
          • If the input data has no header, a header row such as col1 col2… will be added.
  • Wrap column names and data in double quotes
    • Choose one of the following:
      • Always
      • Only when necessary
• Save the values of each column to a data item, separated by line breaks
  • Data item that will save query results
    • Select data item to be set value (String-type (multiple lines) for each column (Column 1, Column 2, etc.))
      • No header line is output
• Save each cell value to a separate data item
  • Data item that will save query results
    • Select data item to be set value (String-type (single line/multiple lines) or Numeric-type for each cell (Cell 1, Cell 2, etc.)
      • Cell values ​​are saved to the specified data items in order from left to right, excluding the header row

This allows for flexible control of output results, expanding the range of applications for external system integration and master update processing.

2. Applied Use Options

By combining options for data input/output and destination data items, you can use [Update CSV Data] for a wide range of data processing. In addition to simple CSV/TSV data processing, you can also perform various processing and conversions on data stored in data items, such as converting text data to numeric data or CSV to TSV.

Here are some ideas for applying the CSV Data Update feature.

2-1. Converting Text to Numeric Data

Overview

CSV and String-type data item values that consist of character strings can be directly saved and converted to Numeric-type data items.

Settings Example

Output Options

• How to save query results: Save each cell value to a separate data item

SQL Example

SELECT * 
FROM "q_stringSingle";

Implementation Examples

• Convert numeric data obtained as a string from an external API into a numeric type
• “100” -> Used as the number 100 for calculation and aggregation

2-2. Saving Multiple Aggregation Results at Once

Overview

Multiple aggregate values ​​(maximum, average, total, etc.) calculated by a single query can be saved to multiple data items at the same time.

Settings Example

Output Options

• How to save query results: Save each cell value in a separate data item

SQL Example

select
  max(cast("Price" as integer)) as "Maximum Price",
  avg(cast("Price" as integer)) as "Average Price",
  sum(cast("Price" as integer)) as "Total Price"
from "Merchandise";

Implementation Examples

• Automatically calculate aggregate values from price lists and sales CSV files
• Automatically display statistics for business reports and approval processes

2-3. Converting Between Multi-Line Strings

Overview

This feature enables reading and writing multi-line strings without requiring headers. This allows you to perform conversions and filtering directly within String-type (multiple lines) data items.

Settings Example

Input Options

• Data Format > Header Row：No Header (data starts from the row 1)

Output Options

• How to Save Query Results: Save the entire table to a single data item in CSV / TSV format
• Data Format when Saving > Header Row: No Header (data starts from the row 1)

SQL Example

SELECT * 
FROM "q_list" 
ORDER BY "col1";

Implementation Examples

• Process a text list and transfer it to another data item
• Format and re-output the contents of a multi-line string using queries, without needing to convert it to CSV format.

2-4. Saving Each Column as a Separate Data Item

Overview

This feature allows you to easily save each column from your query results as individual data items.

Settings Example

Output Options

• How to Save Query Results: Save the values of each column to a data item, separated by line breaks

SQL Example

SELECT "Full Name","Email Address"
FROM "Roster" 

Implementation Examples

• Create an option master (using CSV columns as option labels and IDs)
• Extract individual items from a multi-item CSV

2-5. Converting CSV to TSV

Overview

With flexible input and output format options, you can easily convert between CSV and TSV formats.

Settings Example

Input Options

• Data Format > CSV / TSV：”CSV”

Output Options

• Data Format when Saving > CSV / TSV：”TSV”

Implementation Examples

• Convert CSV files to TSV format for integration with other systems
  

2-6. Controlling Quotes and Headers

Overview

Gain greater flexibility in output formatting with the ability to control the presence of quotes and headers, as well as the column order.

Settings Example

Output Options

• How to Save Query Results: Save the entire table as CSV/TSV format in a single data item
• Data Format when Saving > Header Row: Header included (row 1: column names, data starts from row 2)
• Wrap column names and data in double quotes: Only when necessary

Implementation Example

• Standardize data formats for seamless integration with other systems
• Convert data with or without quotes as needed

2-7. Automatically Adding an ID Column to Input Data

Overview

This feature automatically adds an ID column to your input CSV/TSV data. This simplifies row identification and control, making it easier to work with your data.

Settings Example

Input Options

• Add ID Column to Table (Row numbers are automatically assigned as 1, 2, 3 …): “ON”

SQL Example

SELECT "ID", "col1", "col2"
FROM "data"
ORDER BY "ID";

Implementation Examples

• Improve update processes and track errors using row numbers
• Use the ID column as a key when rejoining (JOIN) query results

3. Summary

By combining these optional features, the following operations can be achieved within the [CSV Data Update] function:

By leveraging these options, you can significantly expand the capabilities of Questetra for data conversion, aggregation, and master management automation within your workflows.