カテゴリー
【シェルスクリプトをオフィス業務の主役に！】シェルスクリプトでエクセル作業を効率化する超活用術まとめ
※ 当ページには【広告/PR】を含む場合があります。
2021/04/20
2024/01/06
目次
- 1. はじめに
- 2. 基本テクニック① 〜 文字列の操作
- 2-1. AwkとJqの正規表現の使い方
- 2-2. AwkとJqで文字列の置換を比較
- 2-3. AwkとJqでCSV形式のデータを出力
- 2-4. AwkとJqで正規表現のマッチングと後方参照
- 2-5. シェルスクリプトのパラメーター拡張で簡単な文字列操作
- 2-6. AwkとJqで文字列分割に必須なsplit関数の使い方
- 2-7. Sedでテキスト全体を一括削除する方法
- 2-8. Sedで文字列の中の空白文字を削除する
- 2-9. SedコマンドでCSVデータから列の区別なくキーを含んでる行を全抽出
- 2-10. Sedコマンドで複数行をまたぐ文字列パターンをマッチさせるための改行文字
- 2-11. SedとAwkでCSVデータの行と列を操作する基本
- 2-12. 巨大なCSVテキストをシェルコマンドで効率的に扱う
- 2-13. AwkとJqでCSVデータの空データをエラー値で埋める
- 2-14. AwkでCSVデータの全てのセル要素を解析して重複を調べる
- 2-15. 複数のCSVファイルを重複を考慮しながら合成する
- 3. 基本テクニック② 〜 表データから検索
- 4. 基本テクニック③ 〜 CSVでデータ集計
- 5. 基本テクニック④ 〜 シェルスクリプトとExcelとの連携
- 6. Jqコマンドを利用したCSV操作
- 7. Awkコマンドを使った便利なテクニック関連
- 8. その他〜覚えておくとお得なテクニック
- 9. 記事リンク・ダイジェスト
- 10. まとめ
はじめに
基本テクニック① 〜 文字列の操作
AwkとJqの正規表現の使い方
・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で文字列の置換を基礎を知りたい
・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でCSV形式のデータを出力
・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の正規表現でマッチした文字列をキャプチャしたい
・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で文字列分割に必須なsplit関数の使い方
・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でテキスト全体を一括削除する方法
・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で空白文字を細かく制御したい
$ 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ずれ 落ち る"
SedコマンドでCSVデータから列の区別なくキーを含んでる行を全抽出
・ CSVデータで不特定の列にキーワードが分布しているパターンを操作したい
$ sed -n -r '
/貝|イカ/p
' <<EOF
山田 モフ雄,ボタンエビ,サーモン,マグロ,アジ,甘エビ
川北 ザマ麿,エンガワ,ハマチ,イワシ,ウニ,マグロ
吉岡 マダ郎,アジ,赤貝,大トロ,アナゴ,サバ
西下 ガサ美,カンパチ,サバ,サーモン,エビ,とびっこ
真中 ポニ太,タコ,とびっこ,マグロ,イワシ,つぶ貝
神崎 ルギ子,サーモン,アナゴ,ウニ,中トロ,たまご
安藤 クゥ弘,甘エビ,赤貝,サーモン,アナゴ,タイ
風間 フッ松,ハマチ,アジ,カンパチ,中トロ,あん肝
時山 バフ丸,アナゴ,サバ,ヤリイカ,白子,タイ
EOF
#👇出力
吉岡 マダ郎,アジ,赤貝,大トロ,アナゴ,サバ
真中 ポニ太,タコ,とびっこ,マグロ,イワシ,つぶ貝
安藤 クゥ弘,甘エビ,赤貝,サーモン,アナゴ,タイ
時山 バフ丸,アナゴ,サバ,ヤリイカ,白子,タイ
Sedコマンドで複数行をまたぐ文字列パターンをマッチさせるための改行文字
・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>
SedとAwkでCSVデータの行と列を操作する基本
・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テキストをシェルコマンドで効率的に扱う
・巨大な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でCSVデータの空データをエラー値で埋める
・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
AwkでCSVデータの全てのセル要素を解析して重複を調べる
・CSVデータの行・列区別なくセル要素の重複を調べたい
$ echo "$RAW_DATASET" | awk -F"," '{
for (i=1; i<=NF; i++) {
if (!label[$i]++) {print $i;}
}
}'
#👇出力
2
3
1
6
4
8
複数のCSVファイルを重複を考慮しながら合成する
・バラバラになって複数存在してしまった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データの検索ツールの作り方の基礎
・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}"
検索用のインプットファイルを使った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
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
基本テクニック③ 〜 CSVでデータ集計
対話的に集計項目を選択できるスクリプトの作成の基本
・対話的に操作できるツールスクリプトが作成したい
#!/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
基本テクニック④ 〜 シェルスクリプトとExcelとの連携
xlsx2csvを使ってExcelファイルの直接操作
・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
xlsx2csvでExcelファイルからCSVデータと取り出したときのセル内改行
・CSVデータのセル内改行の扱い方を知りたい
$ xlsx2csv -e hoge.xlsx | awk -F"," '{ print $1, $2, $3; }'
#👇出力
のどかな\n田園風景 あ\n小林さん！ メガネは\nずれ落ちる
w e l
12 5 7
Linuxコマンドのみでcsvデータからxlsxファイルへ変換する方法
・Linux環境だけでもxlsxファイルを作りたい
・Excelなしでxlsxファイルにデータを作成・管理したい
$ ssconvert example.csv example.xlsx
$ xlsx2csv example.xlsx
名前,性別,年齢,住所
ピヨ田 モブ雄,男,35,地球
ベモ山 デン実,女,43,火星
Jqコマンドを利用したCSV操作
JqとXargsを使ったシェルスクリプトのパイプ処理化をコツ
・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コマンドでJSONデータを扱う際に利用するコマンドオプション
・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": "広島支店"
}
]
]
JSONデータとCSVデータを相互に変換
・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": "さいあくだ"
}
]
Awkコマンドを使った便利なテクニック関連
Jqがインストールできない人のためのAwkでJsonファイルを扱うテクニック
・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
}
'
AwkのカスタムソートでCSVデータの複雑な複雑な並び替え
・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]);
}
}
'
その他〜覚えておくとお得なテクニック
Excel特有の時間形式・『エクセル時間』をDateコマンドで自在に変換
・シェルコマンドでエクセル時間から通常の時間表記にしたい
・シェルコマンドで通常の時間表記からエクセル時間にしたい
$ 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
DateコマンドでExcelファイルからCSVデータを抽出する際の時間形式を変換
・エクセル時間をシェルスクリプト内でちゃんとした時間で表示したい
$ 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でもLinuxのシェルコマンドを使えるようにするbusyboxの紹介
・Windowsで簡単にシェルコマンドを実行させたい
busyboxのDateコマンドを使うときの注意点
・Gnu dateコマンドの基礎を知りたい
・Awkスクリプトの中でもdateコマンドを使いたい
$ date -d "2018-03-13" "+%Y年%m月%d日"
2018年03月13日
記事リンク・ダイジェスト
まとめ
記事を書いた人
ナンデモ系エンジニア
主にAngularでフロントエンド開発することが多いです。 開発環境はLinuxメインで進めているので、シェルコマンドも多用しております。 コツコツとプログラミングするのが好きな人間です。
カテゴリー
- 1. はじめに
- 2. 基本テクニック① 〜 文字列の操作
- 2-1. AwkとJqの正規表現の使い方
- 2-2. AwkとJqで文字列の置換を比較
- 2-3. AwkとJqでCSV形式のデータを出力
- 2-4. AwkとJqで正規表現のマッチングと後方参照
- 2-5. シェルスクリプトのパラメーター拡張で簡単な文字列操作
- 2-6. AwkとJqで文字列分割に必須なsplit関数の使い方
- 2-7. Sedでテキスト全体を一括削除する方法
- 2-8. Sedで文字列の中の空白文字を削除する
- 2-9. SedコマンドでCSVデータから列の区別なくキーを含んでる行を全抽出
- 2-10. Sedコマンドで複数行をまたぐ文字列パターンをマッチさせるための改行文字
- 2-11. SedとAwkでCSVデータの行と列を操作する基本
- 2-12. 巨大なCSVテキストをシェルコマンドで効率的に扱う
- 2-13. AwkとJqでCSVデータの空データをエラー値で埋める
- 2-14. AwkでCSVデータの全てのセル要素を解析して重複を調べる
- 2-15. 複数のCSVファイルを重複を考慮しながら合成する
- 3. 基本テクニック② 〜 表データから検索
- 4. 基本テクニック③ 〜 CSVでデータ集計
- 5. 基本テクニック④ 〜 シェルスクリプトとExcelとの連携
- 6. Jqコマンドを利用したCSV操作
- 7. Awkコマンドを使った便利なテクニック関連
- 8. その他〜覚えておくとお得なテクニック
- 9. 記事リンク・ダイジェスト
- 10. まとめ