MySQLへCSVデータ取りこみについて - PHPプロ！Q&A掲示板

2571

  • 0P

MySQLへCSVデータ取りこみについて

質問日時 / 2010年2月28日 11:30 (最終編集：2月28日 15:10)    回答数 / 12件

Questioner:  vodka  このエントリーをはてなブックマークに追加 

キーワード / mysql    csv    null   

MySQLにCSVファイルをTXTファイルに変換し取り込もうとしています。
MySQLのテーブルはカラム数は5個ですがCSVファイルにはカラム数が5個のものもあれば4個のものもあります。

CSVファイル内容
090201,1,たろう,男,12
090202,2,じろう,男,
090203,3,まさこ,女,14

MySQL実行文
load data infile "test.txt" into table resudata fields terminated by "," lines terminated by "/r/n";

エラー内容
ERROR 1261 (01000):Row 1 doesn't contain data for all columns

調べたところデータが無い項目があるためエラーが表示されるみたいですが、
数値型カラムは項目が無い部分はMySQLのテーブルに値はNULLとして取り込みたいのですが何か方法は無いでしょうか？
分かる方教えてください。

この質問への意見の募集は締め切られ、ポイントは既に配分されました。
意見を投稿することはできますが、ポイントを受け取ることはできません。



ツリー一覧

QMySQLへCSVデータ取りこみについてvodka
┗A01shimix>何か方法は無いでしょうか？ (1)基本的にやってお
　┗A01-1vodka回答ありがとうございます。 今回の質問はMySQLをコ
　　┣A01-1-1shimixこの意見は投稿者によって削除されました。
　　┗A01-1-2shimixあぁ、それは「データがない」とは表現しませんね（汗
　　　┗A01-1-2-1vodkaコメントありがとうございます。 2008120606050110
　　　　┗A01-1-2-1-1shimix前のレスで「現象が確認出来る【最低限の】列定義（cr
　　　　　┗A01-1-2-1-1-1vodka返事ありがとうございます。 shimixさんの分を見て
　　　　　　┗A01-1-2-1-1-1-1shimix＃すでにphpとは無関係の話題ですが（汗 前のレス
　　　　　　　┗A01-1-2-1-1-1-1-1vodka>>前のレスで書きましたが、再度お尋ねします。「vodk
　　　　　　　　┗A01-1-2-1-1-1-1-1-1shimix>最初のテーブルresudataは上手く取り込めました、あ
　　　　　　　　　┗A01-1-2-1-1-1-1-1-1-1vodka回答ありがとうございます。 shimixさんには本当に感
　　　　　　　　　　┗A01-1-2-1-1-1-1-1-1-1-1magicflute21.umaをDELETE 2.uma に1データ分INSERT 3.umaをcsv
　　　　　　　　　　　┗A01-1-2-1-1-1-1-1-1-1-1-1vodka皆さんアドバイスありがとうございます。 自分でいろ

回答一覧

並び替え：

A01
answerershimix [3月1日 09:43]

>何か方法は無いでしょうか？

(1)基本的にやっておくべきこと

ファイルをアップロードする前に（作成時点で）キチンとしたフォーマットに合わせる


(2)(1)が実行されていたとしても、やっておくべきこと

アップロードされたファイルをMySQLにload dataする前に、フォーマットや内容の適・不適をチェックする。


その上で、どうしても今回のようなデータが避けられないとしたら、アップロードされたファイルを直接load dataせずに、フォーマットを合わせた別ファイルを作成して、そちらをload daraすることになります。fgetcsvで読んで配列数が5未満だったら、形式に合わせた初期値を付加すればいいと思います（配列の数が1で最初の項目がnullだったら空行なのでスキップ）。

この意見に回答する

ツリーへ　TOPへ

A01-1
replyervodka [3月1日 12:19]

回答ありがとうございます。
今回の質問はMySQLをコマンドプロンプトから実行してデータを取り込んでいるんですが、数値項目がNULLというのはチェックのミスではなく、項目にデータがない場合もあります。

例えば陸上競技のデータとかで
いちろう,12.1,12.2,13.0
じろう,12.2,,12.1
さぶろう12,2,13.0,

上記の内容は『名前,1回目タイム,2回目タイム,3回目タイム』です。
記録がないところはデータはNULLとなっています。

上記のようなデータをコマンドプロンプトよりDATA LOADを使うと数値であるタイムの部分はNULLだとエラーが表示されます。

NULLの場所はNULLとしてデータを取り込みたいのですがどうすればよいでしょうか？

この意見に回答する

ツリーへ　TOPへ

A01-1-1
replyershimix [3月1日 12:34]

この意見は投稿者によって削除されました。

ツリーへ　TOPへ

A01-1-2
replyershimix [3月1日 13:49] (最終編集：3月1日 14:10)

あぁ、それは「データがない」とは表現しませんね（汗

＃最後にカンマがあるので、列自体は全部5ということになります。

MySQL5.1.34+phpMyAdmin3.2.2.1でload dataを試した限りでは、最後の｛数値｜カンマ｝が存在しなくてもエラーにはなりませんね。まさか列が「NOT NULL」ということはありませんよね（というか途中の「2回目」はNULLでもOKのようですしね）。

現象が確認出来る最低限の列定義（create tableのSQL）とCSVデータを提示していただくといいかもしれません。

この意見に回答する

ツリーへ　TOPへ

A01-1-2-1
replyervodka [3月1日 20:56]

コメントありがとうございます。

2008120606050110,古馬,Ｇ２,芝,3600,良,35.6,35.3,35.1,13,11.5,13.7,13.2,12.6,12.7,12.1,12.8,13.2,14.1,13.7,13.5,12.4,11.9,12.1,11.9,11.4,12.3
2008120606050111,3歳,Ｇ３,芝,1600,良,35.2,35.2,34.4,12.6,11.3,12.4,12.1,11.2,11.1,11.4,12.7

上記がCSVデータです。

Field    type
code     char(20)
nenrei   char(20)
kurasu   char(20)
td       char(6)
kyori    int(11)
baba     char(6)
resu3F   float
kat3F    float
sai3F    float
rappu1   float
rappu2   float
rappu3   float
rappu4   float
rappu5   float
rappu6   float
rappu7   float
rappu8   float
rappu9   float
rappu10   float
rappu11   float
rappu12   float
rappu13   float
rappu14   float
rappu15   float
rappu16   float
rappu17   float
rappu18   float

全項目NullはYeSでDefaultはNULLです。
返事お待ちしています。

この意見に回答する

ツリーへ　TOPへ

A01-1-2-1-1
replyershimix [3月2日 09:57] (最終編集：3月2日 10:59)

前のレスで「現象が確認出来る【最低限の】列定義（create tableのSQL）とCSVデータ」と書いたと思うのですが（汗

こちらで下記内容で確認しました。


（テーブル作成）
  1. CREATE TABLE `laps` (
  2.    `name` VARCHAR( 30 ) NOT NULL ,
  3.    `age` INT NOT NULL ,
  4.    `lap1` FLOAT NULL DEFAULT NULL ,
  5.    `lap2` FLOAT NULL DEFAULT NULL ,
  6.    `lap3` FLOAT NULL DEFAULT NULL
  7. ) ENGINE = MYISAM ;

（データファイル）
  1. name_1,3,23.4,23.5,23.6
  2. name_2,4
  3. name_3,5,,33.3
  4. name_4,3,,,20.1


コマンドラインのmysqlから

  1. 　load data infile 'test.txt' into table laps fields terminated by ',';
　　　　（enclosed byやescaped by、lines terminated byは省略）


環境はWinXP+Mysql5.1.41（xampp1.7.3）です。前のレスのレンタルサーバ+phpMyAdminでも問題ありませんでした。

vodkaさんの環境はどのようなものでしょうか？



（追記）
念のために、例示されたテーブルでも試してみました。問題ありません。

（テーブル作成）
  1. CREATE TABLE `mysql`.`laps` (
  2.    code     char(20) null,
  3.    nenrei   char(20) null,
  4.    kurasu   char(20) null,
  5.    td       char(6) null,
  6.    kyori    int(11) null,
  7.    baba     char(6) null,
  8.    resu3F   float null default null,
  9.    kat3F    float null default null,
  10.    sai3F    float null default null,
  11.    rappu1   float null default null,
  12.    rappu2   float null default null,
  13.    rappu3   float null default null,
  14.    rappu4   float null default null,
  15.    rappu5   float null default null,
  16.    rappu6   float null default null,
  17.    rappu7   float null default null,
  18.    rappu8   float null default null,
  19.    rappu9   float null default null,
  20.    rappu10   float null default null,
  21.    rappu11   float null default null,
  22.    rappu12   float null default null,
  23.    rappu13   float null default null,
  24.    rappu14   float null default null,
  25.    rappu15   float null default null,
  26.    rappu16   float null default null,
  27.    rappu17   float null default null,
  28.    rappu18   float null default null
  29. ) ENGINE = MYISAM ;

（データ）
  1. 2008120606050110,古馬,Ｇ２,芝,3600,良,35.6,35.3,35.1,13,11.5,13.7,13.2,12.6,12.7,12.1,12.8,13.2,14.1,13.7,13.5,12.4,11.9,12.1,11.9,11.4,12.3
  2. 2008120606050111,3歳,Ｇ３,芝,1600,良,35.2,35.2,34.4,12.6,11.3,12.4,12.1,11.2,11.1,11.4,12.7

この意見に回答する

ツリーへ　TOPへ

A01-1-2-1-1-1
replyervodka [3月2日 21:57]

返事ありがとうございます。

shimixさんの分を見て自分と違ったのは

各カラムの後に 「null」または「null default null」が着いていたのと
最後にENGINE = MYISAM があり、2点自分と違いました。
少し調べてみたんですがENGINE = MYISAMがあるとCSVがNULLでも大丈夫なんですか？

しかし今度は別のテーブルを作って同じように命令を入力したんですが

create table uma(nama char(20) null,1mae3F float null default null,1maeco
de char(16) null,2mae3F float null default null,2maecode char(16) null,3mae3F fl
oat null default null,3maecode char(16) null,4mae3F float null default null,4mae
code char(16) null,5mae3F float null default null,5maecode char(16) null,date da
te null) ENGINE = MYISAM;

と入力し
ウオッカ,33.5,2008120606050110,34.5,2008120606050111,,,,,,,2010-01-01,
上記のデータを取り込もうとすると
ERROR 1261 (01000): Row 1 doesn't contain data for all columns
とエラーが表示されます。教えていただいたのと同じように文字のときは「null」数値のときは「null default null」そして最後にENGINE = MYISAMを記入したんですがなぜでしょうか？
何度も質問すいませんが教えてください。

この意見に回答する

ツリーへ　TOPへ

A01-1-2-1-1-1-1
replyershimix [3月3日 00:06] (最終編集：3月3日 00:09)

＃すでにphpとは無関係の話題ですが（汗

前のレスで書きましたが、再度お尋ねします。「vodkaさんの環境はどのようなものでしょうか？」せめてOSとMySQLのバージョンくらいは書いてください。


>しかし今度は別のテーブルを作って同じように命令を入力したんですが

最初のテーブルでうまく実行できていないのに、このテーブルとデータを使って試す意図がわかりません。まずは私が前のレスで試した程度の列数の少ないテーブル（とCSVデータ）で試してみてください。そのために「この内容で検証しました」というレスを付けています。それとも、その（改行もなしに提示された）テーブルとデータについても私に検証しろということでしょうか？



ちなみに

>全項目NullはYeSでDefaultはNULLです。

と書かれていたので、

>shimixさんの分を見て自分と違ったのは
>
>各カラムの後に 「null」または「null default null」が着いていたのと

は意外でした。どうやって「NullはYes」で「defaultはnull」だと指定したんでしょう？

この意見に回答する

ツリーへ　TOPへ

A01-1-2-1-1-1-1-1
replyervodka [3月3日 12:24]

>>前のレスで書きましたが、再度お尋ねします。「vodkaさんの環境はどのようなものでしょうか？」せめてOSとMySQLのバージョンくらいは書いてください。

>>それとも、その（改行もなしに提示された）テーブルとデータについても私に検証しろということでしょうか？

質問の丸投げ及び記述不足ですみません。

環境はWindowsXPでMTSQLのバージョンは5です。
（コマンドプロンプトで最初に「net strat mysql5」で開始します）

最初のテーブルresudataは上手く取り込めました、ありがとうございます。

次に別のテーブルにデータを取り込もうと思ったのでテーブルumaの方を書かせていただきました。

>>どうやって「NullはYes」で「defaultはnull」だと指定したんでしょう？ 
についてはテーブルを作成後「show fields from resudata」を入力するとNULLにはYES、DEFAULTにはNULLとなっていたので指定できていると自分なりに解釈しました。

この意見に回答する

ツリーへ　TOPへ

A01-1-2-1-1-1-1-1-1
replyershimix [3月3日 13:18] (最終編集：3月3日 13:27)

>最初のテーブルresudataは上手く取り込めました、ありがとうございます。

あ、そうなんですね。全然気付きませんでした。

＃というか初耳ですかね。記述不足にもほどがあります（汗


>次に別のテーブルにデータを取り込もうと思ったのでテーブルumaの方を書かせていただきました。

とりあえず手元のWindowsXP+Mysql5.1.41で試しました。

（テーブル作成）
  1. create table uma(
  2.     nama char(20) null,
  3.     1mae3F float null default null,
  4.     1maecode char(16) null,
  5.     2mae3F float null default null,
  6.     2maecode char(16) null,
  7.     3mae3F float null default null,
  8.     3maecode char(16) null,
  9.     4mae3F float null default null,
  10.     4maecode char(16) null,
  11.     5mae3F float null default null,
  12.     5maecode char(16) null,
  13.     date date null
  14. ) ENGINE = MYISAM;

（テキストデータ）
  1. ウオッカ,33.5,2008120606050110,34.5,2008120606050111,,,,,,,2010-01-01,

（読み込み）
  1. load data infile 'test.txt' into table uma fields terminated by ',';

で問題なく動作しています。Warningは出ますがErrorになる要素は思いつきません・・。

この意見に回答する

ツリーへ　TOPへ

A01-1-2-1-1-1-1-1-1-1
replyervodka [3月3日 23:01]

回答ありがとうございます。
shimixさんには本当に感謝しています。
記述不足で本当にすみません。

コメントを参考に

  1. create table uma(
  2. name char(20) null,
  3. 1mae3F float null default null,
  4. 1maecode char(16) null,
  5. 2mae3F float null default null,
  6. 2maecode char(16) null,
  7. 3mae3F float null default null,
  8. 3maecode char(16) null,
  9. 4mae3F float null default null,
  10. 4maecode char(16) null,
  11. 5mae3F float null default null,
  12. 5maecode char(16) null,
  13. date date null) ENGINE = MYISAM;

上記を実行し
CSVデータ

  1. ウオッカ,33.5,2008120606050110,34.5,2008120606050111,,,,,,,2010-01-01

を取り込むために

  1. load data infile "uma.txt" into table uma fields 
  2. terminated by "," lines terminated by "\r\n";
を実行したのですが、

  1. ERROR 1265 (01000): Data truncated for column '3mae3F' at row 1

とエラーが表示されてしまいます。
調べると3mae3Fの値がNULLだからだと思うんですが、CSVにデータがない場合NULLで取り込みたいのですが・・・
アドバイスお願いします。

この意見に回答する

ツリーへ　TOPへ

A01-1-2-1-1-1-1-1-1-1-1
replyermagicflute2 [3月4日 07:58]

1.umaをDELETE
2.uma に1データ分INSERT
3.umaをcsvに出力
4.出力されたcsvをみてみる
5.入力するcsvを編集
6.umaをDELETE
7.csvをumaに入力
8.データコンバート用のスクリプトをPHPで作る

この意見に回答する

ツリーへ　TOPへ

A01-1-2-1-1-1-1-1-1-1-1-1
replyervodka [3月6日 11:56]

皆さんアドバイスありがとうございます。
自分でいろいろ試してみました。

結果は
 ENGINE = MYISAM;
をテーブル作成時の命令に追加することによってCSVの項目がNULLでも正しく取り込めるようになりました。

しかしCSVの一行目にはすべての項目がセットされていないとload dataしてもエラーが表示されるので、一行目にダミーとして全項目に適当な値をセットしたものを取り込んでいます。

もし上記の方法意外でもCSVのNULLを取り込める方法があれば教えてください。

この意見に回答する

ツリーへ　TOPへ

<<質問一覧へ



Pick Up Q&A

Q
動的なURLを静的に見せる方法
 このエントリーをはてなブックマークに追加 
A
普通に考えて、mod_rewrite でしょうね。 http://www.nishishi.com/blog/2006/01/mod_rewrite_url.html...

>>続きを読む

GETのままでは検索エンジンのロボットが拾ってくれなかったためにSEO対策として有効だと言われていますね。

▲解説者：岡本（アシアル株式会社 教育コーディネーター兼　システムエンジニア）

Q&A掲示板 新着情報