Import CSV file with a column having date or text inside

Question

I have a CSV file with a column having a date in format d/m/Y or the word "Illimité" inside meaning unlimited in French.

I would like each time it reads "Illimité" it puts NULL value inside MySQL table.

Here is my current PHP code :

if (isset($_POST['submit'])) {
        $query = "TRUNCATE TABLE `formation` ";
        $result = mysql_query($query);
        $file = $_FILES['file']['tmp_name'];
        $handle = fopen($file, "r");
        while (($fileop = fgetcsv($handle, 1000, ";")) !== false) {

            $nom = $fileop[0];
            $prenom = $fileop[1];
            $formation = $fileop[16];
            $validite = $fileop[26];

            if (is_numeric($validite)) {
                $validite = date_format(date_create_from_format('d/m/Y', $validite), 'Y-m-d');
                $sql = mysql_query("INSERT INTO formation (nom,prenom,formation,validite,usermaj) VALUES ('$nom','$prenom','$formation','$validite','importCSV')");
                } else {
                $sql = mysql_query("INSERT INTO formation (nom,prenom,formation,validite,usermaj) VALUES ('$nom','$prenom','$formation',NULL,'importCSV')");
            }
        }

Sadly this isn't working. MySql shows no errors and it puts NULL all the time. Any help would me much appreciate.

Answer 1

Try this:

function myFunc($CSVValue){

    if(validateDate($CSVValue)){
        //your mysql logic where you put in the date
    }else{
        //your mysql logic where you put in the null
    }


}

function validateDate($date)
{
    $d = DateTime::createFromFormat('d/m/Y', $date);
    return $d && $d->format('d/m/Y') == $date;
}

^{function was copied from this answer or php.net}

-- update --

I don't know how your code looks other than what you have provided. If you were to put this in your code it could look something like this:

if (isset($_POST['submit'])) {
        $query = "TRUNCATE TABLE `formation` ";
        $result = mysql_query($query);
        $file = $_FILES['file']['tmp_name'];
        $handle = fopen($file, "r");
        while (($fileop = fgetcsv($handle, 1000, ";")) !== false) {

            $nom = $fileop[0];
            $prenom = $fileop[1];
            $formation = $fileop[16];
            $validite = $fileop[26];

            $d = DateTime::createFromFormat('d/m/Y', $validite);

            if ($d && $d->format('d/m/Y') == $validite) {
                $validite = date_format(date_create_from_format('d/m/Y', $validite), 'Y-m-d');
                $sql = mysql_query("INSERT INTO formation (nom,prenom,formation,validite,usermaj) VALUES ('$nom','$prenom','$formation','$validite','importCSV')");
                } else {
                $sql = mysql_query("INSERT INTO formation (nom,prenom,formation,validite,usermaj) VALUES ('$nom','$prenom','$formation',NULL,'importCSV')");
            }
        }