こんにちは。sanamekoです！
突然ですが、INSERT文を書くのってすごく大変ですよね！（個人の感想です。）
「カラム名1,カラム名2,･･･カラム名N」の後に「値1,値2,･･･値N」と書くのが苦手で、最終的にカラムがずれてたり、カラム数足りなかったりってこと、よくあります！
手でガリガリ書かずに、うまいことツールを使ってラクに書けないものか･･･ヽ(ˇд ˇ;)ﾉ
ということで、「ExcelでできるだけスマートにINSERT文を作成する方法」を考えたいと思います！
「スマート」とは
ExcelでINSERT文を作成するという話はよく聞くので、今更感は否めませんが、今回は「スマート」をテーマに、下記のことを念頭において作ってみます！
- 誰が見ても分かりやすい
- マクロやVBAは使わない
- あちこちに数式（関数）を散りばめたりせず、最小限のセルに抑える
- 数式の中身を見なくても、何をやっているか大体分かる状態にする
- メンテナンスや使い回しが容易である
- カラムの増減にも簡単に対応できる
- コピーして他のテーブルにも転用できる
他のひとが作ったExcelを解読したり修正するのって大変ですからね。できるだけシンプルに、スマートに作りたいです！
では早速いってみましょう！
叩き台
まず叩き台を作りました。 A1セルにテーブル名、A2～E2セルにカラム名、3行目以降はA列～E列に値を入力すれば、F列にINSERT文が完成します。
作成されたINSERT文：
INSERT INTO t_sanameko (column1,column2,column3,column4,column5) VALUES (1,500,500,500,1500);
ポイント
- 数式が入っているのはF列のみ
- 修正時はF列の数式だけ直せばよい
- レコード数が増えたら、F列の数式を下セルにコピーしていくだけでOK！
- テーブル名・カラム名は数式内に直接書かず、シート上に書かれた文言を参照するだけにする
- どのテーブル/どのカラムに値を入れているのか、数式の中身を見にいかなくても分かる
- 他のテーブルに転用しやすい
肝心の数式の中身ですが、まずは&を使って文字列連結してみました。
F3="INSERT INTO "&$A$1&" ("&$A$2&","&$B$2&","&$C$2&","&$D$2&","&$E$2&") VALUES ("&$A3&","&$B3&","&$C3&","&$D3&","&$E3&");"
うーん、いちいち&とか","をつけるの面倒ですね。カラム数が多ければ多いほど大変。
カラム増減に伴うメンテも面倒くさそう。ミスが起きそうですね･･･！
改良版その1（CONCATENATE関数/Excel）
Excelには、CONCATENATEという、文字列を連結する関数があるそうな。
2 つ以上の文字列を 1 つの文字列に結合するには、文字列関数の CONCATENATE を使います。
書式: CONCATENATE(文字列 1 [文字列 2], ...)
先ほどの「叩き台」のF列の数式をCONCATENATE関数に置き換えてみます。
F3="INSERT INTO "&$A$1&" (" &CONCATENATE($A$2,",",$B$2,",",$C$2,",",$D$2&",",$E$2)&") VALUES (" &CONCATENATE($A3,",",$B3,",",$C3,",",$D3,",",$E3)&");"
ううーん、&がカンマ(,)に変わっただけですね･･･。手間は叩き台と何も変わらないです。
改良版その2（CONCATENATE関数/スプシ）
そういえば、前に雑談用チャットルームで何度か呟かれていたような･･･。
弊社には、業務中の感動を共有してくれるエンジニアがたくさんいます！ありがたいですね！
ス、スプレッドシート!!!( ﾟдﾟ)!!!
ちなみにExcelでは、Excel2016から追加された「CONCAT」関数を使えば、範囲指定した文字列連結ができるようです。（※Office 365ユーザーのみ）
弊社はExcelのバージョンが古いので、ここからはExcelではなくGoogleスプレッドシート（スプシ*1）を使うことにします。
早速、スプシ上で「改良版その1」を再現し、CONCATENATEの引数をセル指定から範囲指定に変更してみました。
J3="INSERT INTO "&$A$1&" ("&CONCATENATE($A$2:$I$2)&") VALUES ("&CONCATENATE($A3:$I3)&");"
数式はだいぶすっきりしましたね！
ただこの方法は、画像の通り、カラムとカラムの間にカンマ(,)を入れる列が必要になります。うううーん、ちと面倒くさい･･･
改良版その3（JOIN関数/スプシ）
そういえば、MySQLにはCONCAT_WSという便利な関数があります。
文字列を連結するだけでなく、区切り文字まで入れてくれるという優れものです。
連結されたものをセパレータ付きで返します
CONCAT_WS( 区切文字列, 文字列1, 文字列2, ・・・文字列N )
スプシに同じような関数ないかな？とググってみると、「JOIN」という便利な関数を発見しました！ 早速使ってみます。
F3="INSERT INTO "&$A$1&" ("&JOIN(",",$A$2:$E$2)&") VALUES ("&JOIN(",",$A3:$E3)&");"
シートの見た目も数式も、とてもスッキリしました！ 範囲指定だからカラム増減にも強いですね！
※Excelでも「TEXTJOIN」という関数で同じようなことができますが、こちらもExcel2016でのみ使用できるようです。
さて、ここで「数値型カラム限定かよ！」という声が聞こえてきそうですね！
そうなんです、文字型カラムはシングルコーテーション(')で囲んであげたり、日付型カラムは書式を整えてあげる必要があります。
「改良版その3」をベースにし、カラムの型も考慮して完成版を作ってみました！
完成版
「各カラムの型（2行目）」「作業用セル（F列～J列）」「型の変換パターン（M列～N列）」を追加しました。
何をやっているか簡単に説明します。
１．まず作業用セルで、各カラムの型に応じて書式を整えます。
F4=SUBSTITUTE(VLOOKUP(A$2,$M:$N,2,false),"@@@",A4)
２．このF4セルの数式を作業用セルすべて（F列～J列）にコピーします。
３．K列に、先程と同様にJOIN関数でINSERT文を作成していきます。（ここで参照するのは作業用セル）
K4="INSERT INTO "&$A$1&" ("&JOIN(",",$A$3:$E$3)&") VALUES ("&JOIN(",",$F4:$J4)&");"
これでおしまいです٩( ‘ω’ )و
作成されたINSERT文：
INSERT INTO t_sanameko (id,name,price,like,created_at) VALUES (1,'えりんぎ',138,'好き',cast('2018/04/01' as date));
反省点
もっとスマートにできそうですが、sanamekoにはこれが限界でした。
以下、反省点です。
- 作業用セルは作らず、数式は1セルだけにしたかった。
- カラムの増減で、作業用の列も増減しなきゃいけないのは手間
- NULLのとき考えてなかった･･･。作業用セルに分岐入れるか･･･。
- 数式が入っているセルは背景色をつけたり太枠で囲えばより分かりやすいかも。
- 記事タイトルに「Excel」と書いたのに結局スプレッドシートになってしまった。
もっと精進します。ではでは～～！
[追記]スプシがさらにスマートに！
弊社エンジニアのねこちゃんが、このスプシに改良を加えてくれました！
これでもかというくらいスプシがスッキリしますので、ぜひぜひ試してみてください！
aimstogeek.hatenablog.com
＼\『明日のはたらくを創る』仲間を募集中!! /／ 919.jp
