dpb35161 2015-07-16 15:14
浏览 354
已采纳

导入CSV时,在每行末尾添加逗号

I am quite new to PHP and need your help for an issue. I will explain my problem in the following steps:

1- I have a CSV file and I would like to import it to MySQL database with PHP.

2- When I add commas at the end of each lines in the CSV file, I could import it by using IMPORT option in Mysql db. I can not import it without adding commas at the end of lines.

3- I want to add a new line into my PHP code which will add commas at the end of each record/line and will enable the data imported without any error.

Here is my code:

<?php

//read file
$csvfile=file_get_contents("/samba/import/Tbl_HRList.csv");


$lines = explode(PHP_EOL, $csvfile);
$array = array();
foreach ($lines as $line) {
    $field = str_getcsv($line);
    if ($field[0] != ''){

        $sql="INSERT INTO HR_Tbl (Employee_ID, First Name, Prefix, Surname, Location, Organizational Code, Organizational Unit, Team, Team Code, Function, Function code, T24 Department Code, Date in service (GBI), Company email, End Date Contract, End Date Systems, Temp. Leave Date, Temp. Leave End, Temp. Leave Code)
                VALUES
('$field[0]','$field[1]','$field[2]','$field[3]','$field[4]','$field[5]','$field[6]','$field[7]','$field[8]','$field[9]','$field[10]','$field[11]','$field[12]','$field[13]','$field[14]','$field[15]','$field[16]','$field[17]','$field[18])";

    }

    //insert record to database

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully". PHP_EOL;
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}
else
{
continue;
}
}

$conn->close();
?>

Thanks in advance Alex

  • 写回答

1条回答 默认 最新

  • douman9420 2015-07-16 15:19
    关注

    Use Load Data Infile and completely abandon the above idea.

    You can have csv lines terminate with or

    You can have row one of that import file either have or not have column names. if you have column names, you skip the first row.

    If you have column names on row1, you can have, say, 12 columns, and just use 4 if you want.

    It is a very flexible construct. Check it out.

    https://dev.mysql.com/doc/refman/5.1/en/load-data.html

    LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
      FIELDS TERMINATED BY ',' ENCLOSED BY '"'
      LINES TERMINATED BY '
    '
      IGNORE 1 LINES;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥20 Intellij实现web登录界面
  • ¥15 IPQ5018制作烧录固件,boot运行失败(操作系统-linux)(相关搜索:操作系统)(相关搜索:操作系统)
  • ¥20 icefall在librispeech基础上加入个人数据集
  • ¥30 keepalive高可用故障运维配置询问
  • ¥15 求帮助!国家电网内网u盘突然识别不出来了。
  • ¥15 matlab语音变速变调同时实现
  • ¥15 如何用Thoony写ESP32温湿度检测无源蜂鸣器报警代码?
  • ¥20 部件内部的CT图像数据集
  • ¥15 Visual studio调用动态库
  • ¥15 双目摄像头标定后的校准文件