プログラミング

【MySQL】Excelシートのデータから一括でSQL文を作成する方法

SQL一括INSERT
ねぇもりさん、SQLで大量のテストデータを作る良い方法ないかなぁ~

元データはExcelシートに作ってあるんだよね

もり
もり
それならExcel関数で上手く加工して、一括でSQL文を作ってみよう

Laravelのテスト用にテーブルにたくさんデータが必要だな~。INSERT文でレコード作れるらしいけど、1件ずつ作るのめんどうくさいなぁ~

というわけで、考えてみました。

レコードを作成するテーブルの情報

テーブル名は「books」で、カラムは3つあります。

  • id
  • author_id
  • book_title

 

Excelで作成したテストデータがこちら

SQL一括1

Excelシートのデータからクエリを作る

元データの左右に空白列を挿入します。目印としてセルを黄色にしています。

SQL一括1-2

VALUES以降のクエリを作成

booksテーブルにレコードを作成するINSERT文です。

INSERT INTO books VALUES('idの値','author_idの値','book_titleの値');

1件目(Excel2行目)の場合、VALUES以降の部分はこうなります

('1','1','白夜行');

この形式にするため、シングルクォートやカンマなどの記号を入力します。

SQL一括2

私はこんな感じで入力しています。

①先頭行の値を最終行まで一瞬でコピーする方法

先頭行のセルの右下にカーソルを合わせると、十字のマークが出現します。そのポイントで「ダブルクリック」します。

②シングルクォートは2つ並べる

‘,’と入力すると、最初のシングルクォート以降が消えて,’となってしまうので、,’と入力しています。

(※Excelでは、1つめのシングルクォート以降が文字列として認識されるため、先頭のシングルクォートは見た目消えてしまう)

SQL一括2-2

 

A列~G列の文字列を&で結合します。

SQL一括3

H列にVALUES以降のクエリができました。

SQL一括4

INSERT文と連結させる

I列にINSERT文を入力し、H列と結合します。

INSERT INTO books VALUES の部分を文字列として認識させたいので、前後をダブルクオーテーションで囲っています。

I2セルに下記の数式を入力したら、最終行までコピーします。セルの右下にカーソルを合わせて「ダブルクリック」です!

="INSERT INTO books VALUES"&H2

SQL一括5

クエリのできあがり

SQL一括6

Excelシート上で作成したクエリを実行する

動画をご覧ください。下記の4つの作業をしています。

use データベース名;のコマンドで、事前にデータベースの切り替えをしておきましょう!

  1. booksテーブルが空であることを確認
  2. Excelで作成したI列のクエリを一括コピー
  3. ターミナルに貼り付け
  4. booksテーブルにレコードが作成されていることを確認

この手順なら、Excelシートにデータさえ作成しておけば、10行でも100行でも手間は同じです!

ノンプログラマーがプログラミングを学ぶ理由

ノンプログラマーがプログラミングを使えるようになると、「仕事の幅」が広がります。

日常生活にたとえると「車の運転」のイメージです。

多くの人は、大人になると自動車教習所に通って、運転の練習をしますね。F1レーサーやタクシードライバーなどの「プロ」になるわけではないのに。

車の運転ができれば、遠いところへ、速く・ラクに移動できて「生活の幅」が広がるからです。

プログラミングができるようになれば、今の仕事を速く・ラクに片づけて、「仕事の幅」を広げることができます。

「プロ」を目指す必要はありません。今の仕事をより豊かにするための「手段」、それがプログラミングです。

1週間のオンライン無料体験
いつでもどこでも今すぐ視聴OK