ExcelやWeb上にある表からSQL文を自動生成する

webサイトなどにある表やExcelなどから一行ごとにINSERT文を自動で生成するプログラムをphpで作成してみました。

実行環境

Windows10
PHP 7.1.33

データのコピー

まず、INSERT文にするためのデータをコピーし、適当なファイルに貼り付けます。
例えば、以下のような表がweb上にあるとします。

ID	名前	価格
001	りんご	100円
002	ぶどう	200円
003	いちご	300円
004	バナナ	100円
005	ナシ	250円
006	スイカ	3000円
007	メロン	4000円
008	さくらんぼ	350円
009	モモ	600円
010	レモン	300円

この表のIDと名前だけでSQL文を作成したい場合は、Chromeの拡張機能にある「Copytables」を使うことで、以下の画像のように特定の列や行のみを選択することが可能となります。(Excelの場合はctrlキーなどを使ってうまくコピーして下さい)

コピーができたら、適当な名前でファイルを作成し、貼り付けます。(今回はdata.txt)

SQL文を生成するプログラム

次にPHPファイル(ここではinsert.php)を作成し、下のコードを記述して実行します。(解説は後ほど)

insert.php

<?php
header("Content-type: text/html; charset=utf-8");
$filename = "data.txt";
$file = file($filename);
$fp = fopen("insert_sql.txt","w");
for($i=0; $i<count($file); $i++){
  $data =  preg_split("/\s/", $file[$i], -1, PREG_SPLIT_NO_EMPTY);
  $data_values = "'".$data[0]."'";
  if(count($data) >= 2){
    for($j=1; $j<count($data); $j++){
      $data_values .= ", '".$data[$j]."'";
    }
  }
  $contents = "INSERT INTO test VALUES(".$data_values.");";
  fwrite($fp,$contents."\r\n");
}
fclose($fp);
?>

実行すると以下のように「insert_sql.txt」というファイルが作成され、そのなかにSQL文が記述されています。

ソースコードの解説

PHPファイルに記述したコードの内容について詳しく解説します。

まず、3~5行目ではデータを記述した「data.txt」というファイルをfile()関数を使って 、1行ずつ配列に格納します。その後、fopen()関数で書き込み可能なファイルを新規で作成し、開いています。

insert.php

$filename = "data.txt";
$file = file($filename);
$fp = fopen("insert_sql.txt","w");

次にfor文でデータの行数分だけSQL文を生成する準備をします。
count()関数で配列変数$fileにある要素の数を数えることで、その分だけ処理を繰り返すことができます。

insert.php

for($i=0; $i<count($file); $i++){

}

7行目ではpreg_split()関数を使って文字列をある文字列で区切って配列に格納します。
もし、データの区切りがカンマだった場合は第一引数を “/,/” にし、半角スペースだった場合は “/\s/” にしたりします。

insert.php

$data =  preg_split("/\s/", $file[$i], -1, PREG_SPLIT_NO_EMPTY);

8~11行目でinsert文の VALUES の中身を作成します。データの１行の中身を文字列で区切って配列に格納した配列変数$dataの要素分だけカンマで繋げていきます。

insert.php

$data_value = "'".$data[0]."'";
  if(count($data) >= 2){
    for($j=1; $j<count($data); $j++){
      $data_value .= ", '".$data[$j]."'";

その後、SQL文を作成し、変数に格納します。

insert.php

$contents = "INSERT INTO test VALUES(".$data_values.");";

fwite()関数で最初に作成して開いたファイルにSQL文を記述します。

insert.php

fwrite($fp,$contents."\r\n");

最後にfor文の外で開いていたファイルを閉じ、完成です。

insert.php

fclose($fp);

ソースコードを少し変えることで、いろんなパターンのSQL文に対応できると思います。