今の現場で、大量のエクセルデータをデータベースに登録する作業がありました。
もちろん手作業でやるわけにいかないので、
エクセルの関数を使ってINSERT文をひたすら生成しました。
そのやり方と注意点をまとめてみたので、よろしければご覧ください。

Step1：エクセルデータを確認

今回はこのようなエクセルデータを、対応するテーブルのカラムに入れていきます。
よくある形のデータですね。


（このデータはランダムで生成された架空の個人情報です）

Step2：カラム名を入れる

2行目に行を追加して、ここにテーブルのカラム名を入れます。

このカラム名を間違えると、いつまでたってもINSERTできないので、注意しましょう。

Step3 セル参照と「＆」でINSERT文生成

ここから本題です。
「M3」のセルにINSERT文を書いていきます。

最終的な完成形はこちら

セルに入力する値

=”INSERT INTO staff_info(“&$A$2&”,”&$B$2&”,”&$C$2&”,”&$D$2&”,”&$E$2&”,”&$F$2&”,”&$G$2&”,”&$H$2&”,”&$I$2&”,”&$J$2&”,”&$K$2&”,”&$L$2&”)VALUES(‘”&A3&”‘,'”&B3&”‘,'”&C3&”‘,'”&D3&”‘,'”&E3&”‘,'”&F3&”‘,'”&G3&”‘,'”&H3&”‘,'”&I3&”‘,'”&J3&”‘,'”&K3&”‘,'”&L3&”‘);”

表示される文字列

INSERT INTO staff_info(id,name,kana,tel,post,pref,address1,address2,address3,address4,birthday,age)VALUES(‘1′,’大野愛莉’,’オオノアイリ’,’0852135152′,’699-0814′,’島根県’,’出雲市’,’湖陵町常楽寺’,’3-14′,”,’1972/06/22′,’47’);

 

Point1：カラム名は「絶対参照」

カラム名を指定するカッコの中をご覧ください。

「INSERT INTO テーブル名(’この中’)」

A2セルの参照が「$A$2」となっていますね。
この「$」をつける事で、絶対参照となり、
後々このINSERT文を下にコピーしても、常にこのセルを参照するようになります。

 

絶対参照だと、下にコピーしても常に2行目のテーブル名を参照している

 

相対参照だと、下にコピーした時に、参照するセルも一緒に下にずれる

Point2：シングルクオート、ダブルクオート、カンマに注意

基本的にはセルの参照と、ダブルクオートで括られた文字列を、
「＆」で文字列連結しているだけですが、
ご覧の通り、長くなってとても分かりづらいです。
特にインサートする値を指定する「VALUES(’この中’)」はごちゃごちゃしてわかりづらいですね。

A3&”‘,'”

ここまでを1つのブロックとして考えましょう。
A3セルの値　＋　シングルクオート　＋　カンマ　＋　シングルクオート
が出力されるので、この場合
「　1’,’　」
となります。

・どこまでが出力される文字列なのか
・どこまでが出力されない記号なのか

ということに注意しましょう。

これがつながっていき、最終的に
VALUES(‘1′,’大野愛莉’,’オオノアイリ’,’0852135152′,’699-0814′,’島根県’,’出雲市’,’湖陵町常楽寺’,’3-14′,”,’1972/06/22′,’47’);
というように、シングルクオートで括られた文字列がカンマ区切りで出力されるようになります。

 

ダブルクオートや＆が抜けていると、画像のようにセルを参照しているはずの箇所が黒くなってしまいます。


このような時は
・出力する文字列はダブルクオートで括られているか
・セルの参照と文字列はちゃんと＆で連結されているか
・ちゃんと「セルの参照」として扱われているか（文字列として扱われていないか）

といった所を1つづつ確認していきましょう。

Step4 試しに1つだけインサートしてみる

ここまで出来たらあとはコピペして一気にインサートを…
と思いますが、いきなり全部やるのではなく、
ちゃんとインサート文として間違いがないか確認するために、
まずは1つだけインサートしてみましょう。
ここでエラーが起きたら、エラー箇所を特定してStep3に戻りましょう。

Step5：インサート文を全行にコピー

インサートが実行され、思い通りの結果が得られたら、全ての行にコピーしていきましょう。
オートフィル（セルの右下の四角を下にドラッグするやつ）を使ってもいいですが、
データが大量にあるとちょっと大変なので、もっと楽に素早くできる方法を紹介します。

Point3 「Control + D」で一気にコピー

まずは「L3」セルにカーソルを合わせ

「Control + ↓」を押すと、値が入力されているセルの一番最後のセルまで移動します。

一個右のM列に移って「Control + shift + ↑」を押すと…

M列のINSERT文をコピーしたい箇所が全て選択されます。

あとは「Control + D」を押すと

はい、一気にコピペされました！

「Control + D」のショートカットキーは
「選択したセルの一番上のセルの内容を、他のセルにコピー＆貼り付け」
というものです。
今回のように大量のデータを扱う時に便利です。

これでインサート文が生成されました！
あとは流し込むだけです！

 

まとめ

いかがだったでしょうか。
今回のやり方を応用すればINSERT文だけでなく、UPDATE文も同じように作れますね。

ぜひ色々なところで活用してみてください。
ではまた！