php 与 mysql：导入 .csv 分隔空间

Question

如何将此文件导入到mysql，行之间有空格？ 通过 phpmyadmin 这是可能的，但我需要通过网站来完成。

<?php

// Check if file was uploaded & there were no errors

if ($_FILES && $_FILES['csv-file']['error'] == 0) {

    $extension = pathinfo($_FILES['csv-file']['name'],PATHINFO_EXTENSION);

    // Check if extension is csv then proceed to import

    if($extension == 'csv'){

        // Open file for reading

        $file = fopen($_FILES['csv-file']['tmp_name'], 'r');

        // Loop through all rows of file and insert them to database table

        while (!feof($file)) {

            // Get current row as recordset

            $row = fgetcsv($file);

            if (!empty($row)) {

                $data = [];

                $data['numbers'] = htmlentities($row[0]);

                $data['tids'] = htmlentities($row[1]);

                $data['date'] = htmlentities($row[2]);

                $data['time'] = htmlentities($row[3]);

                $data['zero'] = htmlentities($row[4]);

                $data['terminal_sn'] = htmlentities($row[5]);

                $data['space'] = htmlentities($row[6]);

                $records[] = $data;

                mysqli_query($dbcon,"INSERT INTO employees (".implode(",",array_keys($data)).") VALUES ('".implode("','",array_values($data))."')");

            }

        }

    }else{?>

这是我的 .csv 文件，其中包含数据：

10222157120501 T0040922 07/09/2020 18:13:56 0 315-525-348 1

10223157120502 T0040923 07/09/2020 18:15:24 0 318-027-497 1

10224157120503 T0040924 07/09/2020 18:15:36 0 316-176-614 1

10225157120504 T0040925 07/09/2020 18:16:25 0 317-377-077 1

Answer 1

// prepare the insert query once outside the loop$sql = 'INSERT INTO employees (`numbers`, `tids`, `date`, `time`,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;`zero`, `terminal_sn`, `space`)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES(?,?,?,?,?,?,?)';$stmt = $dbcon->prepare($sql);if (($handle = fopen($_FILES['csv-file']['name'], "r")) !== FALSE) {&nbsp; &nbsp; while (($data = fgetcsv($handle, 1000, " ")) !== FALSE) {&nbsp; &nbsp; &nbsp; &nbsp; //&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;space delimiter&nbsp; &nbsp; &nbsp; ^&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; // bind the columns to the query parameters&nbsp; &nbsp; &nbsp; &nbsp; $stmt->bind_param('sssssss', $row[0], $row[1], $row[2], $row[3],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$row[4],$row[5], row[6]);&nbsp; &nbsp; &nbsp; &nbsp; // execute the query with parameters replaced with data&nbsp; &nbsp; &nbsp; &nbsp; $stmt->execute();&nbsp; &nbsp; }&nbsp; &nbsp; fclose($handle);}