TSV String, Convert date data to yyyy-MM format
Converts the date data in the specified column to yyyy-MM or yyyy format and appends the value to the end of all lines.
Configs
  • C1:Set tsv string. *#{EL}
  • C2:Set column ID of the date field of TSV string.(e.g. “3”) *#{EL}
  • C3:Select date format of conveted date data. *
  • C4:Select STRING type item for appended TSV string.
Script (click to open)

main();

function main(){

    const tsv = configs.get('conf_tsv');
    const dateFieldId = Number(configs.get('conf_dateFieldId'));
    const dateFormat = configs.get('conf_dateFormat');
    const conf_appendedTsv = configs.get('conf_appendedTsv');

    const rowArray = tsv.trim().split('\n');
    const cells = [];
    let appendedTsv = '';
    for(let i = 0 ; i < rowArray.length ; i++){
        cells[i] = rowArray[i].split('\t');
        cells[i].push(convertDateFormat(cells[i][dateFieldId], dateFormat));
        appendedTsv += cells[i].join('\t');
        appendedTsv += '\n';
    }

    if(conf_appendedTsv !== ''){
        engine.setDataByNumber(conf_appendedTsv, appendedTsv.slice(0,-1));
    }
    
}

/*
文字の日付を指定されたフォーマットに変換して文字で返す。
@param dateStr {string} (e.g. "yyyy" "yyyy-MM")
@param dateFormat {string} (e.g. "2022-11-01")
@return Date.parse() で解釈できない文字の場合、'' が返されます。
*/
function convertDateFormat(dateStr, dateFormat){
    let date;
    try{
        date = new Date(dateStr);
        return dateFormatter.format(dateFormat, date);
    }catch(e){
        engine.log(String(e));
        engine.log('error: ' + dateStr + ' ('+ dateFormat +')');
        return '';
    }
}

/*
- 指定するフィールドの列IDはゼロからスタートします。
- 変換前の文字列は Date.parse() で解釈される場合に指定したフォーマットの日付文字列に変換されます。
    - YYYY-MM-DDTHH:mm:ss.sssZ (e.g. "2022-10-10T12:34:56.789" "2022-10-10" etc.)
- 指定されたフィールドの値が適切に変換されなかった場合、空文字('')が追加されます。
- The column ID of the date field you specify starts at zero.
- The string before conversion is converted to a date string of the specified format when interpreted by Date.parse().
- If the value of the specified field is not properly converted, an empty character ('') is added.
*/

Download

The Addon-import feature is available with Professional edition.
Freely modifiable JavaScript (ECMAScript) code. No warranty of any kind.

Notes

  • The column ID of the date field you specify starts at zero.
  • The string before conversion is converted to a date string of the specified format when interpreted by Date.parse().
  • If the value of the specified field is not properly converted, an empty character (”) is added.

Capture

See also

%d bloggers like this: