Converter: TSV-String to Table
Converter: TSV-String to Table
Converts a TSV String to Table-type data. The string values of all cells in the TSV string are copied over to Table-type data B. If there is an inconsistency in the Table column design, an error may occur.
Configs
  • A: Set TSV String *#{EL}
  • B: Select TABLE DATA (update)
  • B2: Select TABLE DATA (append)
Script (click to open)

// GraalJS Script (engine type: 2)
// 
// Notes:
// Copies text information of all cell values.
// Copies in the display order of the column. (Does not depend on column ID)
// If the number of columns decreases, the right columns will not be copied.
// If the number of columns increases, blank strings will be completed.
// For Numeric-column, the period "." is recognized as a decimal point.
// For Numeric-column, the comma "," is recognized as a thousand separator.
// For Select-column, the value must be choice ID. (Otherwise, not copied)
// Auto-calculated columns are reevaluated. (Not always the same value)
// To add data without erasing already stored cell data, use B2.
//
// Notes (ja):
// 全てのセル値の文字情報をコピーします。
// カラムの表示順にコピーします。(カラムIDに依らない)
// カラム列数が少なくなる場合は、右列部がカットされます。
// カラム列数が増える場合は、空白文字列で補完されます。
// 数値カラムへの値コピーは、ピリオド "." を小数点として認識して代入します。
// 数値カラムへの値コピーは、桁区切り文字 "," が存在しても構いません。
// 選択カラムへの値コピーは、選択肢IDでなければなりません。(コピーされません)
// 自動計算カラムは、再評価されます。(必ずしも同じ値になりません)
// 既に格納されているセルデータを消さずに追記したい場合は、B2をご利用ください。


//////// START "main()" ////////////////////////////////////////////////////////////////
main();
function main(){

//// == Config Retrieving / 工程コンフィグの参照 ==
const strTsv   = configs.get( "conf_StrTsv" )   + ""; // required
const dataIdB  = configs.get( "conf_DataIdB" )  + ""; // not required
const dataIdB2 = configs.get( "conf_DataIdB2" ) + ""; // not required
// 'java.lang.String' to javascript primitive 'string'

if( strTsv === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " TSV String {A} is empty \n" );
}

let outputTable1 = null;
let outputTable1ProcessDataDefinitionView = null;
let outputTable1DataDefinition = null;
let numColsB = 0;
if( dataIdB !== "" ){
  outputTable1ProcessDataDefinitionView = engine.findDataDefinitionByNumber( dataIdB ); // com.questetra.bpms.core.event.scripttask.ProcessDataDefinitionView
  outputTable1DataDefinition = outputTable1ProcessDataDefinitionView.getSubDataDefinitions(); // java.util.List<com.questetra.bpms.core.event.scripttask.SubDataDefinitionView>
  outputTable1 = outputTable1ProcessDataDefinitionView.createListArray(); // com.questetra.bpms.core.event.scripttask.ScriptListArray
  numColsB  = outputTable1DataDefinition.size() - 0;
  engine.log( " AutomatedTask TableConfig:" +
              " #of {B}-Columns Definition: " + numColsB );
}

let outputTable2 = null;
let outputTable2ProcessDataDefinitionView = null;
let outputTable2DataDefinition = null;
let numColsB2 = 0;
if( dataIdB2 !== "" ){
  outputTable2ProcessDataDefinitionView = engine.findDataDefinitionByNumber( dataIdB2 ); // com.questetra.bpms.core.event.scripttask.ProcessDataDefinitionView
  outputTable2DataDefinition = outputTable2ProcessDataDefinitionView.getSubDataDefinitions(); // java.util.List<com.questetra.bpms.core.event.scripttask.SubDataDefinitionView>
  outputTable2 = engine.findDataByNumber( dataIdB2 ); // com.questetra.bpms.core.event.scripttask.ScriptListArray
  if( outputTable2 === null ){
    outputTable2 = outputTable1ProcessDataDefinitionView.createListArray();
  }
  numColsB2  = outputTable2DataDefinition.size() - 0;
  engine.log( " AutomatedTask TableConfig:" +
              " #of {B2}-Columns Definition: " + numColsB2 );
}


//// == Data Retrieving / ワークフローデータの参照 ==
// (nothing)


//// == Calculating / 演算 ==
let twoDimArrayA = []; // for TSV-A
const arrTsv = strTsv.split("\n");
engine.log( " AutomatedTask MultilineString:" + 
            " TSV {A}, number of lines " + arrTsv.length );
for( let i = 0; i < arrTsv.length; i++ ){
  twoDimArrayA[i] = arrTsv[i].split("\t");
}

if( dataIdB !== ""){
  for( let i = 0; i < arrTsv.length; i++ ){
    let tmpRow = outputTable1.addRow(); // com.questetra.bpms.core.event.scripttask.ScriptListArray.ScriptListRow
    if( twoDimArrayA[i].length < numColsB ){
      for( let j = 0; j < twoDimArrayA[i].length; j++ ){
        const tmpCol = outputTable1DataDefinition.get(j); // com.questetra.bpms.core.event.scripttask.SubDataDefinitionView
        if( tmpCol.matchDataType("DECIMAL") ){
          twoDimArrayA[i][j] = twoDimArrayA[i][j].replace( /,/g, "" );
        }
        tmpRow.setCol( j, twoDimArrayA[i][j] );
      }
      for( let k = twoDimArrayA[i].length; k < numColsB; k++ ){
        tmpRow.setCol( k, "" ); // Fill with empty characters
      }
    }
    if( twoDimArrayA[i].length >= numColsB ){
      for( let j = 0; j < numColsB; j++ ){
        const tmpCol = outputTable1DataDefinition.get(j); // com.questetra.bpms.core.event.scripttask.SubDataDefinitionView
        if( tmpCol.matchDataType("DECIMAL") ){
          twoDimArrayA[i][j] = twoDimArrayA[i][j].replace( /,/g, "" );
        }
        tmpRow.setCol( j, twoDimArrayA[i][j] );
      }
    }
    engine.log( " AutomatedTask:" +
                " Add B[" + i + "]" );
  }
}

let numOriginLines = 0;
if( dataIdB2 !== ""){
  if( outputTable2 !== null ){
    numOriginLines = outputTable2.size() - 0;
  }
  engine.log( " AutomatedTask:" +
              " #of {B2}-Rows: " + numOriginLines );

  for( let i = 0; i < arrTsv.length; i++ ){
    let tmpRow = outputTable2.addRow(); // com.questetra.bpms.core.event.scripttask.ScriptListArray.ScriptListRow
    if( twoDimArrayA[i].length < numColsB2 ){
      for( let j = 0; j < twoDimArrayA[i].length; j++ ){
        const tmpCol = outputTable2DataDefinition.get(j); // com.questetra.bpms.core.event.scripttask.SubDataDefinitionView
        if( tmpCol.matchDataType("DECIMAL") ){
          twoDimArrayA[i][j] = twoDimArrayA[i][j].replace( /,/g, "" );
        }
        tmpRow.setCol( j, twoDimArrayA[i][j] );
      }
      for( let k = twoDimArrayA[i].length; k < numColsB2; k++ ){
        tmpRow.setCol( k, "" ); // Fill with empty characters
      }
    }
    if( twoDimArrayA[i].length >= numColsB2 ){
      for( let j = 0; j < numColsB2; j++ ){
        const tmpCol = outputTable2DataDefinition.get(j); // com.questetra.bpms.core.event.scripttask.SubDataDefinitionView
        if( tmpCol.matchDataType("DECIMAL") ){
          twoDimArrayA[i][j] = twoDimArrayA[i][j].replace( /,/g, "" );
        }
        tmpRow.setCol( j, twoDimArrayA[i][j] );
      }
    }
    engine.log( " AutomatedTask:" +
                " Add B2[" + (numOriginLines + i) + "]" );
  }
}


//// == Data Updating / ワークフローデータへの代入 ==
if( dataIdB !== "" ){
  engine.setDataByNumber( dataIdB, outputTable1 );
}
if( dataIdB2 !== "" ){
  engine.setDataByNumber( dataIdB2, outputTable2 );
}

} //////// END "main()" ////////////////////////////////////////////////////////////////

Download

2021-08-05 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/converter-tsv-string-to-table-2021/
The Add-on import feature is available with Professional edition.

Notes

  • Copies text information of all cell values.
  • Copies the columns in the order in which they are displayed (does not depend on the column ID/field name).
  • If the number of columns decreases, the right columns will not be copied.
  • If the number of columns increases, they will be completed with blank strings.
  • For Numeric columns periods are recognized as a decimal point.
  • For Numeric columns commas are recognized as a thousand separator.
  • For Select columns the copied value must be the Choice ID (otherwise nothing is copied).
  • Auto-calculated columns are re-evaluated (not always to the same value).
  • To add data without erasing existing cell data, use B2.
  • Please prepare the value of Excel-CSV in the following format according to the data type of the table type sub-item (column)
    • String-type: (entered character string)
    • Numeric-type: Standard format (period as decimal point, no thousands separator)
    • Select-type: Choice ID
    • Date-type: yyyy-mm-dd
  • If the format does not match, or if the specified value does not satisfy the input check contents, a processing error will result

Capture

Converts TSV String to Table-type data. The string value of all cells in the TSV string is copied over Table-type data B. If there is an inconsistency in the Table column design, an error may occur.

See also

1 thought on “Converter: TSV-String to Table”

  1. Pingback: Converter: TSV-String to Table – Questetra Support

Comments are closed.

%d