php with mysql: import .csv delimited space

Question

How import this file to mysql with spaces between rows? Through phpmyadmin it is possible, but I need do it via website.

<?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{?>

And this is my .csv file with data:

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

Spaces between rows? Do you mean the csv has a blank row every other row? — RiggsFolly
– RiggsFolly, Commented Sep 7, 2020 at 19:36
If the space is between fields, look at the manual for fgetcsv() for the delimiter. — Nigel Ren
– Nigel Ren, Commented Sep 7, 2020 at 19:38
Ahh that makes more sense @NigelRen :) Maybe its time to go and watch University Challenge instead — RiggsFolly
– RiggsFolly, Commented Sep 7, 2020 at 19:38
Your script is open to SQL Injection Attack. Even if you are escaping inputs, its not safe! You should consider using prepared parameterized statements in either the MYSQLI_ or PDO API's instead of concatenated values — RiggsFolly
– RiggsFolly, Commented Sep 7, 2020 at 19:40
htmlentities should only be being used when outputting to an HTML doc. — user3783243
– user3783243, Commented Sep 7, 2020 at 19:48

RiggsFolly · Accepted Answer · 2020-09-07 19:51:21Z

1

// prepare the insert query once outside the loop
$sql = 'INSERT INTO employees (`numbers`, `tids`, `date`, `time`, 
                               `zero`, `terminal_sn`, `space`)
            VALUES(?,?,?,?,?,?,?)';
$stmt = $dbcon->prepare($sql);

if (($handle = fopen($_FILES['csv-file']['name'], "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, " ")) !== FALSE) {
        //             space delimiter      ^
        
        // bind the columns to the query parameters
        $stmt->bind_param('sssssss', $row[0], $row[1], $row[2], $row[3],
                                     $row[4],$row[5], row[6]);
        // execute the query with parameters replaced with data
        $stmt->execute();
    }
    fclose($handle);
}

answered Sep 7, 2020 at 19:51

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tadman Over a year ago

Also possible: LOAD DATA INFILE to directly import the CSV.

symcbean Over a year ago

The date value in the csv field should be stored in a date data type (and presumably this is actually a date+time - so the 2 attributes from the CSV should be combined to a single value). But the above will probably not work as expected in such a scenario. really the values need to parsed and vonverted to an appropriate representation.

RiggsFolly Over a year ago

@symcbean Totally agree, was going to wait for a complaint that this did not work before making that point, but none came. I am assuming that means the date and time are being stored as a VARCHAR data type columns. We will wait for the SQL based question about how to do interesting stuff with dates that are not stored in a DATE data type :)

Collectives™ on Stack Overflow

php with mysql: import .csv delimited space

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related