Update CSV Data

Update CSV Data

CSV データ更新

Data stored in String-type is treated as an RDB table, and the data is manipulated by queries (SQL). The results of the query are saved in the specified Data Item. Two data can also be combined.

Input Data

Capture

Notes

• The following data will be processed
  • Data Stored in a String-type Data Item
    • The entered data can be processed as either a String-type (multiple lines) or a String-type (single line)
    • The data is considered an RDB table and treated as an H2 Database table
    • A query (SQL) is issued against the table, and the results are saved in the specified Data Item
    • This step can also be used to convert string data to numeric values ​​(the current [Update Data] step does not support conversion from text to numeric values)
  • Data Stored in a Table-type Data Item
    • CSV values, except for summary rows, are imported directly into the DB table
    • Column names are table item names (not field names)
    • All columns are saved as varchar data
    • Data format when imported into the DB table
      • String table items: Saved as is
      • Numeric table items: Saved as text without decimal points or thousands separators
      • Date table items: Saved as text in yyyy-MM-dd format
      • Select table items: Save the “selected value” to the table
• You can choose whether to treat the first line of the input TSV/CSV format data as a table column name (Header included) or as data (No header)
  • If you are targeting a String-type (single line), specify “No header (data starts from row 1)”
  • Column names must be between 1 and 64 characters long
  • If any column name is empty or exceeds 64 characters, the process will fail
  • If there are duplicate column names, the process will fail
  • All columns are treated as varchar and cannot be treated as integer, date, etc.
  • If the number of columns in the column name row does not match the number of columns in the data row, they will be automatically supplemented
    • If the number of columns in the data row is greater than the number of columns in the column row, a column with the name, for example, “col10” will be added to the 10th position (starting with 1)
    • If a data row has fewer columns than column rows, the missing columns in the data row will be treated as having null data
• You can automatically add an ID column to input TSV/CSV data
  • An ID indicating the row number of the data is automatically assigned
  • Processing will fail if the CSV/TSV data contains an “ID” column
• Specifying [C1: Data item 1 that contains data] expands [C1a: Data format]/[C1b: Table name when accessed by query]/[C1c: Add ID column to table (1, 2, 3 … row numbers are automatically assigned)] (similar for C2)
• If you specify a second Data Item in [C2: Data item 2 that contains data], you can also issue a query (SQL) that joins two data (tables)

Query

Capture

Notes

• Please write it in SQL compatible with an H2 Database
• The query (SQL) specified in [C3: Query issued for data] has a maximum limit of 2000 characters
  • Table names and column names specified in a query (SQL) must be enclosed in double quotes (“)
  • There are some prohibited words that should not be included in your query
    • Function names related to the file system, etc.
    • Table names and column names are also excluded
• If you select the “No header” option in the input settings, specify the columns in the query (SQL) using “col1” and “col2”
• Please refer to the query examples at the bottom of the page

Save Settings

Capture

Notes

• There are three options for [C4: How to save query results]
  • If you select [Save the entire table to a single data item in CSV / TSV format], the settings items for [C4a: Data item that will save query results]/[C4b: Data format when saving]/[C4c: Wrap column names and data in double quotes] will be expanded
    • If you specify the same Data Item as C1 in [C4a: Data item that will save query results], the data will be overwritten
    • CSV or TSV format can be selected for [C4b: Data format when saving], and you can also select whether to include headers
      • Data for which “No header” was selected in the input settings can be converted to a table (CSV/TSV) with headers
      • This is also effective for converting between multi-line strings
    • In [C4c: Wrap column names and data in double quotes], you can specify whether to wrap column names and data in double quotes when saving CSV/TSV data
  • If you select [Save each column value to a separate data item], [C4a: Data item that will save query results] will be expanded
    • Each column value can be saved to a separate String-type (multi-line) data item
    • This can be applied to updating Options Masters
  • If you select [Save each cell value to a separate data item], [C4a: Data item that will save query results] will be expanded
    • You can set the destination data item for each cell value individually
    • You can change the sort order of destination data items
    • You can save numbers from each row of a String-type (multi-line) data item directly to each Numeric-type data item
    • You can save multiple aggregation results such as “Max”, “Average”, “Total” all at once. Please refer to “Query Examples” for how to specify queries to achieve this

Query Examples

Projection

select "Product ID", "Item Name", "Price"
from "Products"

select * except("Product ID")
from "q_products"

Selection

select *
from "Products"
where "Product ID" like 'p123-%'

select *
from "Products"
where cast("Price" as integer) > 1000

Aggregate Functions

select
  max(cast("Price" as integer)) as "Max Price",
  min(cast("Price" as integer)) as "Min Price",
  avg(cast("Price" as integer)) as "Average Price",
  sum(cast("Price" as integer)) as "Total Price"
from "Products";

Join

select p.*, s.* except(s."Product ID")
from "Products" as p join "Sales" as s on p."Product ID" = s."Product ID"

See Also

• CSV Data Processing: From Manual to Automated