du_1993 2017-08-02 23:35
浏览 388
已采纳

PHP:从CSV导入数据到数据库时删除�等特殊字符[复制]

This question already has an answer here:

I created a PHP script that allows me to upload a huge file of data from csv file. While importing, I'd like to replace the special character like to a letter c. Below is my code:

        $sql ="INSERT INTO bill_of_materials(allotment_code, category_name, activity, quantity, end_unit_quantity, unit, description,
        unit_cost, regular_labor_cost, end_unit_labor_cost, type, batch) VALUES";

        while (($line = fgets($handle)) !== false) {

          $sql .= "('".implode("', '", explode(";", sanitize($line)))."'),";
          $counter++;
        }

            $sql = substr($sql, 0, strlen($sql) - 1);
             if (mysqli_query($new_conn, $sql) === TRUE) {

                echo 1;

                //database file name
                $new_database_file = $new_database.'.sql';

                if(file_exists('backup/'.$new_database_file)) {

                    unlink('backup/'.$new_database_file);

                    // backup main database

                    $command = "C:/xampp/mysql/bin/mysqldump --host=$host --user=$user --password=$pass $database_name > backup/$new_database_file";
                    system($command);

                } else {
                    // backup main database

                    $command = "C:/xampp/mysql/bin/mysqldump --host=$host --user=$user --password=$pass $database_name > backup/$new_database_file";
                    system($command);
                }
            } else {
                echo $sql;
            }

In addition, I have a data from my CSV that is W2-A1 2/F Front Fa�ade - B and I'd like to see an output like W2-A1 2/F Front Facade - B. How can i do this?

</div>

展开全部

  • 写回答

1条回答 默认 最新

  • doufan3408 2017-08-02 23:53
    关注

    First of all, make sure you are using correct database client charset collation. If database charset/collation is correct, you may use preg_replace to sanitize dirty characters like so:

    function sanitize($line){
       $clean = iconv('UTF-8', 'ASCII//TRANSLIT', $line); // attempt to translate similar characters
       $clean = preg_replace('/[^\w]/', '', $clean); // drop anything but ASCII
       return $clean;
    }
    

    If that won't help (e.g. you have truly corrupted binary stream - for example saving into CSV from old Excel source file) you may wont to use binary translated characters (first you must find out corrupted binary sequence e.g. by dumping it via chr(ord($line[$position]))) - example:

    function sanitize($line){
        $map = [
            // corrupted chars sequence -> fixed chars
            "\xC3\xA8" => 'č',
            "\xC3\x88" => 'Č',
            "\xC3\xB9" => 'ů',
            "\xC3\x99" => 'Ů',
            "\xC3\xAC" => 'ě',
            "\xC3\x8C" => 'Ě',
            "\xC3\xB8" => 'ř',
            "\xC3\x98" => 'Ř',
            "\x53\xC2\x8D" => 'Š',
            "\xC2\xA9" => 'Š',
        ];
        return str_replace(array_keys($map), $map, $line);
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 雄安新区高光谱数据集的下载网址打不开
  • ¥66 android运行时native和graphics内存详细信息获取
  • ¥100 求一个c#通过CH341读取数据的Demo,能够读取指定地址值的功能
  • ¥15 rk3566 Android11 USB摄像头 微信
  • ¥15 torch框架下的强化学习DQN训练奖励值浮动过低,希望指导如何调整
  • ¥35 西门子博图v16安装密钥提示CryptAcquireContext MS_DEF_PROV Error of containger opening
  • ¥15 mes系统扫码追溯功能
  • ¥40 selenium访问信用中国
  • ¥20 在搭建fabric网络过程中遇到“无法使用新的生命周期”的报错
  • ¥15 Python中关于代码运行报错的问题
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部