Details about this thread
  • State Not Answered
  • Replies 5 replies
  • Subscribers 307 subscribers
  • Views 1846 views
  • Users 0 members are here
Related content from around SugarClub

How does Sugar Import recognise is a date is dd/mm/yyyy or mm/dd/yyyy ?

Hugo Geyskens

While starting to use the Contracts module , I've uploaded the about 3300 contracts which were managed so far via Excel.

After the upload , I saw that Sugar Import interpreted my dates as MM/DD/YYYY instaed of DD/MM/YYYY. 

The csv file was created out of an Excel using the semicolon as field separator and the coma as item separator (used within the Teams field).

When investigating this further (I discovered the import file properties section only a couple of days later :-) ) together with our IT department, they were able to create csv files that were imported correctly whatever the format (MM/DD/YYYY or DD/MM/YYYY) they used.

Only difference was , they used the comma as field separator and had only 1 team in the Teams field (so no item separator used).

I know I can solve my issue by manually setting the date format correctly in the Import Files Properties section. 

But I still like to understand what made the IT support file dates work fine while mine were interpreted incorrectly ?
Somehow , somewhere the Sugar Import tries to determine what my date format is...

Bye now

Hugo

Parents
  • 0 Sugar Employee Badge

    This is the order of the possibel formats in the CsvAutoDetect function:

        static protected $_date_formats = array(
        'm/d/Y' => "/^(0?[1-9]|1[012])\/(0?[1-9]|[12][0-9]|3[01])\/\d\d\d\d/", // 12/23/2010 or 3/23/2010
        'd/m/Y' => "/^(0?[1-9]|[12][0-9]|3[01])\/(0?[1-9]|1[012])\/\d\d\d\d/", // 23/12/2010 or 23/3/2010
        'Y/m/d' => "/^\d\d\d\d\/(0?[1-9]|1[012])\/(0?[1-9]|[12][0-9]|3[01])/", // 2010/12/23 or 2010/3/23
        'm-d-Y' => "/^(0?[1-9]|1[012])-(0?[1-9]|[12][0-9]|3[01])-\d\d\d\d/", // 12-23-2010 or 3-23-2010
        'd-m-Y' => "/^(0?[1-9]|[12][0-9]|3[01])-(0?[1-9]|1[012])-\d\d\d\d/", // 23-12-2010 or 23-3-2010
        'Y-m-d' => "/^\d\d\d\d-(0?[1-9]|1[012])-(0?[1-9]|[12][0-9]|3[01])/", // 2010-12-23 or 2010-3-23
        'm.d.Y' => "/^(0?[1-9]|1[012])\.(0?[1-9]|[12][0-9]|3[01])\.\d\d\d\d/", // 12.23.2010 or 3.23.2010
        'd.m.Y' => "/^(0?[1-9]|[12][0-9]|3[01])\.(0?[1-9]|1[012])\.\d\d\d\d/", // 23.12.2010 or 23.3.2010
        'Y.m.d' => "/^\d\d\d\d\.(0?[1-9]|1[012])\.(0?[1-9]|[12][0-9]|3[01])/", // 2010.12.23 or 2010.3.23
    );

    When it comes to an analysis of the csv data rows the following function will return the first matching format

       protected function getFormat(&$formats) {

        if (!$this->_parsed) {
            return false;
        }

        $depth = 1;

        foreach ($this->_parser->data as $row) {

            foreach ($row as $val) {

                foreach ($formats as $format=>$regex) {

                    $ret = preg_match($regex, $val);
                    if ($ret) {
                        return $format;
                    }
                }
            }
...

    So, the first regex matching format will be retiurned.

    When you have day values only from 1 to 12 the format m/d/Y will be taken.

    Harald Kuske
    Principal Solution Architect – Professional Services, EMEA
    hkuske@sugarcrm.com
    SugarCRM Deutschland GmbH

  • 0 in reply to Harald Kuske

    Thanks Harald. Altough I'm not such a code-expert, I can extract the concept from your message.

    ;-)

Reply Children
  • 0 in reply to Hugo Geyskens

    If to assume the file rows have the same format and we are lucky to have any row with the date over the 12-th, then the format just could be detected for the file with some effort.
    In other cases, it's mandatory to specify the format directly prior to processing the data.
    Because neither code can interpret pure data value of e.g. "02/03/2022" as March or February day until some human being specify what is it for they.

    Best Regards,
    Dmytro Chupylka

    integroscrm.com
    We make work in Sugar CRM system faster, more convenient and efficient