/Excelの関数でINSERT文を生成しよう2019-06-30Excelの関数でINSERT文を生成しようTweet今の現場で、大量のエクセルデータをデータベースに登録する作業がありました。手作業でやるわけにいかないので、エクセルの関数を使ってINSERT文をひたすら生成しました。そのやり方と注意点をまとめてみたので、よろしければご覧ください。今回はこのようなエクセルデータを、対応するテーブルのカラムに入れていきます。ある形のデータですね。(このデータはランダムで生成された架空の個人情報です)Step2:カラム名を入れる2行目に行を追加して、ここにテーブルのカラム名を入れます。このカラム名を間違えると、いつまでたってもINSERTできないので、注意しましょう。セル参照と「&」でINSERT文生成ここから本題です。「M3」のセルにINSERT文を書いていきます。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:カラム名は「絶対参照」カラム名を指定するカッコの中をご覧ください。テーブル名(’この中’)」A2セルの参照が「$A$2」となっていますね。後々このINSERT文を下にコピーしても、常にこのセルを参照するようになります。「&」で文字列連結しているだけですが、ご覧の通り、長くなってとても分かりづらいです。インサートする値を指定する「VALUES(’この中’)」はごちゃごちゃしてわかりづらいですね。A3&”‘,'”ここまでを1つのブロックとして考えましょう。・どこまでが出力される文字列なのか・どこまでが出力されない記号なのかということに注意しましょう。これがつながっていき、最終的にVALUES(‘1′,’大野愛莉’,’オオノアイリ’,’0852135152′,’699-0814′,’島根県’,’出雲市’,’湖陵町常楽寺’,’3-14′,”,’1972/06/22′,’47’);というように、シングルクオートで括られた文字列がカンマ区切りで出力されるようになります。ダブルクオートや&が抜けていると、画像のようにセルを参照しているはずの箇所が黒くなってしまいます。このような時は・出力する文字列はダブルクオートで括られているか・セルの参照と文字列はちゃんと&で連結されているか・ちゃんと「セルの参照」として扱われているか(文字列として扱われていないか)といった所を1つづつ確認していきましょう。試しに1つだけインサートしてみるここまで出来たらあとはコピペして一気にインサートを...と思いますが、いきなり全部やるのではなく、ちゃんとインサート文として間違いがないか確認するために、まずは1つだけインサートしてみましょう。ここでエラーが起きたら、エラー箇所を特定してStep3に戻りましょう。Step5:インサート文を全行にコピーインサートが実行され、思い通りの結果が得られたら、全ての行にコピーしていきましょう。オートフィル(セルの右下の四角を下にドラッグするやつ)を使ってもいいですが、データが大量にあるとちょっと大変なので、もっと楽に素早くできる方法を紹介します。↓」を押すと、値が入力されているセルの一番最後のセルまで移動します。↑」を押すと...M列のINSERT文をコピーしたい箇所が全て選択されます。D」のショートカットキーは「選択したセルの一番上のセルの内容を、他のセルにコピー&貼り付け」というものです。今回のように大量のデータを扱う時に便利です。これでインサート文が生成されました!あとは流し込むだけです!まとめいかがだったでしょうか。今回のやり方を応用すればINSERT文だけでなく、UPDATE文も同じように作れますね。色々なところで活用してみてください。