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.

so your if statement is not passing, have you tried to var_dump($validite), does it give numeric value so it would have a chance to pass? — Siim Kallari, Apr 24 '15 at 07:26
@SunilPachlangia I cannot change it to UTF-8 because the type = date in MySQL — BackTrack57, Apr 24 '15 at 07:35
A date is not numeric so it always go's to the else statement — Jelle de Fries, Apr 24 '15 at 07:36
@JelledeFries and SunilPachlangia You are both right it's not numeric ! My mistake. I need to find how to test if it's a date in format d/m/Y. — BackTrack57, Apr 24 '15 at 07:41

score 0 · Accepted Answer · edited May 23 '17 at 10:29

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')");
            }
        }

I'm trying your code and it's not working. I'm getting Fatal error: Cannot redeclare myFunc() — BackTrack57, Apr 24 '15 at 08:13
You should put in some effort yourself, my code is just a template. you shouldn't just copy it. — Jelle de Fries, Apr 24 '15 at 08:17
Thank you a lot ! It's working perfectly ! I didn't understood how your code was working what i did was wrong. I still have much to learn. Good day ! — BackTrack57, Apr 24 '15 at 08:39

Import CSV file with a column having date or text inside

1 Answers1