How can i import null value in a csv file into my table of mysql?

Question

I would like to know how I can import a CSV file with null values to my MySQL table.

This is the code I use in my PHP to import the CSV file to the MySQL table.

<?php

class csv extends mysqli {

    private $state_csv = false;
    public function __construct() {

        parent::__construct("localhost","","","");

        if ($this->connect_error) {
            echo "File to connect Database: ". $this ->connect_error;
        }
    }

    public function import($file=''){
        $file = fopen($file, 'r');

        while ($row = fgetcsv($file)) {

            $value = "'". implode("','", $row) ."'";
            $q = "INSERT INTO test(cognome,nome,etichetta,email) VALUES(". $value .")";
            if ($this->query($q)) {
                $this->state_csv = true;
            } else {
                $this->state_csv = false;
            }
        }
    }
}

My CSV file has this structure:

Test, Test,,test@test.com

If I import the CSV file with all the data I can import it but the problem is when there is a null value.

You should be able to allow null values in your MySQL database columns. Otherwise, if you can't adjust the database, replace null values with an empty variable (or space). — O Genthe, Jul 10 '19 at 10:14
My database allow null values because when I do an import from mysql the null values are inserted in the table. The problem is when I import with my php I can't import null values or empty values. — Oriol Molina Lopez, Jul 10 '19 at 10:25
Perhaps a var_dump of $value and/or $q will shed some light on what the problem actually is? — O Genthe, Jul 10 '19 at 10:40

score 1 · Answer 1 · answered Jul 10 '19 at 10:40

I guess your sample CSV line

Test, Test,,test@test.com

is intended to become these four values for your database columns

 Test
  Test
 (null)
 test@test.com

But the third column appears as a zero-length string in your $row array, so it is inserted into the table as a zero length string. Your INSERT statement will look like this:

INSERT INTO test(cognome,nome,etichetta,email) VALUES('Test',' Test','','test@test.com')

See the empty string for etichetta?

You need to replace this line:

 $value = "'". implode("','", $row) ."'";

with some smarter code to loop over the elements of $row interpreting them in a more sophisticated way. Maybe something like this will work. (NOT debugged!)

$cols = [];
foreach ($row as $col) {
    if (strlen($row) == 0) array_push ($cols, "NULL")
    else array_push ($cols, "'" . mysqli_real_escape_string($row) . "'")
}
$value = implode (',', $cols);

The result will be

INSERT INTO test(cognome,nome,etichetta,email) VALUES('Test',' Test',NULL,'test@test.com')

And, using real_escape_string will handle names like O'Sullivan and Johnny ';DROP TABLE test; correctly.

I have fixed your code and I used it but the import still doesn't work. — Oriol Molina Lopez, Jul 10 '19 at 12:17
Sorry to say, "doesn't work" is not enough detail for us to help you. — O. Jones, Jul 10 '19 at 14:45

How can i import null value in a csv file into my table of mysql?

1 Answers1