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

Question

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

Answer 1

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;