【シェルスクリプトをオフィス業務の主役に！】シェルスクリプトでエクセル作業を効率化する超活用術まとめ

            
            ・Awkの正規表現の使い方を知りたい
・Jqの正規表現の使い方を知りたい
        

            
            $ awk '$1 ~ /[0-9]$/ {print FNR ": " $1}' << EOF
1234
abc
dfgh
567
ij
EOF
#👇出力
1: 1234
4: 567

$jq -sR '[
    split("\\r?\\n"; "g")[]|select(length > 0)|split(","; "g")
]' << EOF
2021/04/06,出張,山田
2021/04/08,会議,佐藤
2021/04/17,リモートワーク,鈴木
EOF
#👇出力
[
  [
    "2021/04/06",
    "出張",
    "山田"
  ],
  [
    "2021/04/08",
    "会議",
    "佐藤"
  ],
  [
    "2021/04/17",
    "リモートワーク",
    "鈴木"
  ]
        

            
            ・Awkで文字列の置換を基礎を知りたい
・Jqで文字列の置換を基礎を知りたい
        

            
            $ awk -F"," 'BEGIN {OFS=","}
{
    gsub("/", "-", $1);
    print $1, $2, $3;
}' << EOF
2021/04/06,出張,山田
2021/04/08,会議,佐藤
2021/04/17,リモートワーク,鈴木
EOF
#👇出力
2021-04-06,出張,山田
2021-04-08,会議,佐藤
2021-04-17,リモートワーク,鈴木

$ jq -sR '
    [split("\n")[]|select(length > 0)|split(",")]
    | map(.[0] |= gsub("/"; "-"))
    | map({"日付": .[0], "予定": .[1], "担当": .[2]})
' << EOF
2021/04/06,出張,山田
2021/04/08,会議,佐藤
2021/04/17,リモートワーク,鈴木
EOF
#👇出力
[
  {
    "日付": "2021-04-06",
    "予定": "出張",
    "担当": "山田"
  },
  {
    "日付": "2021-04-08",
    "予定": "会議",
    "担当": "佐藤"
  },
  {
    "日付": "2021-04-17",
    "予定": "リモートワーク",
    "担当": "鈴木"
  }
]
        

            
            ・Awkで出力フォーマットを操作したい
・Jqで出力フォーマットを操作したい
        

            
            $ awk -F"," '{
    num = match($3, /(山田|.?郎)/);
    print RSTART " " RLENGTH;
    print $0 " -> " $3 "が" num "バイト目に見つかりました";
}' << EOF
2021/04/06,出張,山田 モフ男
2021/04/08,会議,佐藤 ピヨ子
2021/04/17,リモートワーク,鈴木 フガ郎
EOF
#👇出力
1 6
2021/04/06,出張,山田 モフ男 -> 山田 モフ男が1バイト目に見つかりました
0 -1
2021/04/08,会議,佐藤 ピヨ子 -> 佐藤 ピヨ子が0バイト目に見つかりました
11 6
2021/04/17,リモートワーク,鈴木 フガ郎 -> 鈴木 フガ郎が11バイト目に見つかりました

$ jq -sR '
    [ split("\n")[] | select(length > 0) | split(",") ]
    | to_entries
    | map({date: .value[0], task: .value[1], name: .value[2], index: .key})
    | map(select(.name | test("(山田|.?郎)")))
' << EOF
2021/04/06,出張,山田 モフ男
2021/04/08,会議,佐藤 ピヨ子
2021/04/17,リモートワーク,鈴木 フガ郎
EOF
#👇出力
[
  {
    "date": "2021/04/06",
    "task": "出張",
    "name": "山田 モフ男",
    "index": 0
  },
  {
    "date": "2021/04/17",
    "task": "リモートワーク",
    "name": "鈴木 フガ郎",
    "index": 2
  }
]
        

            
            ・Awkの正規表現でマッチした文字列をキャプチャしたい
・Jqの正規表現でマッチした文字列をキャプチャしたい
        

            
            $ awk -F"," 'BEGIN {OFS=","} {
    last4digits_ = gensub(/[0-9]{4}-[0-9]{4}-[0-9]{4}-([0-9]{4})/, "\\1", g, $2);
    print $1, last4digits_;
}' << EOF
グルヤマ マムオ,1111-2222-3333-4444,000
ヌルタニ ポウスケ,5555-5555-6666-6666,222
ポロクチ ニャルミ,3333-4444-5555-9999,777
EOF
#👇出力
グルヤマ マムオ,4444
ヌルタニ ポウスケ,6666
ポロクチ ニャルミ,9999

$ jq -sR '[ split("\n")[] | select(length > 0) | split(",") ] |
    map({
        "名義": .[0],
        "下4桁": .[1] | capture("[0-9]{4}-[0-9]{4}-[0-9]{4}-(?<digits>[0-9]{4})") | .digits
    })
' << EOF
グルヤマ マムオ,1111-2222-3333-4444,000
ヌルタニ ポウスケ,5555-5555-6666-6666,222
ポロクチ ニャルミ,3333-4444-5555-9999,777
EOF
#👇出力
[
  {
    "名義": "グルヤマ マムオ",
    "下4桁": "4444"
  },
  {
    "名義": "ヌルタニ ポウスケ",
    "下4桁": "6666"
  },
  {
    "名義": "ポロクチ ニャルミ",
    "下4桁": "9999"
  }
]
        

            
            ・bashのパラメーター拡張を知りたい
・できるだけ簡潔なシェルスクリプトを書きたい
        

            
            $ HOGE_FILE="/path/to/hoge.piyo.fuga.mofu"
$ echo "${HOGE_FILE/./-}"
/path/to/hoge-piyo.fuga.mofu
$ echo "${HOGE_FILE//./-}"
/path/to/hoge-piyo-fuga-mofu
        

            
            ・Awkで文字列をさらに細かく分割したい
・Jqで文字列をさらに細かく分割したい
        

            
            $ awk -F"&" 'BEGIN{ OFS="," } {
    print $1,$2,$3,$4;
}' testdata.asv
#👇Csvで出力
山下モゲ雄,営業部,本社,3年
島田フガ子,経理部,名古屋支部,15年
岡田ピポ太,製造部,山口工場,8年
沢口モフ代,人事部,本社,4年
銭形ガメ吉,海外部,メキシコ支部,11年

$ jq -s -R '
    [ split("\n")[] | select(length > 0) | split(" ") ]
' testdata.ssv
#👇配列として表示
[
  [
    "山下モゲ雄",
    "営業部",
    "本社",
    "3年"
  ],
  [
    "島田フガ子",
    "経理部",
    "名古屋支部",
    "15年"
  ],
  [
    "岡田ピポ太",
    "製造部",
    "山口工場",
    "8年"
  ],
  [
    "沢口モフ代",
    "人事部",
    "本社",
    "4年"
  ],
  [
    "銭形ガメ吉",
    "海外部",
    "メキシコ支部",
    "11年"
  ]
]
        

            
            ・Sedの正規表現の使い方を思い出したい
・テキストを高速に特定の文字を一括削除をしたい
        

            
            $ echo 'a b c d e f g' | sed -r 's/[[:space:]]//g'
abcdefg

$ echo 'HEX: 01 2e 24 a5 8c 2f de' | sed -r 's/[[:xdigit:]]//g'
HX:
        

            
            ・Sedで空白文字が上手く削除されない理由が知りたい
・Sedで空白文字を細かく制御したい
        

            
            $ sed -r '
s/"[[:blank:]]+/"/g
s/\\n[[:blank:]]+/\\n/g
s/[[:blank:]]+"/"/g
s/[[:blank:]]+\\n/\\n/g
s/[[:blank:]]+/ /g
' <<EOF
"      のどか  な\n       田園      風景    ","       あ       \n小林      さん！","  メガネ  は\n   ずれ   落ち        る"
EOF
"のどか な\n田園 風景","あ\n小林 さん！","メガネ は\nずれ 落ち る"
        

            
            ・ CSVデータで不特定の列にキーワードが分布しているパターンを操作したい
        

            
            $ sed -n -r '
/貝|イカ/p
' <<EOF
山田 モフ雄,ボタンエビ,サーモン,マグロ,アジ,甘エビ
川北 ザマ麿,エンガワ,ハマチ,イワシ,ウニ,マグロ
吉岡 マダ郎,アジ,赤貝,大トロ,アナゴ,サバ
西下 ガサ美,カンパチ,サバ,サーモン,エビ,とびっこ
真中 ポニ太,タコ,とびっこ,マグロ,イワシ,つぶ貝
神崎 ルギ子,サーモン,アナゴ,ウニ,中トロ,たまご
安藤 クゥ弘,甘エビ,赤貝,サーモン,アナゴ,タイ
風間 フッ松,ハマチ,アジ,カンパチ,中トロ,あん肝
時山 バフ丸,アナゴ,サバ,ヤリイカ,白子,タイ
EOF
#👇出力
吉岡 マダ郎,アジ,赤貝,大トロ,アナゴ,サバ
真中 ポニ太,タコ,とびっこ,マグロ,イワシ,つぶ貝
安藤 クゥ弘,甘エビ,赤貝,サーモン,アナゴ,タイ
時山 バフ丸,アナゴ,サバ,ヤリイカ,白子,タイ
        

            
            ・Sedで改行文字を先読みさせて正規表現を使いたい
        

            
            $ str_show_2=${str_show//$'\n'/\\n}
$ echo $str_show_2 | sed -e 's/<head>.*<\/head>//g'
<!doctype html>\n<html lang="en">\n \n <body>\n <div>\n <header>\n <span>蛸壺のプロブラミング・ブログ</span>\n <a href="/home"><span>HOME</span></a>\n </header>\n <footer>\n <ul>\n <li><a href="/home">Home</a> ｜</li>\n <li><a href="/home#blog">Blog</a> ｜</li>\n <li><a href="/home#about">About</a> ｜</li>\n <li><a href="/home#contactus">Contact Us</a></li>\n </ul>\n </footer>\n </div>\n </body>\n</html>
        

            
            ・CSVデータ内の行と列をシェルコマンドでどう操作するか知りたい
        

            
            $ sed -i '$ a 出川 ゴボ之,営業,大阪支部,1年' employee.csv
$ cat employee.csv
山下 モゲ雄,営業部,本社,3年
島田 フガ子,経理部,名古屋支部,15年
岡田 ピポ太,製造部,山口工場,8年
沢口 モフ代,人事部,本社,4年
銭形 ガメ吉,海外部,メキシコ支部,11年
出川 ゴボ之,営業,大阪支部,1年

$ awk -F "," '
BEGIN {
    OFS=","
}
F == 0 {
    open_arr[$1] = $2;
    next;
}
{
    if($1 in open_arr) {
        print $1, open_arr[$1], $2, $3, $4;
    }
}
' F=0 add_employee.csv F=1 employee.csv > combined.csv
$ cat combined.csv
#👇出力
山下 モゲ雄,25歳,営業部,本社,3年
島田 フガ子,42歳,経理部,名古屋支部,15年
岡田 ピポ太,30歳,製造部,山口工場,8年
沢口 モフ代,27歳,人事部,本社,4年
銭形 ガメ吉,34歳,海外部,メキシコ支部,11年
        

            
            ・巨大なCSVファイルから部分的に切り取ったデータを取り出したい
・Head/Tail/Cut/Trコマンドでより高速にテキストを捌く方法を知りたい
・Head/Tail/Cut/TrコマンドのCSVデータの使い方を知りたい
        

            
            #!/bin/bash

FILE_NAME=hoge.csv
TOTAL_LINES=$(wc -l hoge.csv | cut -d' ' -f1)
BUFFER_SIZE=3
START_READ=1
END_READ=$(($TOTAL_LINES / $BUFFER_SIZE))
LEFT_LINES=$(($TOTAL_LINES % $BUFFER_SIZE))
COLUMN_LIST=1,3
echo "TOTAL:${TOTAL_LINES} START:${START_READ} END:${END_READ} LEFT:${LEFT_LINES}"

do_something() {
    #DO SOMETHING...
    echo "$1"
}

for i in $(seq "$END_READ" -1 0); do
    while read LINE; do
        do_something $LINE
    done< <(
        if [ "$i" != 0 ] ; then
            head -n "$(( ($END_READ - $i + 1) * $BUFFER_SIZE ))" $FILE_NAME |
            tail -n "$(( $BUFFER_SIZE ))" |
            cut -f "$COLUMN_LIST" -d ","
        else
            tail -n "$(( $LEFT_LINES ))" $FILE_NAME |
            cut -f "$COLUMN_LIST" -d ","
        fi
    )
done
        

            
            ・Awkでセル内空白を空文字かヌルなのかを区別させたい
・Jqでセル内空白を空文字かヌルなのかを区別させたい
・Excel関数のようにエラー値を#N/Aで再現したい
        

            
            $ awk -F"," '{
    if ( typeof($1) != "strnum" || typeof($2) != "strnum" ) {
        print "#N/A";
    } else {
        print $1*$2;
    }
}' << EOF
3,６
4,19
8,3
５,22
4.5,9.13
526,0.0
９.1,0.0
EOF
#👇出力
#N/A
76
24
#N/A
41.085
0
#N/A
        

            
            ・CSVデータの行・列区別なくセル要素の重複を調べたい
        

            
            $ echo "$RAW_DATASET" | awk -F"," '{
    for (i=1; i<=NF; i++) {
        if (!label[$i]++) {print $i;}
    }
}'
#👇出力
2
3
1
6
4
8
        

            
            ・バラバラになって複数存在してしまったCSVファイルを一つに統合したい
        

            
            $ awk -F "," 'BEGIN {OFS=","}
F == 0 {
    open_arr[$1] = $2;
    next;
}
F == 1 {
    if($1 in open_arr) {
        close_arr[$1] = $2;
    }
    next;
}
{
    if($1 in open_arr) {
        print $1, open_arr[$1], close_arr[$1], $2;
    }
}
' F=0 open.csv F=1 close.csv F=2 volume.csv
#👇実行結果
2020-05-19,1575,1567,9500
2020-05-20,1570,1598,9600
2020-05-21,1599,1551,8300
2020-05-22,1551,1541,3100
2020-05-25,1553,1578,3800
2020-05-26,1583,1569,9700
2020-05-27,1569,1569,14300
2020-05-28,1537,1570,18400
2020-05-29,1571,1558,12200
2020-06-01,1558,1554,4600
2020-06-02,1564,1588,11200
2020-06-03,1599,1569,12300
2020-06-04,1590,1565,8000
2020-06-05,1571,1566,8300
        

            
            ・CSVデータからデータを探すスクリプトの概要を知りたい
        

            
            #!/bin/bash

usage_exit() {
    echo "USAGE: $(basename $0) [-l key_list] [-h help] [input_file]" 1>&2
    exit 1
}

noarg_err() {
    echo "ERROR: must provide key_list!" 1>&2
    exit 1
}

noinputfile_err() {
    echo "ERROR: not allowed input file to be empty!" 1>&2
    exit 1
}

while getopts l: OPT; do
    case $OPT in
        l ) KEY_LIST="$OPTARG"
            ;;
        \? ) usage_exit
            ;;
    esac
done

shift $((OPTIND - 1))

if [ -z "$KEY_LIST" ]; then
    noarg_err
fi

if [ -z "$1" ]; then
    noinputfile_err
fi

echo "FILE: $1, KEY_LIST: ${KEY_LIST}"
        

            
            ・データ検索を直接入力でなく、入力ファイルで操作したい
        

            
            #!/bin/bash

usage_exit() {
    echo "USAGE: $(basename $0) [-l key_list] [-h help] [input_file]" 1>&2
    exit 1
}

noarg_err() {
    echo "ERROR: must provide key_list!" 1>&2
    exit 1
}

selecteither_err() {
    echo "ERROR: should select an option from -l/-i!" 1>&2
    exit 1
}

noinputfile_err() {
    echo "ERROR: not allowed input file to be empty!" 1>&2
    exit 1
}

while getopts l:i: OPT; do
    case $OPT in
        l ) KEY_LIST="$OPTARG"
            ;;
        i ) KEY_LIST_FILE="$OPTARG"
            ;;
        \? ) usage_exit
            ;;
    esac
done

shift $((OPTIND - 1))

if [ -z "$KEY_LIST" ] && [ -z "$KEY_LIST_FILE" ]; then
    noarg_err
elif [ -n "$KEY_LIST" ] && [ -n "$KEY_LIST_FILE" ]; then
    selecteither_err
elif [ -z "$1" ]; then
    noinputfile_err
fi

echo "FILE: $1, KEY_LIST: ${KEY_LIST}, KEY_LIST(FILE): ${KEY_LIST_FILE}"

if [ -n "$KEY_LIST" ]; then
    PARR=($(echo "${KEY_LIST}" | tr ',' ' '))
    for p in "${PARR[@]}"; do
        awk -F"," '
            $1 ~ /'"$p"'/ { last_macthed = $0; }
            END { print last_macthed ? last_macthed : "'"$p"'?,#N/A,#N/A,#N/A"; }
        ' $1
    done
elif [ -n "$KEY_LIST_FILE" ] && [ -f "$KEY_LIST_FILE" ]; then
    cat "$KEY_LIST_FILE" | while read KEY || [ -n "${KEY}" ]; do
        awk -F"," '
            $1 ~ /'"$KEY"'/ { last_macthed = $0; }
            END { print last_macthed ? last_macthed : "'"$KEY"'?,#N/A,#N/A,#N/A"; }
        ' $1
    done
fi
        

            
            ・ CSVデータを複雑な条件で検索するツールを作りたい
        

            
            #!/bin/bash

usage_exit() {
    echo "USAGE: $(basename $0) [-l key_list] [-h help] [input_file]" 1>&2
    exit 1
}

noarg_err() {
    echo "ERROR: must provide key_list!" 1>&2
    exit 1
}

selecteither_err() {
    echo "ERROR: should select an option from -l/-i!" 1>&2
    exit 1
}

noinputfile_err() {
    echo "ERROR: not allowed input file to be empty!" 1>&2
    exit 1
}

while getopts l:i: OPT; do
    case $OPT in
        l ) KEY_LIST="$OPTARG"
            ;;
        i ) KEY_LIST_FILE="$OPTARG"
            ;;
        \? ) usage_exit
            ;;
    esac
done

shift $((OPTIND - 1))

if [ -z "$KEY_LIST" ] && [ -z "$KEY_LIST_FILE" ]; then
    noarg_err
elif [ -n "$KEY_LIST" ] && [ -n "$KEY_LIST_FILE" ]; then
    selecteither_err
elif [ -z "$1" ]; then
    noinputfile_err
fi

echo "FILE: $1, KEY_LIST: ${KEY_LIST}, KEY_LIST(FILE): ${KEY_LIST_FILE}"

if [ -n "$KEY_LIST" ]; then
    PARR=($(echo "${KEY_LIST}" | tr ',' ' '))
    for p in "${PARR[@]}"; do
        awk -F"," '
            $1 ~ /'"$p"'/ { last_macthed = $0; }
            END { print last_macthed ? last_macthed : "'"$p"'?,#N/A,#N/A,#N/A"; }
        ' $1
    done
elif [ -n "$KEY_LIST_FILE" ] && [ -f "$KEY_LIST_FILE" ]; then
    cat "$KEY_LIST_FILE" | while read KEY || [ -n "${KEY}" ]; do
        K=($(echo "${KEY}" | awk -F",{1}" '{k1=$1?$1:"NULL";k2=$2?$2:"NULL";k3=$3?$3:"NULL";k4=$4?$4:"NULL";print k1,k2,k3,k4;}'))
        echo "+++ MATCHING-KEYS 1: ${K[0]} 2: ${K[1]} 3: ${K[2]} 4: ${K[3]} +++ "
        awk -F"," '
            function isSkip(s_){return s_ == "NULL";}
            {
                ch1 = !isSkip("'"${K[0]}"'");
                ch2 = !isSkip("'"${K[1]}"'");
                ch3 = !isSkip("'"${K[2]}"'");
                ch4 = !isSkip("'"${K[3]}"'");
                total = ch1 + ch2 + ch3 + ch4;
                if (ch1 && $1 !~ /'"${K[0]}"'/) {ch1=0}
                if (ch2 && $2 !~ /'"${K[1]}"'/) {ch2=0}
                if (ch3 && $3 !~ /'"${K[2]}"'/) {ch3=0}
                if (ch4 && $4 !~ /'"${K[3]}"'/) {ch4=0}
                if (ch1 + ch2 + ch3 + ch4 == total) {print $0}
            }
        ' $1
    done
fi
        

            
            ・対話的に操作できるツールスクリプトが作成したい
        

            
            #!/bin/bash

function DoAScript() {
    echo "スクリプトA: $1が選択されました。"
}

function DoBScript() {
    echo "スクリプトB: $1が選択されました。"
}

function Addup() {
    printf "集計したい項目を 1 か 2 で選択してください\n"
    printf "\t1) 月間総来客数 \t2) 月間総売上\n"
    printf "※ 終了は q か c を入力！\n>>>"
    read OPT

    if [ $OPT == '1' ] ; then
        DoAScript "$OPT"
    elif [ $OPT == '2' ] ; then
        DoBScript "$OPT"
    elif [ $OPT == 'q' ] || [ $OPT == 'c' ] ; then
        echo "終了"
        exit 1
    else
        printf "ヒント...1 か 2 で入力!\n\n"
        Addup
    fi
}

Addup
        

            
            ・セル文字列の要素に曖昧さを含んだ CSVデータでも集計を行いたい
        

            
            #!/bin/bash

declare -A branches=(
    ['1']='営業部'
    ['2']='経理部'
    ['3']='製造部'
    ['4']='海外部'
)

function Total() {
    local option="${branches[$1]}"
    local filepath="$2"
    cat "$filepath" | awk -F"," '
        $2 ~ /'$option'/ { addup += $4 }
        END {print "\n  '$option' : " addup " 円"}
    '
}

function Addup() {
    local option
    printf "集計したい部署を以下 1/2/3/4 から選択してください\n"
    printf "\n  1) 営業部  2) 経理部  3) 製造部 4) 海外部\n\n"
    printf "※ 終了は q か c を入力！\n>>>"
    read OPT
    case "$OPT" in
        1 | 2 | 3 | 4 ) Total $OPT $1; option=$OPT;;
        q | c ) echo "終了"; exit 0;;
        * ) printf "ヒント... 1/2/3/4 で入力!\n\n"; Addup $1;;
    esac
    [ -n "$option" ] && exit 1
}

Addup $1
        

            
            ・CSVデータで複数の列で集計条件を同時に満たす要素だけで集計がしたい
        

            
            #!/bin/bash

declare -A branches=(
    ['1']='営業部'
    ['2']='経理部'
    ['3']='製造部'
    ['4']='海外部'
)

declare -A locations=(
    ['1']='本社'
    ['2']='名古屋'
    ['3']='山口'
    ['4']='ベトナム'
    ['5']='メキシコ'
)

function Total() {
    local b="${branches[$1]}"
    local l="${locations[$2]}"
    local filepath="$3"
    cat "$filepath" | awk -F"," '
        $2 ~ /'$b'/ && $3 ~ /'$l'/ { addup += $4 }
        END {print "\n  '$b' '$l' : " addup " 円"}
    '
}

function Addup() {
    local opt1 opt2

    printf "集計したい部署を以下 1/2/3/4 から選択してください\n"
    printf "\n  1) 営業部  2) 経理部  3) 製造部  4) 海外部\n\n"
    printf "※ 終了は q か c を入力！\n>>>"
    read OPT1
    case "$OPT1" in
        1 | 2 | 3 | 4 ) opt1=$OPT1;;
        q | c ) echo "終了"; exit 0;;
        * ) printf "ヒント... 1/2/3/4 で入力!\n\n"; Addup $1;;
    esac

    printf "集計したい勤務地を以下 1/2/3/4/5 から選択してください\n"
    printf "\n  1) 本社  2) 名古屋  3)  山口  4) ベトナム  5) メキシコ\n\n"
    printf "※ 終了は q か c を入力！\n>>>"
    read OPT2
    case "$OPT2" in
        1 | 2 | 3 | 4 | 5 ) Total $opt1 $OPT2 $1; opt2=$OPT2;;
        q | c ) echo "終了"; exit 0;;
        * ) printf "ヒント... 1/2/3/4/5 で入力!\n\n"; Addup $1;;
    esac
    [ -n "$opt1" ] || [ -n "$opt2" ] && exit 1
}

Addup $1
        

            
            ・日付時間を集計条件に指定したい
・Dateコマンドの出力フォーマットを良く知りたい
        

            
            #!/bin/bash

function TotalCustomerOrSales() {
    local option=$(( $1 + 1 ))
    local mode="$2"
    local filepath="$3"
    cat "$filepath" | awk -v mode=$mode -F"," '
        function excel_month(rawtime) {
            parsed_time = (rawtime - 1) * 60 * 60 * 24 - 2209075200;
            cmd="date -d @"parsed_time " +\"%m\"";
            cmd | getline month_; close(cmd);
            return month_;
        }
        function excel_week(rawtime) {
            parsed_time = (rawtime - 1) * 60 * 60 * 24 - 2209075200;
            cmd="date -d @"parsed_time " +\"%W\"";
            cmd | getline week_; close(cmd);
            return week_;
        }
        function excel_day(rawtime) {
            parsed_time = (rawtime - 1) * 60 * 60 * 24 - 2209075200;
            cmd="date -d @"parsed_time " +\"%u\"";
            cmd | getline day_; close(cmd);
            return day_;
        }
        BEGIN {
            # Array of months
            for (i=1;i<=12;i++) {
                m[i-1] = i > 9 ? i "" : "0" i;
            }
            # Array of weeks
            for (i=0;i<=53;i++) {
                w[i] = i > 9 ? i "" : "0" i;
            }
            # Array of days of the week
            for (i=1;i<=7;i++) {
                d[i-1] = i "";
            }
        }
        {
            if (mode == 1) {
                for (i in m) {
                    if ( excel_month($1) == m[i] ) {
                        if (mSummary[i] == "" ) {
                            mSummary[i] = $'$option'
                        } else {
                            mSummary[i] += $'$option'
                        }
                    }
                }
            } else if (mode == 2) {
                for (i in w) {
                    if ( excel_week($1) == w[i] ) {
                        if (wSummary[i] == "" ) {
                            wSummary[i] = $'$option'
                        } else {
                            wSummary[i] += $'$option'
                        }
                    }
                }
            } else if (mode == 3) {
                for (i in d) {
                    if ( excel_day($1) == d[i] ) {
                        if (dSummary[i] == "" ) {
                            dSummary[i] = $'$option'
                        } else {
                            dSummary[i] += $'$option'
                        }
                    }
                }
            }
        }
        END {
            if (mode == 1) {
                for (i in mSummary) {
                    print m[i] "月:" mSummary[i]
                }
            } else if (mode == 2) {
                for (i=0 ; i < length(w) ; i++ ) {
                    if (wSummary[i] != "") { print w[i] "週:" wSummary[i] }
                }
            } else if (mode == 3) {
                for (i in dSummary) {
                    print d[i] "曜日:" dSummary[i]
                }
            }
        }
    '
}
        

            
            ・単純な計算だけでなく、数学的な操作をデータ解析したい
        

            
            #!/bin/bash

declare -A categories=(
    ['1']='営業部'
    ['2']='経理部'
    ['3']='製造部'
    ['4']='海外部'
    ['5']='本社'
    ['6']='名古屋'
    ['7']='山口'
    ['8']='ベトナム'
    ['9']='メキシコ'
)

function Average() {
    local option="${categories[$1]}"
    local filepath="$2"
    cat "$filepath" | awk -F"," '
        BEGIN { count = 0 }
        $2 ~ /'$option'/ || $3 ~ /'$option'/ {
            avrg[count++] = $4;
        }
        END {
            result = 0;
            for (i = 0; i < count; i++ ) { result += avrg[i] }
            result = count > 0 ? result / count : 0;
            print "\n  '$option' (" count "人)の平均値 : " result " 円";
        }
    '
}

function Addup() {
    local option
    printf "集計したい部署を以下 1/2/3/4 から選択してください\n"
    printf "\n  1) 営業部  2) 経理部  3) 製造部  4) 海外部"
    printf "\n  5) 本社    6) 名古屋  7) 山口    8) ベトナム  9) メキシコ\n\n"
    printf '※ 終了は q  か c を入力！\n>>>'
    read OPT
    case "$OPT" in
        [1-9]* ) Average $OPT $1; option=$OPT;;
        q | c ) echo "終了"; exit 0;;
        * ) printf "ヒント... 1から9 で入力!\n\n"; Addup $1;;
    esac
    [ -n "$option" ] && exit 1
}

Addup $1
        

            
            ・OS環境に依らずLinuxでもExcelファイルを直接読み込みたい
・OS環境に依らずLinuxでもExcelファイルからCSVデータを保存したい
        

            
            $ xlsx2csv -s 1 book1.xlsx | awk -F"," '
    { print $1, $2, $3; }
'
#👇出力
1 2 3
4 5 6

$ xlsx2csv -s 2 book1.xlsx | awk -F"," '
    { print $1, $2, $3; }
'
#👇出力
7 8 9
a b c
        

            
            ・CSVデータのセル内改行の扱い方を知りたい
        

            
            $ xlsx2csv -e hoge.xlsx | awk -F"," '{ print $1, $2, $3; }'
#👇出力
のどかな\n田園風景 あ\n小林さん！ メガネは\nずれ落ちる
w e l
12 5 7
        

            
            ・Linux環境だけでもxlsxファイルを作りたい
・Excelなしでxlsxファイルにデータを作成・管理したい
        

            
            $ ssconvert example.csv example.xlsx

$ xlsx2csv example.xlsx
名前,性別,年齢,住所
ピヨ田 モブ雄,男,35,地球
ベモ山 デン実,女,43,火星
        

            
            ・Jqを使ったスクリプトとても重い
        

            
            #!/bin/bash

employee=$(cat << EOF
[
    {"社員":"山下モゲ雄","部署":"営業部","事業所":"本社","勤続":"3年"},
    #中略...上記で説明したデータセット
    {"社員":"山下モゲ雄","部署":"営業部","事業所":"埼玉支部","勤続":"3年"},
]
EOF
)

employee=$(
echo "$employee" | jq -c '.[]' | sed -e 's/"/\\"/g' | xargs -n1 bash -c '
    echo "$0" | jq -c "(.[\"部署\"] |= \"営業部\") | (.[\"事業所\"] |= \"埼玉支店\")"
')

echo "["${employee//$'\n'/,}"]" | jq -c '.'
        

            
            ・Jqの入力データの取り込みが思うように上手くいかない
・Jqの出力データのフォーマットが思うような形になっていない
        

            
            $ jq --slurp '.' << EOF
[
    {"name": "バミ田 ポプ雄","age": 43,"branch": "本社"},
    {"name": "ギャマ川 ノリュ実","age": 23,"branch": "福岡支店"},
    {"name": "ドル間 ハム吉","age": 33,"branch": "広島支店"},
]
EOF
#👇出力
[
  [
    {
      "name": "バミ田 ポプ雄",
      "age": 43,
      "branch": "本社"
    },
    {
      "name": "ギャマ川 ノリュ実",
      "age": 23,
      "branch": "福岡支店"
    },
    {
      "name": "ドル間 ハム吉",
      "age": 33,
      "branch": "広島支店"
    }
  ]
]
        

            
            ・Jq＆AwkコマンドでCSVデータをJSONデータに変換したい
・Jq＆AwkコマンドでJSONデータをCSVデータに変換したい
        

            
            $ CSV_TEXT=$(cat << EOF
hoge,piyo,fuga,moga,buyo
120,こんにちは,3.14,ごきげんだぜ,false
54,コンバンワ,-8.07,,true
-89,てやんでぇ,1.2E-3,さいあくだ,
EOF
)

$ echo "$CSV_TEXT" | awk -F"," '
    NR == 1 {
        rowCount = 0;
        for (i = 1; i <= NF; i++) {key[i] = $i;}
    }
    NR > 1 {
        rowCount++;
        for (i = 1; i <= NF; i++) {
            if ($i != "") {
                if ($i ~ /^[+-]?([0-9]+[.]?[0-9]*|[.][0-9]+)([eE][+-]?[0-9]+)?$/) {
                    value[rowCount][i] = "{\"key\":\"" key[i] "\",\"value\":" $i "}";
                }
                else if (tolower($i) ~ /(true|false|null)/) {
                    value[rowCount][i] = "{\"key\":\"" key[i] "\",\"value\":" $i "}";
                }
                else {
                    value[rowCount][i] = "{\"key\":\"" key[i] "\",\"value\":\"" $i "\"}";
                }
            }
        }
    }
    END {
        for (j=1;j<=rowCount;j++) {
            rslt = "[";
            for (i = 1; i <= NF; i++) {
                if (value[j][i] == null) { continue; }
                else if (i == NF) { rslt = rslt value[j][i]; }
                else { rslt = rslt value[j][i] ","; }
            }
            sub(/,$/, "", rslt);
            print rslt "]";
        }
    }
' | jq 'from_entries' | jq -s '.'
#👇出力結果
[
  {
    "hoge": 120,
    "piyo": "こんにちは",
    "fuga": 3.14,
    "moga": "ごきげんだぜ",
    "buyo": false
  },
  {
    "hoge": 54,
    "piyo": "コンバンワ",
    "fuga": -8.07,
    "buyo": true
  },
  {
    "hoge": -89,
    "piyo": "てやんでぇ",
    "fuga": 0.0012,
    "moga": "さいあくだ"
  }
]
        

            
            ・Jqを使いたいのに、インストール必須なpythonが諸事情等で使えない
        

            
            $ cat input.dat | awk '
    BEGIN{
        s_pos = 0;
        conv_json = "[";
    }
    /[0-9]{4}-[0-9]{2}-[0-9]{2}/{
        s_pos = NR;
        if(NR == s_pos) {
            date = "\"date\":\"" $0 "\""
        }
    }
    /^[0-9\.]+$/{
        if(NR == s_pos+1) {
            open = "\"open\":" $0
        }
        if(NR == s_pos+2) {
            high = "\"high\":" $0
        }
        if(NR == s_pos+3) {
            low = "\"low\":" $0
        }
        if(NR == s_pos+4) {
            closed = "\"close\":" $0
        }
        if(NR == s_pos+5) {
            volume = "\"volume\":" $0
        }
    }
    NF == 0{
        if(NR == s_pos+6) {
            content = "{" date "," open "," high "," low "," closed "," volume "}";
            conv_json = conv_json content ","
        }
    }
    END{
        sub(/,$/, "", conv_json);
        conv_json = conv_json "]";
        print conv_json
    }
'
        

            
            ・CSVデータを独自のルールで並び替えたい
        

            
            $ EXAM_SCORE=$(cat << EOF
Ichiro 84
Bob 25
Wakame 76
Hanako 56
Alice 90
Kabao 53
Jam 43
Tarao 25
Cheese 12
Kasuo 47
Piyoko 88
Ikura 29
EOF
)

$ echo "$EXAM_SCORE" | awk '
function cmp_num_val(i1, v1, i2, v2) {
    if (v1 < v2) {
        return -1;
    } else {
        return 1;
    }
}
{
    data[$1] = $2;
}
END {
    PROCINFO["sorted_in"] = "cmp_num_val";
    for (j in data) {
        printf("data[%s] = %s\n", j, data[j]);
    }
}
'
        

            
            ・シェルコマンドでエクセル時間から通常の時間表記にしたい
・シェルコマンドで通常の時間表記からエクセル時間にしたい
        

            
            $ echo '2021年01月01日'|
    sed -r 's/([0-9]{4})[^0-9]+([0-9]{1,2})[^0-9]+([0-9]{1,2})[^0-9]+/\1-\2-\3/g'|
    awk -F"," '
    function parse_epochtime(rawtime) {
        cmd="date -u --date=\""rawtime" 00:00:00\" +\"%s\"";
        cmd | getline parsed_time
        close(cmd)
        parsed_time = (parsed_time + 2209075200)/60/60/24 + 1;
        return parsed_time;
    }
    {
        epochtime = parse_epochtime($1);
        print epochtime
    }
'
44197
        

            
            ・エクセル時間をシェルスクリプト内でちゃんとした時間で表示したい
        

            
            $ cat accounting.csv | awk -F"," '
    function parse_excel_date(rawtime) {
        parsed_time = (rawtime - 1) * 60 * 60 * 24 - 2209075200;
        excel_date = strftime("%Y年%m月%d日", parsed_time);
        return excel_date;
    }
    BEGIN{ OFS="," }
    {
        print parse_excel_date($1),$2,$3;
    }
'
#👇出力
2021年01月01日,51,98875
2021年01月02日,91,150457
2021年01月03日,77,134982
        

            
            ・自作スクリプトで単純な引数の引き渡しの方法が知りたい
・getoptsを使った自作スクリプトの作り方が知りたい
・引数解析付きの自作スクリプトの作り方が知りたい
        

            
            #!/bin/bash
PROGNAME="$(basename $0)"

usage() {
cat << EOS >&2
Usage: ${PROGNAME} [--hoge] [-1, --fuga [VALUE]] [-2, --piyo VALUE]
    A sample script of parsing on bash.
Options:
    --hoge        A single option.
    --fuga        A option with optional value.
    --piyo        A option with required value.
    -h, --help    Show usage.
EOS
    exit 1
}

PARAM=()

for opt in "$@"; do
    case "${opt}" in
        '-h' | '--help' )
            usage
            ;;
        '--hoge' )
            HOGE=true; shift
            ;;
        '-1' | '--fuga' )
            FUGA=true; shift
            if [[ -n "$1" ]] && [[ ! "$1" =~ ^-+ ]]; then
                FUGA_VALUE="$1"; shift
            fi
            ;;
        '-2' | '--piyo' )
            if [[ -z "$2" ]] || [[ "$2" =~ ^-+ ]]; then
                echo "${PROGNAME}: Option needs an argument -- $( echo $1 | sed 's/^-*//' )" 1>&2
                exit 1
            fi
            PIYO=true; PIYO_VALUE="$2"; shift 2
            ;;
        '--' | '-' )
            shift
            PARAM+=( "$@" )
            break
            ;;
        -* )
            echo "${PROGNAME}: Invalid option detected -- '$( echo $1 | sed 's/^-*//' )'" 1>&2
            exit 1
            ;;
        * )
            if [[ -n "$1" ]] && [[ ! "$1" =~ ^-+ ]]; then
                PARAM+=( "$1" ); shift
            fi
            ;;
    esac
done

INPUT_FILE="${PARAM}"; PARAM=("${PARAM[@]:1}")
[[ -z "${INPUT_FILE}" ]] && usage

if [[ -n "${PARAM[@]}" ]]; then
    usage
fi

cat << EOS
HOGE > ${HOGE:-false} : FUGA > ${FUGA:-false} : PIYO > ${PIYO:-false}
FUGA_VALUE > ${FUGA_VALUE} : PIYO_VALUE > ${PIYO_VALUE}
INPUT_FILE < ${INPUT_FILE}
EOS
        

            
            ・Windowsで簡単にシェルコマンドを実行させたい
        

            
            ・Gnu dateコマンドの基礎を知りたい
・Awkスクリプトの中でもdateコマンドを使いたい
        

            
            $ date -d "2018-03-13" "+%Y年%m月%d日"
2018年03月13日