【Excel】Excelのみ(マクロ、VBAなどは使用しません)を使用してInsert文を作成する方法の紹介

大量のInsert文などを作成するときにはどのようにしていますか。

数行のデータ作成なら手動でもなんとかなりますが、数十行、数百行となると辛いことが多いと思います。

今回は、Excelの標準機能だけ(マクロ、VBAなどは使用しません)を使用してInsert文を作成する方法を紹介します。

注意事項

データ作成時の注意事項を記載しておきます。

シングルクォーテーションの書き方

SQLで文字列を宣言するためには、以下の記載例のように「'(シングルクォーテーション)」で括る必要があります。

insert into hoge values ('a');

また、Excelで「'」を入力するときには注意が必要です。Excelでは先頭に「'」を入力すると特殊な扱いになるため文字として認識されなくなります。入力する場合は「''」と連続して入力するようにしてください。

それぞれ、記載例とコピーしたときの挙動例を記載します。

【「'」を1つだけ入力したときの例】

'hoge'
-> コピーすると「hoge'」になります。 

【「''」を2つ連続して入力したときの例】

''hoge'
-> コピーすると「'hoge'」になります。 

作成方法

さまざまな作成方法があるため順番に簡単な方法から紹介していきます。

「&」の文字連結を使用する

Excelは文字を連結する際に「&」を使用することができます。この文字連結を使用して作成する方法です。

ABCDE
1Insert文ColumnAColumnBColumnCColumnD
2="insert into hoge values (" & B1 & "," & C1 & "," & D1 & "," & E1 & ");"anull''c'10

「A1」をコピーすると以下のSQLを作成することができます。後は行をコピーをすることで複数のInsert文を作成することができます。

insert into hoge values (a,null,'c',10);

この方法では、カラム数が増減した場合は、「&」を追加する必要があるため、ある程度の手間がかかります。

「CONCATENATE関数」、または、「CONCAT関数」を使用する

Excelの文字列操作をするための「CONCATENATE関数」、または、「CONCAT関数」を使用する方法です。

【注意】Excelのバージョンにより使用方法の変更が必要です

Excelのバージョンが「2016」以降の場合、「CONCAT関数」を使用することを推奨します。「CONCATENATE関数」は、今後のバージョンアップにより使用できなくなる可能性があるためです。

Office公式サイトに以下のように記載されています。

重要: Excel 2016、Excel Mobile、および Excel オンラインでは、この関数は CONCAT 関数に置き換えられています。
CONCATENATE 関数は、引き続き下位互換性がありますが、今後は CONCAT を使用することを検討する必要があります。
これは、CONCATENATE が Excel の将来のバージョンで利用できない可能性があるためです。

https://support.office.com/ja-jp/article/concatenate-%E9%96%A2%E6%95%B0-8f8ae884-2ca8-4f7a-b093-75d702bea31d

「CONCATENATE関数」を使用した方法を記載します。「CONCAT関数」も同じ方法で使用できるため、Excelのバージョンにより適宜変更してください。

ABCDE
1Insert文ColumnAColumnBColumnCColumnD
2=CONCATENATE("insert into hoge values (",B1,",",C1,",",D1,",",E1,");"anull''c'10

「A1」をコピーすると以下のSQLを作成することができます。後は行をコピーをすることで複数のInsert文を作成することができます。

insert into hoge values (a,null,'c',10); 

この方法は、「&」を記載する必要がないため、いくらか記載方法がシンプルになり見やすくなります。

「TEXTJOIN関数」(Excel2016以降 + Office365)

「Excel 2016以降」+「Office365」限定の機能ですが、連結するための区切り文字指定、範囲を指定することができる「TEXTJOIN関数」を使用する方法です。

ABCDE
1Insert文ColumnAColumnBColumnCColumnD
2="insert into hoge values (" & TEXTJOIN(",",FALSE,B1:E1) & ");"anull''c'10

「A1」をコピーすると以下のSQLを作成することができます。後は行をコピーをすることで複数のInsert文を作成することができます。

insert into hoge values (a,null,'c',10); 

この方法は、区切り文字も指定できますし、範囲指定も行えるので増減が発生して比較的容易に修正できます。

まとめ

「Excel2016以降」+「Office365」が使用できる環境なら「TEXTJOIN関数」を使用すると簡単に作成できると思います。

それ以外の環境の方は、「CONCATENATE関数」または「CONCAT関数」を使用するほうがメンテンナンスは簡単になると思います。

knowledge
スポンサーリンク
fumidzuki