PHP+MySQL でのcsvファイルインポート

質問

PHP + MySQL　で、他システムから出力されてきたcsvファイルをテーブルへインポートしたいと考えています。

LOAD DATA INFILE で取り込もうとしていますが、対象のcsvファイルの特徴のためにうまく行かず苦戦しています。

取り込みたいファイルは、以下のようになっています。
　・各フィールドが　"(ダブルクォート)　で囲まれている
　・各フィールドは　,(カンマ)　で区切られている
　・金額の項目には、桁区切りのカンマが入っている

　例) "0001","あああああ",…(中略)…,"105,000","100,000","5,000",…
　　　　コード　項目名　　　　　　　　　　　　税込み　　税抜き　消費税
　というような形です。

　困っているのは、金額に使われている、桁区切りのカンマの処理についてです。
　目的としては、データベースへ取り込むときには 105,000　は 105000　として取り込めればいいのですが、他システムから、桁区切りがなされた状態でcsvが作成されてきています。

LOAD DATA INFILE test.csv INTO TABLE data_table FIELDS TERMINATED BY ',' ENCLOSED BY '\"' IGNORE 1 LINES;

　各項目のダブルクォートはENCLOSED BY で、項目を分けるカンマはTERMINATED BYで処理できていると思うのですが、金額の区切りに使われているカンマと項目の区切りのカンマを区別させられずに苦戦しています。

　事前にcsvを加工するのも考えてはいるのですが、なるべくcsvを加工せずにそのまま取込したいと思います。
　何か、簡単に回避できる方法等があればご教示願います。
　宜しくお願いします。

回答No.4

webmomo · ベストアンサー

> "0001","あああああ",…(中略)…,"105,000","100,000","5,000"

常にこの書式であるという前提ですが、ファイルを1行ずつ読んで
カラム区切りをタブに変え、項目の整形を行う方法があります。

一度お試しください。

$file_name = "test.csv"; //LOADファイル

//ファイルオープン
$fp = fopen("./$file_name", "r");
$fw = fopen("./$file_name.tmp", "w"); //変換先ファイル

while(!feof($fp)){
$buf = fgets($fp, 2048);
$buf = str_replace("\",\"","\t",$buf); // 「","」を「\t」に置換
$buf = str_replace("\"","",$buf); // 「"」を「」に置換
//mb_convert_variables("UTF-8", "SJIS", $buf); // SJISをUTF-8に変換する場合
fputs($fw, $buf);
}

//ファイルクローズ
fclose($fp);
fclose($fw);

//タブ区切りでLOAD(パラメータは環境に合わせて調整ください)
$sql = "LOAD DATA LOCAL INFILE './$file_name.tmp' REPLACE INTO TABLE data_table FIELDS TERMINATED BY '\t';

$res = mysql_query($sql); // mysql_connect、mysql_closeは省略
if(!$res){
echo "LOAD ERROR!!";
}

$ret = unlink("./$file_name.tmp"); //変換先ファイルの削除

CSVはSJISだと思いますが、PHPの文字コードと異なる場合、
日本語を含んでいると文字化けを起こす場合があります。

念のため、PHPの文字コードに合わせ、mb_convert_variablesで"UTF-8"か"EUC"に
しておくほうがよいかと思います。

お礼

回答ありがとうございます。
どうやらcsv自体を直してしまうほうが早いようですね。

お教え頂いた方法ならば、思っていたよりファイル加工の手間もかからないようですので、今回はこちらの方法でいこうと思います。

試しに上のとおりに実行させて頂いたところ、金額の部分が105,000→105しか格納されませんでしたが、上のソースにさらにカンマを除去するために
$buf = str_replace(",","",$buf);
を加えることでうまくいきました。

'105,000'　という値のままでは、DECIMAL型のフィールドに取り込まれなかったのかな、と理解してます。

何はともあれ、無事に処理ができるようになりました。
勉強になりました。ありがとうございます。

回答No.3

yambejp

#1です

>FIELDS TERMINATED BY ',' のカラム区切りのカンマと誤認されているのが問題だと思っています。

たぶんちがうと思いますよ
単にカンマ付きのデータなので読めるところまでよんで
後は捨てられているだけだと思います。
誤認されていたらカラムが右にずれていくのでわかると思います。
そもそもエンクローズの指定をしてるのだからターミネーターが
誤認されたらCSVとして成り立たないでしょう

>カラムがずれた状態で受けてしまうのではないかと思うのですが、間違ってますでしょうか？

前述したとおり間違ってます。
せめて試してから再質問してほしかったです。

お礼

再度の回答ありがとうございます。
そして申し訳ありません。おっしゃるとおり、試してから再質問すべきでした。
私がENCLOSED やTERMINATEDの意味をちゃんと理解できていないようです。

#1にお礼を投稿した後ではありますが、#1のアドバイスを単純には試して見ましたが、エラーが帰ってきて誤ったデータ更新すらできずにおります。

実際のデータはフィールド数も多く、SQL文も相当長くなってしまって、記述の仕方がまずかったのか、うまく試すことすらできていない状態でして…。

ENCLOSED BY…の後の( )内はテーブルにある全項目を列挙する必要があって、@の変数へ入れるものだけ項目名の変わりに"@変数名"として、SET以降はREPLACEした項目だけでよろしいのでしょうか。

質問時の文で誤った実行結果を出してみると、ご指摘の通り、カラムがずれているというよりは、金額項目のカンマ以降の数字が捨てられているようでした。例の場合、税込みに105が入り、次の税抜きに100が入っているようです。

ちなみに、思いついて質問文にESCAPED BY ','　をつけてみたところ、カンマ以下が0の場合(たとえば、525,000)にだけ525とおかしな結果になるようで、525,123の場合ならば、正しく格納されるようです。

この場合、#1で意図されるような記述を正しく私が書ければ上手く行くのでしょうか。
もう少し試行錯誤してみたいと思います。

回答No.2

taka451213

こんばんは。

普通に入らんですかね・・・？
ENCLOSED BY '\"'
この円マークは何ですか？

補足

PHPからMySQLを操作しようとしています。
\は ’(シングルクォート)をエスケープするために入れました。
エスケープしないとPHP側でエラーを起こすような気がしますが…。

今のままだと、カラムの区切りのカンマと、105,000の5と0の間のカンマが区別されず、インポートはできても正しいデータがテーブルに入りません。

テストのために、csvファイルをExcelなどで開き、105,000→105000としてインポートするとうまくいきました。

ただ、毎回Excelなどでデータを修正して使うのではなく、直接インポートさせるようにしたいと思っています。

回答No.1

yambejp

一回変数に受けてせっとするとか？

//test.txt

"0001","あああああ","105,000","100,000","5,000"

//hogeテーブル
コード,項目名,税込み,税抜き,消費税

LOAD DATA INFILE 'test.txt' INTO TABLE hoge
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(コード,項目名,@TEMP1,@TEMP2,@TEMP3)
SET 税込み=REPLACE(@TEMP1,',',''),税抜き=REPLACE(@TEMP2,',',''),消費税=REPLACE(@TEMP3,',','');

お礼

回答ありがとうございます。

恥ずかしながら、変数に一度受ける方法を初めて知りました。

しかしながら、今回の場合、"105,000円"に使われているカンマが、FIELDS TERMINATED BY ',' のカラム区切りのカンマと誤認されているのが問題だと思っています。
(読み込むと、105だけが該当のフィールドに読み込まれているようです。)

この方法だと、変数に受けようにも、カラムがずれた状態で受けてしまうのではないかと思うのですが、間違ってますでしょうか？
実際はフィールド数もかなりあって、検証するだけでも大変でして…。

さらなるアドバイスがあれば、お知恵をお貸し下さい。

やはり、ｃｓｖ自体を先に読み込んで加工するような処理をした方がよいのでしょうか。