dongzhuonao8429 2015-07-11 21:23
浏览 221
已采纳

将CSV文件导入mysql数据库

I am using php to import csv file to database table. when i choose the file and submit I get rows inserted but only table IDs and incremented file numbers the other columns are empty

the following is the php code:

<html>
<head></head>       
    <form name="import" method="post" enctype="multipart/form-data">
        <input type="file" name="file" /><br />
        <input type="submit" name="submit" value="Submit" />
    </form>
<?php       
    if(isset($_POST["submit"]))
    {
        $file = $_FILES['file']['tmp_name'];
        $handle = fopen($file, "r");
        $c = 0;

    $db = new PDO('mysql:host=localhost;dbname=apdatabase', 'root', '');
    $db->exec("SET CHARACTER SET utf8");

        while(($filesop = fgetcsv($handle, 1000,";")) !== false)
        {               
             $filenumb          = $filesop['filenumb']; 
    $hospitalname          = $filesop['hospitalname']; 
    $department       = $filesop['department']; 
    $unit      = $filesop['unit']; 
    $operdt = $filesop['operdt'];
    $reason_desc       = $filesop['reason_desc']; 
    $notes      = $filesop['notes']; 
    $full_name       = $filesop['full_name']; 
    $dataentrytime      = $filesop['dataentrytime']; 

    $sql = "INSERT INTO can_surg (can_surg_id, filenumb, hospitalname, department, unit, operdt, reason_desc, notes, full_name, dataentrytime) VALUES (NULL, $filenumb,'$hospitalname','$department','$unit','$operdt','$reason_desc','$notes','$full_name', '$dataentrytime')";
            $c = $c + 1;
            $result = $db->query($sql);
        }
        if($sql){
            echo "You database has imported successfully. You have inserted ". $c ." recoreds";
        }else{
            echo "Sorry! There is some problem.";                   
        }    
    }
?>
</html>
  • 写回答

1条回答 默认 最新

  • doukang8949 2015-07-11 21:35
    关注

    Edited Code;

    $db = new PDO('mysql:host=localhost;dbname=apdatabase', 'root', '');
$db->exec("SET CHARACTER SET utf8");
if(isset($_POST["submit"])) {
    $file = $_FILES["file"]["tmp_name"];
    if($_FILES["file"]["size"] > 0){
    $file = fopen($filename, "r");
        while(($filesop = fgetcsv($file, 10000, ",")) !== FALSE){ 
            // You have to set values according to columns, e.g , filenumb is 0 column in your csv file
            $filenumb  = '$filesop[0]'; 
            $hospitalname  = '$filesop[1]'; 
            $department = '$filesop[2]'; 
            $unit = '$filesop[3]'; 
            $operdt = '$filesop[4]';
            $reason_desc = '$filesop[5]'; 
            $notes = '$filesop[6]'; 
            $full_name = '$filesop[7]'; 
            $dataentrytime = '$filesop[8]'; 

            $sql = "INSERT INTO can_surg (can_surg_id, filenumb, hospitalname, department, unit, operdt, reason_desc, notes, full_name, dataentrytime) VALUES (NULL, $filenumb,'$hospitalname','$department','$unit','$operdt','$reason_desc','$notes','$full_name', '$dataentrytime')";
            $result = $db->query($sql);
        }
    }
    fclose($file);
}

    Try the above code now

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题？

悬赏问题

  • ¥15 cgictest.cgi文件无法访问
  • ¥20 删除和修改功能无法调用
  • ¥15 kafka topic 所有分副本数修改
  • ¥15 小程序中fit格式等运动数据文件怎样实现可视化？（包含心率信息））
  • ¥15 如何利用mmdetection3d中的get_flops.py文件计算fcos3d方法的flops？
  • ¥40 串口调试助手打开串口后,keil5的代码就停止了
  • ¥15 电脑最近经常蓝屏，求大家看看哪的问题
  • ¥60 高价有偿求java辅导。工程量较大，价格你定，联系确定辅导后将采纳你的答案。希望能给出完整详细代码，并能解释回答我关于代码的疑问疑问，代码要求如下，联系我会发文档
  • ¥50 C++五子棋AI程序编写
  • ¥30 求安卓设备利用一个typeC接口，同时实现向pc一边投屏一边上传数据的解决方案。