エクセルで作成した表をマクロでいじりたい！って言われて、とりあえず話聞いてできそうだったらという感じでお仕事を受けました。
蓋を開けてみたらMySQLのJOINのような作業だったので、コマンドライン上だけの操作でファイルを簡単に比較する方法は無いものかと試していた所、マクロなんか使わなくてもできたので手順等をメモ。
お仕事内容
商品の注文された数と出荷した数の確認を取るために必要だということで知り合いから依頼されました。
あんまり詳しく内容は話せませんが、やりたいこととしては、
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
■注文一覧 No,商品名,数量 1,A,10 2,B,20 3,C,30 4,G,40 ■出荷一覧 No,商品名,数量 1,A,10 2,B,10 3,C,10 4,E,40 5,F,10 6,G,5 7,G2,35 ↓ ■合体 商品名,数量(注文),数量(出荷) A,10,10 B,20,10 C,30,10 D,40,NULL E,NULL,40 F,NULL,10 G,NULL,5 G2,NULL,35 |
というようにしてほしいそう。
そのまま一意の値で比較して差分出せよ(笑)って思うかもしれませんが、Gのように実は他の商品を使っていたので名前は違いますパターンや、後ろにいろんな値がついていたりするらしいのでそのまま比較するのは怪しいということでこのような感じに合体したいとのこと。
上の例で言えばGは普通に比較したら足りないけど、G2ってやつで実は代用していて、それと合わせると数があっているから大丈夫って具合。
手順
それでは早速やった作業内容と解説。
先程の合体した状態にするためには、２つのエクセルファイルに大してMySQLのJOINのような操作をすれば行けそうだなぁと思いました。
そこで調べて見るとjoinコマンドでそれができるということが判明。
しかしcsvファイルでないと行けないらしい、、、
もしかしたら今後も同じようなことを頼まれるかもしれないので、先の自動化を見据えて、コマンドライン上でexcelファイルをcsvファイルに変換することに。
変換するためのコマンドはいくつかあるみたいですが、見た感じ一番簡単そうなxlsx2csvを使ってみることにします。
|
1 2 3 4 5 6 7 |
// インストール # pip install xlsx2csv // 使い方確認&動作確認 # xlsx2csv.py -h |
Windowsだと色々パスを通さないと行けなかったりするらしいけれど、Macならインストールした時点でそのまま使えるようなので問題なし。
|
1 2 3 |
# xlsx2csv <インプットファイル> <アウトプットファイル> |
ファイルをCSVにすることができたら、本来の目的出会ったJOINの操作を行っていきます。
joinコマンド
使い方：https://qiita.com/isseium/items/20eb6802898d9b1ba2b4
ちなみに注意点ですが、joinをする際にはもととなるファイルを予めソートしておく必要があります。
(※私はこれに気が付かず30分〜1時間ほどやられました)
|
1 2 3 4 5 6 |
// -t は区切り文字指定 -k は並べ替え対象の列(カラム)指定 # sort -t , -k2 <並べ替え対象ファイル> > <アウトプットファイル> # join -t, -a 1 -a 2 -1 2 -2 2 -e "NULL" -o 0,1.3,2.3 <インプットファイル1> <インプットファイル2> <アウトプットファイル> |
そして開いて見るとあら不思議。MySQLを使っていないのに同じような操作が簡単にできてしました。
まとめ
excel方眼紙撲滅委員会というものがあるくらいexcelは使いづらいですね、、、
全部の操作がコマンドライン上で完結した方がいいと私は言いませんが、大体の作業はプレーンなテキストファイルから変換して言った方が実は作業スピードも早く、PCの負荷も軽いので、excelでよく集計とかするぜって人は少しコマンドの勉強をしてみるといいかもしれません。(と今回のやつで思いました)
もっととってもかっこいい方法があったらぜひ教えて下さい！！
それでは今回はこれまで、、、
コメント