CSVからテーブル作成用のSQL文を作成するPHP
macのnumbersで、windowsエクセルを開き、csv化することは問題なくできるが、フィールドの値にカンマが使われていたり、改行文字が使われている場合は注意が必要である。カンマが使われている場合は往々にしてあるので、事前にカンマを<カンマ>などのユニークでフィールド内の値として利用されていない文字列に変換する必要がある。また、改行文字が使われている場合は、それを
に変換する必要がある（これはフィールドの値の利用方法によって変わる）。
この２つの事前準備をnumbers上で実施した上で、csvとして書き出す。書き出した後で、下記ソースコードのPHPファイル実行する。このPHPファイルで先程のcsvファイルを読み込む。これによって、csvファイルの内容に応じた、MySQLのテーブルを作成する為のSQLが出力されるので、そのSQLをphpmyadmin上で実行する。すると、テーブルが作成される。合わせて、PHPファイルの実行によって、読み込んだCSVファイルを加工して出力することができる。加工内容は、区切り文字をカンマから任意の文字列に変更することと,numbers上で変換した<カンマ>をカンマに戻すことの２点である。この加工後のcsvファイルを、phpmyadminのインポート機能によってインポートすれば、テーブル作成〜データインポートまでが完了となる。
<html><head><title>Make Table</title><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /></head><body>
<?php/***** 設定項目 ******/
$database_name = 'データベース名'; //データベース名$table_name = 'テーブル名'; //テーブル名
$file_name = '開くファイルの名前'; //開くファイルの名前$skip_lines = 2; //スキップする行数$kugiri = ","; //csvの区切り文字$primary = 'プライマリーキーのフィールド名'; //プライマリーキーのフィールド名$auto_increment = false; //AUTO_INCREMENTの有無
$csv_output = true; //csvファイルを出力するか？$kugiri_after = "$"; //区切り文字の設定$comma_change_char = "<カンマ>"; //カンマにチェンジする文字列
/***** 設定項目 終了 ******/
$line_no = 0; //現在の行$field_name_list = array(); //フィールド名のリスト$field_number_list = array(); //フィールドの最大文字数のリスト$field_type_list = array(); //フィールドのタイプのリスト$column = 0; //カラム数（カラム数チェック用$file_all_content = ''; //ファイルの全内容
if ($fp = fopen ( $file_name, "r" )){ while (! feof ($fp)) { $line_no++; $load = fgets ($fp, 30000); //フィールド名の取得 if($line_no == $skip_lines + 1){ $file_all_content .= $load; $load = str_replace(array("\r\n","\r","\n"), '', $load); $field_name_list = explode($kugiri,$load); $column = count($field_name_list); //各フィールドのボリュームの取得 }else if($line_no > $skip_lines){ $file_all_content .= $load; $load = str_replace(array("\r\n","\r","\n"), '', $load); $field_content_list = explode($kugiri,$load); //カラム数チェック if($column != count($field_content_list)){ print 'カラム数が不正です->' . $line_no .'行目<br>'; print count($field_content_list) . '<br>'; print $load . '<hr>'; }else{ $column = count($field_content_list); } for ($i = 0; $i<count($field_content_list); $i++){ //文字数の測定 $number = mb_strlen($field_content_list[$i], "UTF-8"); if($field_number_list[$i] < $number){ $field_number_list[$i] = $number; } //文字列タイプの測定 $type = ''; if(preg_match('/^[0-9]+$/',$field_content_list[$i])){ if($field_number_list[$i] == 1){ if($field_content_list[$i] == 0 or $field_content_list[$i] == 1){ $type = 'TINYINT'; }else{ $type = 'INT'; } }else{ $type = 'INT'; } }else{ $type = 'VARCHAR'; }
//文字列タイプの登録 $type_old = $field_type_list[$i]; if(isset($field_type_list[$i])){ if($type != $type_old){ if($type == 'VARCHAR'){ $field_type_list[$i] = 'VARCHAR'; }else if($type == 'INT'){ if($type_old != 'VARCHAR'){ $field_type_list[$i] = 'INT'; } }else if($typ == 'TINYINT'){ if($type_old != 'VARCHAR' and $type_old != 'INT'){ $field_type_list[$i] = 'TINYINT'; } } } }else{ $field_type_list[$i] = $type; } } } } fclose ($fp);}?>
<?php//csvファイルの出力if($csv_output){ $file_all_content = str_replace($kugiri, $kugiri_after, $file_all_content); $file_all_content = str_replace($comma_change_char, ',', $file_all_content); $file_all_content .= "\n"; if ($fp = fopen ( 'after.csv', "w" )){ if (fwrite($fp, $file_all_content) === FALSE){ print 'csvファイルの更新に失敗しました！' ; } fclose ($fp); }else{ print 'csvファイルが開けませんでした！'; }}?>
<pre>CREATE TABLE <?php echo $database_name;?>.<?php echo $table_name;?> (<?phpfor ($i = 0; $i<count($field_name_list); $i++){ echo $field_name_list[$i] . " "; if($field_name_list[$i] == $primary){ if($auto_increment){ echo "INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY,\n"; }else{ echo "INT( 10 ) NOT NULL PRIMARY KEY,\n"; } }else if($i == count($field_name_list) - 1){ echo $field_type_list[$i] . "( "; echo $field_number_list[$i] . " ) "; echo "NULL\n"; }else{ echo $field_type_list[$i] . "( "; echo $field_number_list[$i] . " ) "; echo "NULL,\n"; }}?>) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;</pre></body></html>