Excel のデータをMySQLに入れたいけど、CSV形式は何かと大変
Excel のデータをMySQLに入れたいけど、CSVだと何かと大変。Excelって、・セルの中で改行していたり、・データ区切り文字に使いたいカンマ(、)・文字列囲みのダブルコーテーション(”)とかが、含まれていたりすると、インポートするときに、そこそこ面倒です。phpMyAdmin だと、CSVを画面からインポートすることができるのですが、100%の確率で、上記のような理由で、エラーとなってしまいます。エラーをすべてつぶしていると、件数が多いと、かなりの時間を使ってしまいます。そこで、オススメなのが、XML形式です。前提としては、MySqlのクライアントが必要です。MySqlクライアントから、Loadコマンドを利用します。■MySQLのLoadコマンドで取り込む(ゴールイメージ)XMLファイルを準備して、コマンドラインから、こんな感じで実行します。たとえば、mysql --host ホスト名 --user ユーザID -pパスワード <<EOFuse データベース名;set character_set_client="utf8";truncate table table_chumon;load xml local infile "D:\chumon.xml" replace into table table_chumon rows identified by "<table_chumon>" EOF上の例では、・ホスト名、ユーザID、パスワード、データベース名:環境にあわせて指定・取り込むファイルを指定:"D:\chumon.xml" ・取り込み先のテーブルを指定:table_chumon・レコードの区切りの指定:<table_chumon> (ここは、作成したXMLファイルに記述されている文字列に合わせるのが楽です)を指定してます。■取り込むデータの準備XML形式での準備としては、Excelでデータを作成すると思うのですが、実はExcelファイルから、XMLファイルを直接出力するのは面倒です。Excelの開発ツールを使う必要があります。Excelの開発ツールで、出力するXMLの項目などを定義するのですが、これが結構手間がかかります。開発に慣れていない方には、ハードルが高いかも・・・お薦めは、Accessの利用です。Accessない場合は、Excelで頑張りましょう。Accessだと、事前の定義なしで、XMLファイルを出力できます。大まかなイメージとしては、1.Accessから、データが入っているExcelシートを読み込むか、またはリンクしてます2.そして、対象のシートをXML形式で保存します。3.事前にExcel側で、Excelの列名をテーブルの列名に合わせておきます。具体的にみると、1.AccessにExcelデータと取り込むAccessを起動して、「外部データ」から「Excel」をクリック。あとは、画面に合わせて、対象のExcelのシートを選びます(リンクでも、インポートでもどちらでもOK)2.XMLファイルの出力Excelを取り込むと、テーブルに表示されます。右クリックで、「エクスポート」から「XMLファイル」を選択します。エクスポートの情報は、「データ」と「スキーマ」で良いでしょう。出力されたXMLって、どうなっているの?出力したファイルをテキストエディタなどで開くと、以下のようになっています。Excelの1行が、タグ付されます。以下の例では、<レイド>と</レイド>で囲まれた部分が、1行分です。上の例で、「レコードの区切りの指定:<table_chumon> 」にあたります。Excelのシート名がここに変換されます。Excelの列名が、<>で囲まれたところに変換されています。Mysqlにロードするときに、ここがMySQLの列名の指定になります。つまり、Excelの方で、列名をMySQLの列名に合わせておけば、出力されたXMLにその列名で生成されます。Excelの1つのセルは、この<列名>と</列名>で囲まれた形式に変換されます。セル中に改行や、区切り文字が入っていても、そのままこの<列名>と</列名>の間に入ります。改行とかが入っていても、CSVだと壊れたデータになってしまいますが、XMLだとそのまま、MySQLにロードできます。■MySQLコマンドの実行最後に、上で書いたのMySQLのコマンドを実行すれば、終了です。コマンドでエラーになる主なケース:だいたい、次の2つです。・MySQLの列名と、XMLで指定した値と一致していない・行の区切り(例では、<レイド>と</レイド>の部分)がXMLファイルと、MySQLコマンドで指定した値と一致していない■MySQLクライアントMysqlクライアントのインストールが必要なんですが、次のようなキーワードでググれば、すぐに見つかります。MySQL Command Line ClientMySQLクライアント多少、準備が必要ですが、CSVのロードエラーをつぶしていく時間を考えれば、圧倒的に楽だと思います。