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ベストアンサー
- 回答日時:
> "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
- 回答日時:
#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
- 回答日時:
こんばんは。
普通に入らんですかね・・・?
ENCLOSED BY '\"'
この円マークは何ですか?
この回答への補足
PHPからMySQLを操作しようとしています。
\は ’(シングルクォート)をエスケープするために入れました。
エスケープしないとPHP側でエラーを起こすような気がしますが…。
今のままだと、カラムの区切りのカンマと、105,000の5と0の間のカンマが区別されず、インポートはできても正しいデータがテーブルに入りません。
テストのために、csvファイルをExcelなどで開き、105,000→105000としてインポートするとうまくいきました。
ただ、毎回Excelなどでデータを修正して使うのではなく、直接インポートさせるようにしたいと思っています。
No.1
- 回答日時:
一回変数に受けてせっとするとか?
//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だけが該当のフィールドに読み込まれているようです。)
この方法だと、変数に受けようにも、カラムがずれた状態で受けてしまうのではないかと思うのですが、間違ってますでしょうか?
実際はフィールド数もかなりあって、検証するだけでも大変でして…。
さらなるアドバイスがあれば、お知恵をお貸し下さい。
やはり、csv自体を先に読み込んで加工するような処理をした方がよいのでしょうか。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) CSVファイルがカンマ区切りにならない。対処法を教えていただきたいです。 仕事でSMS一斉送信ができ 2 2022/07/01 21:24
- Java javaのCSVデータ読込についてです 6 2022/07/02 10:58
- Visual Basic(VBA) VBAで出力したCSVファイルの先頭にカンマを挿入したい 5 2022/10/14 12:20
- システム メールのcsv添付ファイルの種類を テキスト形式からカンマ区切り形式に 変更する方法はありますか? 4 2023/03/09 20:33
- システム CSVファイルのマッピング処理の省力化 1 2022/11/24 00:01
- XML PHP.laravelについて 1 2023/07/06 15:26
- その他(プログラミング・Web制作) データ解析ソフトRでのファイル入力read.csvがエラーになります 7 2022/03/27 22:11
- Visual Basic(VBA) 【VBA】特定の文字で改行(次の行)に行きたい。 3 2022/04/11 17:20
- Access(アクセス) access,vbaでフォルダ内のファイルをテーブルにインポート、ファイル名もフィールドに追加したい 1 2022/08/31 11:11
- Outlook(アウトルック) 【 Windows 10 】アドレス帳に正しくインポートを完了させたい。 3 2023/04/23 13:41
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
日本語が文字化けしないよう読...
-
PHP 読み込んだファイルのブラ...
-
PHPでFilename cannot be empty...
-
こちらはただの直列処理ですか?
-
フォントの色を変えるには?
-
onedrive にexcelファイルをア...
-
form actionで二つ送信先を指定...
-
シェルスクリプトからphpファイ...
-
FTPコマンドでディレクトリごと...
-
PHPのif文でその処理を途中で抜...
-
存在しないメールアドレスかど...
-
.htaccessにphp_valueが使用できな
-
PHP8を使うと、大量のWarningが...
-
PHPでfatal errorが出ても無視...
-
アップロード画像数でCSSを分け...
-
Apacheでhttp://localhost/が表...
-
別ファイルの変数を呼び出した...
-
FORMで送信ボタンと戻るボタン...
-
sqlで日付が一番古いデータの月...
-
php5のrename()は日本語をリネ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
値の取り出し方について教えて...
-
2つの画像ファイルが異なるファ...
-
PHPでFilename cannot be empty...
-
ファイル名は、数字の「0」(...
-
stat failed
-
ファイルの行数取得
-
TSVファイルをCSV形式に変換したい
-
日本語のファイルが開けない
-
XMLのロードに失敗する
-
別のサーバにあるファイルの存...
-
PHP+MySQL でのcsvファイルイン...
-
日本語のデータが文字化けをし...
-
日本語が文字化けしないよう読...
-
PHPでの別のサーバーのテキスト...
-
phpメールフォームから送信・登...
-
PHP、ファイルロックの失敗で消...
-
PHP5のsimplexml_load_fileで取...
-
PHPでAPI 出力はCSVを取得する...
-
PHPでテキストファイルかどうか...
-
PHPで特定の文字列のみ抽出し、...
おすすめ情報