Logicky BLOG

Logickyの開発ブログです

  • Javascript
  • Python
  • PHP
  • Go
  • OS・サーバ
  • 機械学習
  • つくったもの
  • 数学
  • アルゴリズム
  • Logicky

CSVからテーブル作成用のSQL文を作成するPHP

macのnumbersで、windowsエクセルを開き、csv化することは問題なくできるが、フィールドの値にカンマが使われていたり、改行文字が使われている場合は注意が必要である。カンマが使われている場合は往々にしてあるので、事前にカンマを<カンマ>などのユニークでフィールド内の値として利用されていない文字列に変換する必要がある。また、改行文字が使われている場合は、それを<br>に変換する必要がある(これはフィールドの値の利用方法によって変わる)。

この2つの事前準備をnumbers上で実施した上で、csvとして書き出す。書き出した後で、下記ソースコードのPHPファイル実行する。このPHPファイルで先程のcsvファイルを読み込む。これによって、csvファイルの内容に応じた、MySQLのテーブルを作成する為のSQLが出力されるので、そのSQLをphpmyadmin上で実行する。すると、テーブルが作成される。合わせて、PHPファイルの実行によって、読み込んだCSVファイルを加工して出力することができる。加工内容は、区切り文字をカンマから任意の文字列に変更することと,numbers上で変換した<カンマ>をカンマに戻すことの2点である。この加工後の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;?> (
<?php
for ($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>
  • Javascript
  • Python
  • PHP
  • Go
  • OS・サーバ
  • 機械学習
  • つくったもの
  • 数学
  • アルゴリズム
  • Logicky