Import csv with wrong format date to MySQL with Symfony-php

Question

I'm trying to upload a csv file to my mysql table with symfony/php.

                                    ...

while (($data = fgetcsv($handle, 0, ";")) !== false) {

                    $count++;
                    if ($count == 1) { continue; }

                    $entity = new Oasice();
 
                    $entity->setCentre($data[0]);
                    $entity->setAffaire($data[1]);
                    $entity->setTypeAffaire($data[2]);
                    $entity->setTypeDossier($data[3]);
                    $entity->setCommune($data[4]);
                    $entity->setCodeInsee($data[5]);
                    $entity->setMoaEr($data[6]);
                    $entity->setDateAmeoAmheo($data[7]); // date
                    $entity->setChargeAffaire($data[8]);
                    // a lot more column...

In this file I have ten columns with dates in the format dd/mm/YYYY which poses a problem for the integration into the database.

I tried the solutions I could find so far (like for example: this) but nothing conclusive.

example

$date = date("Y-m-d", strtotime(str_replace('/', '-', $data[7])));
dump($date)

but my result is often 01-01-1970 or 1970-01-01 or empty strings.

Edit:

example of date format in multiple columns of the file:

How should I go about it please? what am I doing wrong ?

You should give us some examples how your date is looking in your file in that cases where you get a wrong result. — René Höhle, Oct 10 '20 at 14:51
The dates are all in this type of format: : 04/04/2018 04/09/2020 19/07/2017 — Shika, Oct 10 '20 at 16:06
It is unclear to me what Doctrine version you are using. But in case you specified the entity's `dateAmeoAmheo` property to be one of the [Doctrine date column types](https://www.doctrine-project.org/projects/doctrine-dbal/en/2.10/reference/types.html#date-and-time-types), you should just give a `DateTimeInterface` instance to `setDateAmeoAmheo`, and Doctrine will manage the appropriate date conversion for you. So, perhaps convert the csv's date value to a `DateTime` instance before passing it to your entity. — Jeroen van der Laan, Oct 10 '20 at 19:34
Hmm, my setDateAmeoAmheo is already in DateTimeinterface instance, Doctrine did it itself — Shika, Oct 10 '20 at 21:17
Can you share more details? Why does this date format "pose a problem"? What **exactly** does that mean, and what have you tried to resolve that problem? — Nico Haase, Oct 12 '20 at 06:40
Well, my first problem was : ```Argument 1 passed to App\Entity\Oasice::setDateAmeoAmheo() must implement interface DateTimeInterface or be null, string given, called in ...\htdocs\myProject\src\Controller\ImportController.php on line 77``` so i tried to change the dates i have in my columns to match correct date format, not a string... so far I have tried several methods, such as str_replace etc. My goal is to retrieve the dates (and other data) from this csv in order to put them in my database. — Shika, Oct 12 '20 at 16:39
and the problem is that in my date columns there are often empty strings , this is mainly what drives me crazy — Shika, Oct 12 '20 at 17:13

Jakumi · Accepted Answer · 2020-10-10T19:36:26.687

essentially date_create_from_format (which is an alias for Date::createFromFormat) can create dates from various formats, especially numerical variants (like in the answer you linked yourself):

$date = date_create_from_format('d/m/Y', $data[7]);

// then turn it into the format you want, like Y-m-d:
// $mysqlDate = $date->format('Y-m-d');
// or assign it to your entity
$entity->setDateAmeoAmheo($date);

if you have multiple formats, you could work with fallbacks, since date_create_from_format will return false on error:

$date = date_create_from_format('d/m/Y', $data[7]) 
     ?: date_create_from-format('d/m/y', $data[7])
     // and so on, if needed.
;
// rest as before

Shika · Answer 2 · 2020-10-13T16:10:25.157

I think I have found a solution, not necessarily the best I guess, but I will consider it.

I will try to do this process on all my columns with dates and with empty string

I'll keep you posted if it's okay

      ```   $date7 = explode("/", $data[7]);
                //dump($date);
                if ($date7[0] !== "")
                {
                    $ladateameo = '"'.$date7[2].'-'.$date7[1].'-'.$date7[0].'"';
                }
                else
                {
                    $ladateameo = NULL; 
                }
                //dump($ladateameo);
                $entity->setDateAmeoAmheo($ladateameo); // date    data[7] ```

EDIT :

                    $date9 = explode("/", $data[9]);
                    dump($date9);
                    if ($date9[0] !== "")
                    {
                        dump($date9[0]);
                        $dateReceptionDossier = $date9[2].'-'.$date9[1].'-'.$date9[0];
                        dump($dateReceptionDossier);
                    }
                    else
                    {
                        $dateReceptionDossier = NULL;   
                    }
                    $entity->setReceptionDossier($dateReceptionDossier);

And the problem is still there...

Argument 1 passed to App\Entity\Oasice::setReceptionDossier() must implement interface DateTimeInterface, string given, called in ...\src\Controller\ImportController.php on line 85

The dates are finally in the right format but still taken for string...

Import csv with wrong format date to MySQL with Symfony-php

2 Answers2