Subscribe to this thread
Home - General / All posts - Import table unexpected date format issue
Graeme

992 post(s)
#24-May-24 06:11

I downloaded and imported the May 2024 release of GNAF-Core from Australian PSMA. It is a text based PSV file and imported direct to M9 9.0.181. It includes a field "DATE_CREATED" Several records display as default 12/01/2024, but when the style is changed to "en-AU" English Australian, they display and query in M9 as being 01 December 2024. Thinking this may have been a data entry error, I raised it with the data supplier, who replied in part:

<!--[if gte mso 9]> <![endif]--><!--[if gte mso 9]> Normal 0 false false false EN-AU X-NONE X-NONE <![endif]--><!--[if gte mso 9]> <![endif]--><!--[if gte mso 10]> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman",serif;} <![endif]-->

GNAF Core is a text file and the dates are provided in the format dd-mm-yyyy

We can find many examples of 12-01-2024but can't find any with 01-12-2024. Any instances of “1/12/2024” you are seeing is likely to be a translation error from the application you are using to open the GNAF_CORE.psv file.

The records in question I have exported from M9 and attach.

Attachments:
GNAF_CORE_May2024_date error.psv

Dimitri


7,452 post(s)
#25-May-24 06:31

Key question: What is the actual date in the data set? Is it the first day in December 2024 or is it the twelfth day in January 2024? Given that we are not yet in December of 2024 I'm guessing that the authors of GNAF Core intended a date of 12 January 2024.

If they were using a sensible, modern interchange format, you wouldn't have to ask that question, but text formats like CSV/PSV are liable to all sorts of chaos, for example, because plain text does not unambiguously define data types like datetime values.

Different countries use different date formats (month day year, day month year, etc.) so a dumb text format like CSV/PSV does not in and of itself say what those dates are supposed to be. You have to guess at it, perhaps using clues like what the user has picked for a system language on his or her computer. There's a similar problem in CSV/PSV with the never-ending fight between use of dot . characters or comma , characters as decimal separators in numbers.

Start by reading the main CSV topic, carefully noting the advice on Unicode and also in the notes:

Dates- Different languages have different ways for expression dates. When recognizing date values as dates, Manifold is guided by whatever languages are specified in the Help - Aboutdialog in the Langentry, the presumption being that the date formats in use are those employed by whatever language is in use. Reading a CSV file allows date fields to use multiple different languages. For example, one field can use US English while another field can use German.

So, from the above if the data set you are using represents dates in text form using the traditional Australian convention giving a date in day month year order, and if you also have specified en-AU as the Lang entry, well, then there are good odds that the CSV importer will correctly and automatically transform the Australian convention text values used for dates into genuine datetime values in the table.

As to the difference between what actually is the datetime value and how it is styled, read the Styling Table Fields and Style: Datetime Fields topics for useful info like:

Styling a field does not change either the field's data type or the contents of that field. It just changes the way the contents of the field are displayed in a table window, the Info pane, Labels, or other settings where the table values are displayed. For example, styling a datetime field that contains the value 05/19/2016 07:27:37to use the D(date) option so it displays as Thursday, 19 May 2016does not convert the datetime field into a text field so that the day of the week and the month can be spelled out, nor does it truncate the datetime value to remove the time portion. It simply shows the datetime value using a different display format.

and

The Style dialog provides an extensive selection of different date and time styles, which will automatically adapt to language-country choices specified in the Languagesetting. Over 600 languages are supported by Manifold in Windows 10, with automatic use of correct language names for days of the week and months and automatic use of the correct cultural format for the country.

The key thing is to first import the data correctly. If your original file uses some local date format convention then it is essential your Lang setting matches that, to give the automatic matching of formats a chance to work correctly. After that, once you import your data you can Style it to appear however you want, using whatever local language style you want.

One last thing: you might not have any choice in the matter, but if you can, get the GNAF Core data in some modern format, not PSV. GPKG/SQLite is often an option. Then there's no ambiguity about the datetimes or any other field.

Graeme

992 post(s)
#03-Jun-24 08:59

Thanks for the considered reply and suggestions. I did find a W11 system date setting, which defaulted to US_English, changed and restarted. Retried the import, but still saw the anomaly. Will pass on the suggestion about none text formats for data supply.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.