CSVからの登録で数値の先頭に0が付いてしまう

Question

お世話になっております。初心者のような質問で恐縮ですが、表題のように、CSVからの登録で数値の先頭に0が付いてしまう現象が起きております。

CSVに文字列、数値など色々なパターンの項目があり、それを一括して（PHPで）DB内に登録するスクリプトを用意したのですが、ある数値のカラムを確認すると、1 が 01、2 が 02 と、先頭に0 が付いた状態で登録されてしまう現象が起きています。（文字列のカラムも数値のカラムも、varcharとして設定）

かといって、数値はどれも0が付くのか？と言えば、問題ないカラムもあれば、数値は全て0が付いてしまうカラムもあるような状況です。

最初は気付かずにいたのですが、selectする際、where文にて数値を条件に振り分けようとしたら5が05となっており振り分けることが出来ずに発覚した次第です。

以上、初めての現象で、調べても同じような不具合の記事等が見つからず質問として投稿させて頂きましたが、何が原因として考えられるのでしょうか？

お忙しいなか恐縮ですがアドバイス頂ければ幸いです。
宜しくお願い致します。

Answer 1

もしかしてCSVをExcelで見ていませんか？
CSV上で実際は'01'、'02'が設定されているけどExcelで表示しているために0が省略されているように見えているとか。

Comments

[chapp]

sho_csさん

お忙しい中ご親切なコメントをありがとうございます。

今、ご指摘いただき、あ！と思い、テキストファイルとしてファイルを開いたところ、元のデータは01、02、03といったデータが格納されていました！
ココですね・・・

Answer 2

CSVを読み込んでトリムしてからSQL成形をするスクリプトです。

<?php
    function formatting_to_sql($v) {
        //空白文字を除去
        $v = trim($v);
        
        if( preg_match('/[^\d]/',$v) ){
            //数字以外の文字を含む場合はテキストとして判別して文字コードを変えて返す
            return mb_convert_encoding($v, "UTF-8", "SJIS");
        }
        if( preg_match('/^0+$/',$v) ){
            //テキストが全部0だったら0にして返す
            return 0;
        } else {
            //テキストが数字で0以外の数字があったら先頭の0を省いて返す
            return intval(preg_replace('/^0*([1-9][0-9]*)$/','$1',$v,1));
        }
    }
    
    //CSVを読み込む
    $fp = fopen('sample.csv','r');
    
    //一行ずつCSVからデータを取り出しつつループ
    $i = 0;
    while( $line = fgetcsv($fp) ){
        //ヘッダ行からセル名を記憶する
        if( $i == 0){
            $cell_sql = array();
            foreach( $line as $v ){
                $cell_sql[] = sprintf("%s",$v);
            }
        } else {
            //一行ずつ取り出したCSVの各セルをトリム関数を使ってSQL用に成形
            $line_sql = array();
            foreach( $line as $v){
                $line_sql[] = sprintf("'%s'",formatting_to_sql($v));
            }
            //まとめたセルをはき出してSQLを成形
            $sql = sprintf(
                    'INSERT INTO table (%s) values (%s);'
                    ,mysql_real_escape_string(implode(',',$cell_sql))
                    ,mysql_real_escape_string(implode(',',$line_sql))
                );
            
            //SQL実行
            //...
        }
        //ループカウンタをインクリメント
        $i++;
    }
    
    //ファイルを閉じる
    fclose($fp);

Comments

[chapp]

hermitagejpさん

こんばんは。
当方の環境下においても動作確認させて頂きました。

シンプルなソースで全体を見渡し易く、大変勉強になります。
お忙しい中ご親切な対応をありがとうございました！

[chapp]

hermitagejpさん

お世話になっております。
ご負担を掛けるようで恐縮なのですが、今一度CSVデータをinsertする手順を質問させて下さい。

ご教示頂いたスクリプトを是非活用したいと考えているのですが、CSVデータは約200ほどのカラムであるのに対し、MySQL側のテーブルは250ほどのカラムの構成となっています。

これも、複数のCSVデータを1つのテーブルで管理することが理由で、このような構成となった訳ですが、

foreach( $line as $k => $v){
$line_sql[] = sprintf("'%s'",trim_zero($v));
}
//まとめたセルをはき出してSQLを成形
$sql = sprintf('INSERT INTO table values (%s);',implode(',',$line_sql));

この一連の流れでは、CSVにある１レコードを配列にし、SQL文に生成された順はインデックス順になっているかと思います。

そこで質問なのですが、現在、各カラムに格納するのはインデックス順となっていますが、順序をしているつような場合はどのようなスクリプトになるのでしょうか？

お恥ずかしことかも知れませんが、現在はこれに対応するため、配列を分解し
INSERT INTO table values(0, '$data[0]', '$data[2]', '$data[3]', '$data[1]', ...............
のような形で行ってきました。

出来ればスマートに簡素化したいとは思っているのですが、やはり難しいでしょうか？

お忙しいなか恐縮ですが、引続きアドバイスなど頂戴出来れば幸いです。
ご負担お掛けし申し訳御座いません。

[chapp]

それと、すみません、CSVを混みこんだ後、whileで廻したとき文字コードをエンコードしていたのですが、これら処理は無くても問題ないのでしょうか？
以下が実際に設置している一部ですが、無駄なものは極力排除した方が良いかな？と考えての質問です。
何なら何まですみません。

while(!feof($fp)){
$csv = fgets($fp);
$csv = trim($csv, "\n");
$csv = trim($csv, '\"');
$csv = mb_convert_encoding($csv, "UTF-8", "shift_jis");
$csv = str_replace('"', '', $csv);
$csv_array = explode(",", $csv);

[chokojori]

sprintfでクエリを作るべきではありません。プレースホルダを使いましょう。

[hermitagejp]

>sprintfでクエリを作るべきではありません。プレースホルダを使いましょう。
ご指摘はもっともなので、正式にリリースする際にはご利用になっているライブラリやフレームワークがサポートしているクエリビルダ等をご使用になるのが安全です。
一応、エスケープ処理も組み込んでおきました。

>そこで質問なのですが、現在、各カラムに格納するのはインデックス順となっていますが、順序をしているつような場合はどのようなスクリプトになるのでしょうか？

カラム名を指定してINSERTしたいということでしょうか。
CSVのヘッダ行をセル名とみなしてSQLを組成するように変えてみました。

＞文字コードをエンコードしていたのですが、これら処理は無くても問題ないのでしょうか？
文字コードを変えて保存したいならmb_convert_encodingはいります。
mb_convert_encodingをいれずにINSERTすると、CSVの文字コードでDBに登録されます。

[chapp]

おはようございます。

お忙しいなか早速の投稿ありがとうございます！

今は試せる環境ではないので、後ほど確認させて頂きます。

取り急ぎお礼まで・・・ありがとうございました！

[hermitagejp]

念のため補足しますと、処理の確実性やセキュリティ面などいくつかの観点からfgetcsv、mysql_real_escape_string等の関数は推奨されません。

fgetcsvの代替策については下記が役に立つと思いますのでご参照下さい。
http://yossy.iimp.jp/wp/?p=56

また、mysql_real_escape_stringの代替策についてはお使いになるライブラリやフレームワークのクエリビルダ、モデルのドキュメントをご参照ください。

[chapp]

hermitagejpさん

こんばんは。お世話になっております。
ご教示頂いたソース、実行させて頂きました！

こちらの意図したいことの理解だけでなく、すぐに実行できるようソースまで用意して頂き、感謝いたします。

ご紹介頂いたfgetcsvの代替策を拝見しながら、改め、実装に向けアレンジさせて頂きます。

この度はお忙しい中のご対応、ありがとうございました！

[chapp]

chokojoriさんの貴重なご意見にも感謝いたします。
ありがとうございました！

[chapp]

hermitagejpさん

こんにちは。お世話になっております。
たびたび申し訳ありません。

SQL文を生成する箇所について質問させて下さい。

この度の質問ではINSERT文を生成したいという内容で、貴重な回答を頂いた訳ですが、UPDATEの場合はどのようなソースとなるのでしょうか？

というのも、CSVをアップロードする際、同じID（CSVの1列目のデータがIDとして一意なデータとなっている）があった場合、INSERTではなく、IDをWHERE文となる条件として、UPDATEしたいのです。

なので、

,mysql_real_escape_string(implode(',',$cell_sql))
,mysql_real_escape_string(implode(',',$line_sql))

同一IDが登録が確認出来なかったら上記スクリプトを実行。
IDの存在が確認出来たらUPDATEを実行としたいのですが、UPDATE文の生成が分かりません。

折角シンプルなソースをご提示いただいたので、これを基本として進めたいと思っていますが、応用力がなく立ち止まってしまいました。

お忙しいなか恐縮ですが、UPDATE文の生成に関し、改めご指導頂けないでしょうか？

何度も申し訳ありません。
宜しくお願い申し上げます。

[chapp]

hermitagejpさん

こんにちは。お世話になっております。
たびたび申し訳ありません。

ご教示頂いたソースを元に作業を進めているのですが、元のCSVデータに12桁の数値があり、一意な数値のはずが全て同じ番号になってしまいます。

foreach( $line as $v){
$line_sql[] = sprintf("'%s'",formatting_to_sql($v));
}

この時点では既に数値が変わっている事が分かったのですが、これ以上原因を突き止めることが出来ずに困っています。

ちなみに、100069583454 や 100069583476 といった番号が、全て 2147483647 になってしまいます。

何から何まで申し訳なく思ってます。
何度も申し訳ありませんが宜しくお願い申し上げます。

[chapp]

数値の件、解決しました。
お騒がせして本当申し訳ありません。

[hermitagejp]

下のほうに新しくINSERTとUPDATEを切り分けて生成するコードのサンプルを付けました。
なお、2147483647はINT型の最大値です。

Answer 3

具体的な情報がわからないため、原因を特定して回答することは難しいです。

ただ、CSVはダブルクォートで囲むことで強制的にテキストとして認識させることができますが、エクセルはそれでも数字のみのセルは数字と判別して先頭の0を省いて表示していた記憶があります。
CSVの作り方によっては先頭に0がついてしまうケースもあるかもしれません。

DB内に登録するスクリプトを用意したのですが

PHPMyAdminなどのツールではなく、ご自身でプログラムをご用意されたという認識でよろしいでしょうか。

ご自身でプログラムをご用意されていて、インポート用のプログラムを編集することが可能という前提で、以下に正規表現を用いたトリム用関数の例を挙げます。
①数字以外の文字を含む場合、そのまま返却
②全部数字の0だった場合には0を返却
③全部数字だが、0以外の数字が含まれる場合には先頭の0を削除して返却（複数ついていても一括削除）

これを、CSVの各要素に対して適用してみてください。

<?php
    function trim_zero($v) {
        if( preg_match('/[^\d]/',$v) ){
            //数字以外の文字を含む場合はそのまま返す
            return $v;
        }
        if( preg_match('/^0+$/',$v) ){
            //テキストが全部0だったら0にして返す
            return 0;
        } else {
            //テキストに0以外の数字があったら先頭の0を省いて返す
            return intval(preg_replace('/^0*([1-9][0-9]*)$/','$1',$v,1));
        }
    }
    $v = '0010';
    echo trim_zero($v);

Comments

[chapp]

hermitagejpさん

お忙しい中ご親切なアドバイスをありがとうございます。
ソースコードまで提示していただき感謝です。

>PHPMyAdminなどのツールではなく、ご自身でプログラムをご用意されたという認識でよろしいでしょうか。

はい。仰られるようにこちらで用意していますが、CSVをアップロード時、単にデータを放りこんでいるだけでした。
ご提示いただいたソースを参考に後ほど試させて頂きます。

取り急ぎお礼まで。後ほどご報告させて頂きます。
ありがとうございました。

[chapp]

hermitagejpさん

ご提示いただいたソースを試させて頂きました。
素晴らしいです。文字列を適切に処理してくれるのですね。

と、ここで恥を忍んでの質問ですが、CSVデータをDBに格納する際上記ソースを利用するには、どのような設置となるのでしょうか？

今回のCSVデータ、約200ほどのカラム（500レコード）から成立っており、CSVをアップロードした際、DBへの格納は、知識が乏しく、1レコードずつ配列を分解し、それぞれ該当するカラムに格納しているのですが、これだと1カラムずつ文字列をマッチさせなくてはなりませんよね。

応用力が無くお恥ずかしい限りですが、スマートにCSVデータをDBにinsertするにはどのようなスクリプトとなりますでしょうか？
引続きアドバイスなど頂けたら幸いです。宜しくお願い致します。

[hermitagejp]

コメントだとコードをうまく囲えないみたいなので、下に投稿しました。

Answer 4

UPDATEの場合はどのようなソースとなるのでしょうか？

INSERTとUPDATEをわけるためにPKでレコードが登録されているかどうかを調べるSQLをまずは流します。 ON DUPLICATE KEYを使ってINSERT文一回で済ませる方法もありますが、上記のベーシックなやり方をまずは例として以下に提示します。

コードは前回までに作ったものの修正版です。
主だった修正点は以下のとおり。
１．桁数の多い数字の場合INT型の上限を迎えるので、formatting_to_sqlはINTVALでINT型に直さないで返すようにしました。
２．SELECT文を最初に流してレコードの有無を確認し、INSERTとUPDATEに分かれてSQLを生成します。

<?php
    function formatting_to_sql($v) {
        //空白文字を除去
        $v = trim($v);
        
        if( preg_match('/[^\d]/',$v) ){
            //数字以外の文字を含む場合はテキストとして判別して文字コードを変えて返す
            return mb_convert_encoding($v, "UTF-8", "SJIS");
        }
        if( preg_match('/^0+$/',$v) ){
            //テキストが全部0だったら0にして返す
            return 0;
        } else {
            //テキストが数字で0以外の数字があったら先頭の0を省いて返す
            return preg_replace('/^0*([1-9][0-9]*)$/','$1',$v,1);
        }
    }
    
    //CSVを読み込む
    $fp = fopen('sample.csv','r');
    
    //一行ずつCSVからデータを取り出しつつループ
    $i = 0;
    while( $line = fgetcsv($fp) ){
        //ヘッダ行からセル名を記憶する
        if( $i == 0){
            $cell_sql = array();
            $cell_primary = '';
            $cell_nums = 0;
            foreach( $line as $k => $v ){
                if( $k == 0 ){
                    $cell_primary = formatting_to_sql($v);
                }
                $cell_sql[] = formatting_to_sql($v);
                $cell_nums++;
            }
        } else {
            //一行ずつ取り出したCSVの各セルをトリム関数を使って記憶する
            $line_sql = array();
            $line_primary = '';
            foreach( $line as $k => $v){
                if( $k == 0 ){
                    $line_primary = formatting_to_sql($v);
                }
                $line_sql[] = sprintf("'%s'",formatting_to_sql($v));
            }
            
            //PKで値が登録されているかを調べる参照用SQLを成形
            $sql = sprintf("SELECT count(*) as cnt FROM table WHERE %s = '%s'"
                    ,mysql_real_escape_string($cell_primary)
                    ,mysql_real_escape_string($line_primary)
                );
            
            //SELECT実行
            //...
            
            //挿入・更新用SQLを成形
            if( $cnt == 0 ){
                //INSERTする
                $sql = sprintf(
                        "INSERT INTO table (%s) values (%s);"
                        ,mysql_real_escape_string(implode(',',$cell_sql))
                        ,mysql_real_escape_string(implode(',',$line_sql))
                    );
            } else {
                //UPDATEする
                $sql_values = array();
                for($i=0; $i<$cell_nums; $i++){
                    $sql_values[] = sprintf("%s = '%s'", $cell_sql[$i], $line_sql[$i]);
                }
                $sql = sprintf("UPDATE table SET %s WHERE %s = '%s'"
                        ,mysql_real_escape_string(implode(',',$sql_values))
                        ,mysql_real_escape_string($cell_primary)
                        ,mysql_real_escape_string($line_primary)
                    );
            }
            
            //SQL実行
            //...
        }
        
        //ループカウンタをインクリメント
        $i++;
    }
    
    //ファイルを閉じる
    fclose($fp);

Comments

[chapp]

hermitagejpさん

おはようございます。お休み明けのお忙しい中での対応ありがとうございます。
ソース拝見させて頂きました。
昨夜（明方）、3時過ぎに何とか正常にUPDATE出来るまでに至りましたが、hermitagejpさんのシンプルなソースを拝見し、自身の勉強不足を改め思い知った気がします。

後ほどこのソースを元に試させて頂きます。
取り急ぎお礼まで・・・

本当にありがとうございます！

[chapp]

hermitagejpさん

こんばんは。ご無沙汰しています。

「数値の先頭に０がついてしまう」ということでご親切な回答をしていただきました。

とてもありがたい回答ととして感謝しているところですが、CSVデータに「１-２」といったような文字列があると、１月２日と変換されてしまうのですが、これを交わすにはどうしたらいいのでしょうか？

以前回答いただいてから随分と日にちが経っていますし、自力で解決しようと調べているものの、いずれも解決に至らず、再度質問させていただいた次第です。

もし、この質問に目がとまりましたら、再度アドバイスいただければ幸いです。

お忙しいところすみません。宜しくお願いいたします。

[hermitagejp]

ご無沙汰しております。
風邪でぶっ倒れておりました。

さて、ご質問の件ですが、CSVには「1-2」とあるのはわかりましたが、どこで見ると「1月2日」となるのでしょうか？

その点、補足をお願いします。

[chapp]

hermitagejpさん

こんばんは。ご無沙汰しております。
ご丁寧にすぐにお返事いただいていたのですね！
今日まで気付かずおりました。申し訳ありません！！

追加での質問させていただいた「1-2」が「1月2日」となってしまう件、エクセルで閲覧した際に起きる現象でした。これに気付いたのも質問投稿後。重ねてお詫びいたします。

[hermitagejp]

本件はCSV出力ではなく、CSV入力だったかと思います。
おそらく、登録用データのCSVをエクセルで生成中にその問題が起きているのかと推察します。
でしたら、対象の列を選択⇒右クリック⇒セルの書式設定⇒文字列に変更
これで、1-2と入れたときに自動的に日付に変わることはなくなると思います。

[hermitagejp]

一度作ったCSVを再度編集しなおす場合、手間がかかりますが、データ⇒外部データの取り込み（テキストファイル）からフォーマットを指定して取り込むとその問題を回避できます。

[chapp]

hermitagejpさん

ご親切なアドバイスをありがとうございます。
感謝しています。
また何か御座いましたら宜しくお願い致します。

Answer 5

（文字列のカラムも数値のカラムも、varcharとして設定）

なぜ数値のカラムもvarcharにしているのですか？

あと、MySQLにCSVをインポートするときに使っているツールは何ですか？

Comments

[chapp]

chokojoriさん

お忙しい中ご親切な回答をありがとうございます。

まず、varcharに関してですが、該当データが無い場合、値を空として登録したいのですが、intを設定していると、11という値が勝手に登録されてしまうので、あえてvarcharにしています。（全てnot nullで設定）

ここで改めて質問なのですが、数値の場合、やはりint等を利用すべきなのでしょうか？また、その際、データを空で登録したい場合などは、どういった構成にすべきなのでしょうか？
not nullではなくnullとか？実はこれら経緯も、初めて購入したMySQLに関する初心者本に、明確な定義付けが無かったため、テーブル作成時、NULLを設定するという事はないままに今日まで至っています。

今回の数値をvarcharで・・・というところから外れているかもしれませんが、ご指南頂けると幸いです。

[chokojori]

int型にした上でNULLを許可するのが、その場合のセオリーです。

[chapp]

chokojoriさん

お忙しい中のお返事をありがとうございます。
そうなんですね。これを機に勉強し直してみます。ありがとうございました！