導入
こんにちは、GxPの土田です。
この記事は グロースエクスパートナーズ Advent Calendar 2024 の1日目です。
初日からネタ枠でごめんなさい。
表題とgifの通り、どんなテーブル定義でも対応可能なinsert文生成用Excel関数を作りました。
基礎的な関数から応用まで、いろいろな関数が出てきて面白いので解説しちゃいます。
こんなこともできるんだ～程度に受け止めてください。
※多少はSQLの仕様にも触れますが、メインはExcel関数です。
普通に便利なので関数だけ使っていただいてもOK。使う際は下の 設定 だけ読めば使えます。
関数
早速、関数です。
=CONCAT("insert into ",VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IFERROR(TEXTAFTER(CELL("filename",$A$1),"]"),$A$1),"~","~~"),"?","~?"),"*","~*"),設定!$B:$C,2,FALSE),"(",TEXTJOIN(", ",FALSE,MAP(LET(COLUMNS,OFFSET($B$3,0,0,1,COLUMN()-3),IF(COUNTA(common_column_names)=1,COLUMNS,HSTACK(COLUMNS,TRANSPOSE(DROP(common_column_names,1))))),LAMBDA(I,CONCAT(colqs,I,colqe)))),")"," values(",TEXTJOIN(", ",FALSE,MAP(OFFSET(INDEX($B:$B,ROW()),0,0,1,COLUMN()-3),LAMBDA(I,LET(DATATYPE,INDEX($4:$4,COLUMN(I)),IFS(AND(NOT(ISBLANK(escape_symbol)),LEFT(I)=escape_symbol),RIGHT(I,LEN(I)-LEN(escape_symbol)),LOWER(I)="null","null",AND(OR(MAP(DROP(string_columns,1),LAMBDA(J,LOWER(LEFT(DATATYPE,LEN(J)))=J))),MAP({"sysdatetime","sysdatetimeoffset"},LAMBDA(J,NOT(AND(LOWER(LEFT(I,LEN(J)+1))=J&"(",RIGHT(I,1)=")"))))),CONCAT(strq,I,strq),TRUE,I))))),IF(COUNTA(common_column_values)=1,"",", " & TEXTJOIN(", ",FALSE,DROP(common_column_values,1))),")",LET(COMMENT,OFFSET($A$1,ROW()-1,0),IF(ISBLANK(COMMENT),"",CONCAT("/* ",COMMENT," */"))),";")
（一口ライフハック、Excel関数はR言語モードにするといい感じにハイライトされます）
スペースを全て削ったこの状態で1017文字。
はっきり言って長すぎます。
読みやすくに整形したものがこちら。
=CONCAT(
"insert into ",
VLOOKUP(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
IFERROR(
TEXTAFTER(CELL("filename",$A$1),"]"),
$A$1
),
"~", "~~"), "?", "~?"), "*", "~*"
),
設定!$B:$C, 2, FALSE
),
"(",
TEXTJOIN(
", ", FALSE,
MAP(
LET(
COLUMNS,
OFFSET($B$3, 0, 0, 1, COLUMN()-3),
IF(
COUNTA(common_column_names)=1,
COLUMNS,
HSTACK(
COLUMNS,
TRANSPOSE(DROP(common_column_names, 1))
)
)
),
LAMBDA(I, CONCAT(colqs, I, colqe))
)
),
")",
" values(",
TEXTJOIN(
", ", FALSE,
MAP(
OFFSET(INDEX($B:$B, ROW()), 0, 0, 1, COLUMN()-3),
LAMBDA(I,
LET(
DATATYPE,
INDEX($4:$4, COLUMN(I)),
IFS(
AND(NOT(ISBLANK(escape_symbol)), LEFT(I)=escape_symbol),
RIGHT(I, LEN(I)-LEN(escape_symbol)),
LOWER(I)="null",
"null",
AND(
OR(
MAP(
DROP(string_columns, 1),
LAMBDA(J,
LOWER(LEFT(DATATYPE, LEN(J)))=J
)
)
),
MAP(
{"sysdatetime", "sysdatetimeoffset"},
LAMBDA(J,
NOT(
AND(
LOWER(LEFT(I, LEN(J)+1))=J&"(",
RIGHT(I, 1)=")"
)
)
)
)
),
CONCAT(strq, I, strq),
TRUE,
I
)
)
)
)
),
IF(
COUNTA(common_column_values)=1,
"",
", " & TEXTJOIN(", ", FALSE, DROP(common_column_values, 1))
),
")",
LET(
COMMENT,
OFFSET($A$1, ROW()-1, 0),
IF(ISBLANK(COMMENT), "", CONCAT("/* ", COMMENT, " */"))
),
";"
)
はっきり言って長すぎます。（2回目）
設定
設定シート
設定シートに画像のように設定を記述し、適宜名前付き範囲を設定します。
画像内の設定は SQL Server を前提とした設定ですが、各DBMSの設定に書き換えることでどのようなDBMSにも対応可能です。
- A列：論理名
- B列：シート名
- C列：物理名
- 名前付き範囲
strq：文字列のクォート記号を書いた1セルを指定する。 - 名前付き範囲
colqs：列名のクォート開始記号を書いた1セルを指定する。 - 名前付き範囲
colqe：列名のクォート終了記号を書いた1セルを指定する。 - 名前付き範囲
escape_symbol：シングルクォートを付けたくない場合、セル値の最初に書くエスケープシンボルを書き、1セルを範囲に指定する。 - 名前付き範囲
string_columns：クォートが必要な型一覧を記述し範囲に指定する。ヘッダ行も範囲に含める。 - 名前付き範囲
common_column_names：テーブル定義には書かないが、insertに含めたい共通列を記述し範囲に指定する。ヘッダ行も含める。共通列が無い場合はヘッダの1セルのみを範囲に指定する。 - 名前付き範囲
common_column_values：テーブル定義には書かないが、insertに含めたい共通列の値を記述し範囲に指定する。ヘッダ行も含める。共通列が無い場合はヘッダの1セルのみを範囲に指定する。
各テーブル定義シート
- A1セル：シート名に完全一致（ローカルPC上のファイルであれば不要）
- 3行目（B列以降）：カラム物理名を記述
- 4行目（B列以降）：型を記述（文字数等
char(10)も記述可） - 5行目以降：データ
- A列：行コメント（必要な場合）
- B列以降、3行目にカラム物理名がある列
- クウォート文字のエスケープが必要な場合、セル内にエスケープした値を記述する
- 一番右のカラムの一つ右の列：何も書かない（書いてもよいが読み捨てられる）
- さらにもう一つ右の列：上記関数を記述する
これだけです。null（大小文字問わず）と記述したセルはnullとして扱われます。
データの書き換えはもちろん、新たに列を挿入したり、行を追加したりしても自動で反映されます。
解説
前提知識のために多少は前後しますが、基本的には頭から解説していきます。
以下、関数内の省略は ... で表記します。
結合（CONCAT）
=CONCAT(
"insert into ",
...,
";"
)
これは基礎的な関数。文字列を結合するだけです。 ="text a" & "text b" のように & で結合するのと同じです。
絶対参照（$）
A1 のように書けばA1セルを参照できます。
そしてExcelは関数を上下左右にコピーすると、気を利かせてズレた分参照もズラしてくれます。
=A1 を1つ下の行にコピーすると =A2 になるといった具合。
この挙動を $ を行列それぞれに付けることで回避できます。これは絶対参照と呼ばれるものです。
=$A1 とすると、水平方向へのコピーは参照を書き換えません。しかし1には$を付けていないため、水平方向へのコピーは参照を書き換えます。
シート名（TEXTAFTER, CELL）
TEXTAFTER(CELL("filename",$A$1),"]")
これはおまじないです。これでシート名を文字列で取得できます。
しかし、ExcelOnline（SharePointなど）上でExcelファイルを開くとこれはエラーを返します。
エラー処理（IFERROR）
IFERROR(
TEXTAFTER(CELL("filename",$A$1),"]"),
$A$1
)
前述のExcelOnline上でのエラーをどう回避するかというと、IFERROR で拾ってあげます。
第一引数でエラーが発生したら第二引数の結果を返します。エラーが発生しなければそのまま第一引数の結果を返します。
文字列置換（SUBSTITUTE）
=SUBSTITUTE("abc", "b", "c")
これは "abc" の中の "b" を "c" に置換するという意味です。
垂直方向検索（VLOOKUP）
=VLOOKUP(検索値, 範囲, 列番号, 検索方法),
テーブルから垂直方向にデータを検索し、ヒットした行と同じ行の列番号のデータを返す関数です。
今回はどう使っているかというと、
VLOOKUP(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
IFERROR(
TEXTAFTER(CELL("filename",$A$1),"]"),
$A$1
),
"~", "~~"), "?", "~?"), "*", "~*"
),
設定!$B:$C, 2, FALSE
)
検索値：シート名をいろいろ置換した結果
範囲：設定シートのB列C列
列番号：2(つまりB列が一致した行のC列を返す)
検索方法：FALSE(完全一致)
何故置換を行っているかというと、VLOOKUPの検索値において、 ~, ?, * はワイルドカードに当たります。
これらを残すと意図しない検索になる可能性があるため、各々エスケープを行っています。
水平方向の HLOOKUP もあります。
文字列区切り文字結合（TEXTJOIN）
=TEXTJOIN(区切り文字, 空のセルは無視, テキスト1, ..., テキストn)
区切り文字を使用して文字列を結合する関数です。
第三引数以降は1~複数指定できます。範囲をしていすると範囲内の全てのセルの値を結合します。
TEXTJOIN(
", ", FALSE,
...
)
今回はカラム名をカンマ区切りで結合するのに使用しています。
繰り返し処理（MAP, LAMBDA）
=MAP(array, LAMBDA(parameter, calculation))
MAP, LAMBDA はペアで使います。
MAP の第一引数には配列を指定します。
LAMBDA の第一引数には変数名、第二引数には処理を記述します。処理内では定義した変数を使用できます。
MAP(
...,
LAMBDA(I, CONCAT(colqs, I, colqe))
)
colqs, colqe は名前付き範囲です。実際にはそれぞれ1セルを指しています。
今回はこれにより、カラム名にクォートを付与しています。クォートが不要な場合は名前付き範囲のセル値を空にします。
配列先頭（末尾）除外（DROP）
=DROP(array, rows, [columns])
第一引数に指定した配列を第二引数の行数分除外します。第三引数で列除外も可。
今回は DROP(common_column_names, 1) のように名前付き範囲からヘッダを除外するために使用しています。
何故最初からヘッダを抜いた名前付き範囲にしないかというと、共通列が無かった場合にExcelでは0セルの名前付き範囲は設定できないからです。
そのため、固定で1行以上が存在するようヘッダを名前付き範囲に含め、除外するという仕様にしています。
配列結合（HSTACK, VSTACK）
=HSTACK(array_1, ..., array_n)
=VSTACK(array_1, ..., array_n)
それぞれ水平方向・垂直方向の範囲を結合して範囲を返します。
行・列番号取得（ROW, COLUMN）
=ROW([参照])
=COLUMN([参照])
引数に指定したセルの、何も指定しなければ関数を書いたセルの行・列番号を数値で取得できます。
番号指定範囲切り抜き（OFFSET）
=OFFSET(参照, 行数, 列数, [高さ], [幅])
公式的には
セルまたはセル範囲から指定された行数と列数だけシフトした位置にあるセル範囲の参照を返します。 返されるセル参照は、単一のセル、セル範囲のいずれかの参照です。 また、返されるセル参照の行数と列数を指定することもできます。
と説明されています。
今回は 0, 0 を指定して位置をズラさず、指定のセルから行数と列数を指定して範囲を切り抜くために使用してます。
なぜ初めから範囲指定せずにひと手間掛けているかというと、ざっくり言えばテーブルの列数が何列あるかわからないからですね。
OFFSET($B$3, 0, 0, 1, COLUMN()-3)
のように関数を書いたセルの列番号（COLUMN()）を起点に計算することで、テーブルに何列あるか特定しています。
-3 しているのはA列と関数を書いた列、関数を書いた1つ左の列を列数から除外するためです。
こうして列の論理名一覧を範囲で切り出しています。
分岐（IF, IFS）
=IF(論理式, [値が真の場合], [値が偽の場合])
=IFS(論理式1, 値が真の場合1, ..., 論理式n, 値が真の場合n)
義務教育で習う関数ですね。
変数定義（LET）
=LET(名前, 値, 計算)
第二引数の計算結果を第一引数に指定した名前の変数に格納します。
変数は第三引数の計算で使用できます。
LET(
COLUMNS,
OFFSET($B$3, 0, 0, 1, COLUMN()-3),
IF(
...
)
)
今回は COLIMNS という変数に先ほどの論理名一覧をCOLUMNSに格納しています。
カウント（COUNT, COUNTA）
=COUNT(値1, [..., 値n])
=COUNTA(値1, [..., 値n])
これも義務教育シリーズ。
それぞれ、数値のセルをカウント・空でないセルをカウント。
行列入れ替え（TRANSPOSE）
=TRANSPOSE(配列)
引数に指定した配列の行列を入れ替えます。
今回は何に使用しているかというと、
HSTACK(
COLUMNS,
TRANSPOSE(DROP(common_column_names, 1))
)
のように HSTACK に渡す際、COLUMNS は水平方向、common_column_names は垂直方向の範囲のため、方向を揃えるために使用しています。これでシートに定義された列一覧と common_column_names に定義された共通列を結合して一貫して処理できます。
前半部まとめ
ここまででinsert文前半が終わりました。
=CONCAT(
"insert into ",
VLOOKUP(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
IFERROR(
TEXTAFTER(CELL("filename",$A$1),"]"),
$A$1
),
"~", "~~"), "?", "~?"), "*", "~*"
),
設定!$B:$C, 2, FALSE
),
"(",
TEXTJOIN(
", ", FALSE,
MAP(
LET(
COLUMNS,
OFFSET($B$3, 0, 0, 1, COLUMN()-3),
IF(
COUNTA(common_column_names)=1,
COLUMNS,
HSTACK(
COLUMNS,
TRANSPOSE(DROP(common_column_names, 1))
)
)
),
LAMBDA(I, CONCAT(colqs, I, colqe))
)
),
")"
これで insert into table_name([col_1], [col_2], ..., [col_n]) の部分ができました。
さて続いて後半部です。関数はここからが重いです。
配列行列指定参照（INDEX）
=INDEX(配列, 行番号, [列番号])
配列から行・列番号に指定した位置のセル値（参照）を返します。
OFFSET(INDEX($B:$B, ROW()), 0, 0, 1, COLUMN()-3)
前半部では、カラム物理名一覧を範囲として切り抜くために OFFSET を使用していましたが、今回は第一引数が OFFSET に変わっています。
物理名は固定で3行目にありますが、値はそれぞれの行（関数を書いた行と同じ行）に書かれています。
最初に何行目に関数がペーストされるかわからないため、素直な参照も使用できません。
そのため、現在の行番号（ROW()）を使用して開始位置を特定しています。また、INDEX の第一引数に見慣れない範囲指定がありますが、これは「B列全て」という意味です。
つまり、INDEX($B:$B, ROW()) の部分は「B列の自分と同じ行のセルを返してくれや」という意味です。
後は前半部での使い方と同じですね。
今回 DATATYPEに格納してる INDEX($4:$4, COLUMN(I)) は「4行目のIと同じ列のセル」を返します。つまり各列の型ですね。
論理式（OR, AND, NOT）
=OR(論理式1, [..., 論理式n])
=AND(論理式1, [..., 論理式n])
=NOT(論理式)
分岐と組み合わせて使用します。
OR, AND は範囲を渡すこともできます。MAP で真偽値に処理した結果を渡すことで、高度な関数を組むことができます。
後半部の仕様
さて、ここからは仕様が頭に入っていないとさっぱりだと思うのでここで仕様説明を挟みます。
" values(",
TEXTJOIN(
", ", FALSE,
MAP(
OFFSET(INDEX($B:$B, ROW()), 0, 0, 1, COLUMN()-3),
LAMBDA(I,
LET(
DATATYPE,
INDEX($4:$4, COLUMN(I)),
IFS(
AND(NOT(ISBLANK(escape_symbol)), LEFT(I)=escape_symbol),
RIGHT(I, LEN(I)-LEN(escape_symbol)),
LOWER(I)="null",
"null",
AND(
OR(
MAP(
DROP(string_columns, 1),
LAMBDA(J,
LOWER(LEFT(DATATYPE, LEN(J)))=J
)
)
),
MAP(
{"sysdatetime", "sysdatetimeoffset"},
LAMBDA(J,
NOT(
AND(
LOWER(LEFT(I, LEN(J)+1))=J&"(",
RIGHT(I, 1)=")"
)
)
)
)
),
CONCAT(strq, I, strq),
TRUE,
I
)
)
)
)
),
IF(
COUNTA(common_column_values)=1,
"",
", " & TEXTJOIN(", ", FALSE, DROP(common_column_values, 1))
),
")",
LET(
COMMENT,
OFFSET($A$1, ROW()-1, 0),
IF(ISBLANK(COMMENT), "", CONCAT("/* ", COMMENT, " */"))
),
";"
まず、SQL内で値にはクォートの要否の2種類があり、これらは型によって違います。
必要なのは文字列や日時型です。型のクォート要否に関わらず、クォートが不要な null を格納することもできます。
逆に、nullにクォートとつけてしまうと 'null' という文字列として扱われてしまいます。
後半部で行っているのは、
- 各列を
MAPで繰り返し処理- 値の最初にエスケープシンボルがあるか判定
- 真：エスケープシンボルを除外した部分をそのまま値として使用する
- 値が
nullか判定- 真：nullを値とする
- 型がクォート必須型かどうかを判定
- 真：値がSQL関数リストに含まれるか判定
- 偽：クォートを付与する
- 真：値がSQL関数リストに含まれるか判定
- 他：値をそのまま値とする
- 値の最初にエスケープシンボルがあるか判定
- おまけ：A列に値があればinsert文の一番後ろにコメントとして記述する
です。
AND(NOT(ISBLANK(escape_symbol)), LEFT(I)=escape_symbol),
RIGHT(I, LEN(I)-LEN(escape_symbol)),
LOWER(I)="null",
"null",
AND(
OR(
MAP(
DROP(string_columns, 1),
LAMBDA(J,
LOWER(LEFT(DATATYPE, LEN(J)))=J
)
)
),
MAP(
{"sysdatetime", "sysdatetimeoffset"},
LAMBDA(J,
NOT(
AND(
LOWER(LEFT(I, LEN(J)+1))=J&"(",
RIGHT(I, 1)=")"
)
)
)
)
),
CONCAT(strq, I, strq),
TRUE,
I
)
この部分で前述の分岐を表しています。
文字列操作系（LOWER, LEFT, RIGHT, LEN）
=LOWER(文字列)
=LEFT(文字列, [文字数])
=RIGHT(文字列, [文字数])
=LEN(文字列)
それぞれ、小文字変換・左切り抜き・右切り抜き・文字数返却 の関数です。使っていませんがもちろん UPPER もあります。
まず使っているのは、
IFS(
AND(NOT(ISBLANK(escape_symbol)), LEFT(I)=escape_symbol),
RIGHT(I, LEN(I)-LEN(escape_symbol)),
...
)
の部分。 escape_symbol が空でなければ値の先頭と一致するかを調べます。
一致した場合は escape_symbol を先頭から切り取った値を値として使用します。
次は、
IFS(
...
OR(I="", LOWER(I)="null"),
"null",
...
)
の部分。これは値が null や Null, NULL の場合、全て小文字に丸めて null に一致するかを判定しています。Excelにおいて、セルが空であることは "" で判別します。
これで、
- 値が
null、もしくはセルが空か判定
- 真：nullを値とする
の部分を実現しています。
次に、
OR(
MAP(
DROP(string_columns, 1),
LAMBDA(
J,
LOWER(LEFT(DATATYPE, LEN(J)))=J
)
)
)
の部分。string_columns はクォート対象型一覧の名前付き範囲です。
型一覧を繰り返し処理、各型に処理対処の型（DATATYPE）が一致するかを調べています。
DATATYPE には varchar(10) のように文字数指定を含んだ型が格納されている可能性があるため、前方一致で判定しています。それが LOWER(LEFT(DATATYPE, LEN(J)))=J の部分。
クォート対象型と同じ文字数分を前方から切り取って、一致すれば真としています。
MAP で真偽化した全てを OR に渡すことで、どれか一つでもと一致すれば真となります。
察しのよい方なら気づいたかもしれませんが、バグがあります。
前方一致で判定しているため、例えばクォート対象の string という型とクォート非対象の string_ja_naiyo という型があった時、後者は string に前方一致するためクォートされてしまいます。
しかし、現状それによって困ることが無いため許容しています。正規表現が使えれば意図通りの判定ができるのでしょうが、悲しいかなExcel関数ではそれがサポートされていないのです。
実際には datetimeoffset は datetime に前方一致するが、どちらもクォート対象なので困らない、といった具合です。
配列（{}）
={"a", "b"}
={"a", "b"; "c", "d"}
このように配列を定義できます。2次元配列は ; で区切ります。
これも MAP で繰り返しができます。
MAP(
{"sysdatetime", "sysdatetimeoffset"},
LAMBDA(
J,
NOT(
AND(
LOWER(LEFT(I, LEN(J)+1))=J&"(",
RIGHT(I, 1)=")"
)
)
)
)
この部分で値が関数リストのどれかに一致するかを判定し、NOT で反転しています。
さらに1つ上の AND と組み合わせて、
- 型がクォート必須型かどうかを判定
- 真：値がSQL関数リストに含まれるか判定
の部分を実現しています。
これも実際に使うときには名前付き範囲にした方が使い易いんでしょうが、せっかくなので使ってみようという事で配列にしています。
IFS(
AND(ISBLANK(escape_symbol)<>0, LEFT(I)=escape_symbol),
RIGHT(I, LEN(I)-LEN(escape_symbol)),
LOWER(I)="null",
"null",
...,
CONCAT(strq, I, strq),
TRUE,
I
)
クォート対象型かつ値が関数リストに含まれない場合は CONCAT(strq, I, strq) でクォートを付与して終了です。
クウォート文字のエスケープは実装していません（エスケープ方法がRDMSによってかなり違ったりするため）。値をセルに記述するときに、エスケープした値を記述することでエラーの無いSQLを出力できます。（例： It\’s a beautiful day. とデータセルに入力すると、'It\’s a beautiful day.' のようにSQLに出力されるためエラーにならない）
IFS において、elseは最後に TRUE, 値 を置くことで実現します。今回はクォートせずそのまま値として使用するのに TRUE, I としています。
さて、関数は（たぶん）全て出きったので残りをさらっと解説です。
IF(
COUNTA(common_column_values)=1,
"",
", " & TEXTJOIN(", ", FALSE, DROP(common_column_values, 1))
)
これは共通列があった場合に共通列値を付けているところ。
LET(
COMMENT,
OFFSET(A1, ROW()-1, 0),
IF(ISBLANK(COMMENT), "", CONCAT("/* ", COMMENT, " */"))
)
最後にこれはA列が空でなかった場合、後ろにSQLコメントで付け足しているところです。
OFFSET(A1, ROW()-1, 0) は現在のセルと同じ行のA列という意味です。
締め
いかがでしたでしょうか（常套句）
AND, CELL, COLUMN, CONCAT, COUNTA, DROP, HSTACK, IF, IFERROR, IFS, INDEX, ISBLANK, LAMBDA, LEFT, LEN, LET, LOWER, MAP, NOT, OFFSET, OR, RIGHT, ROW, SUBSTITUTE, TEXTAFTER, TEXTJOIN, TRANSPOSE, VLOOKUP 総勢28名でお送りしました。
皆さんの推し関数をコメントで教えてください！ではまた！
おまけ
ここまで読んでくださった方には テンプレート を差し上げます。
{"sysdatetime", "sysdatetimeoffset"} と配列でべた書きされていた部分は、名前付き範囲 functions に置き換えられているバージョンです。
ご自由にお使いください。