Converting date formats on import

[Jondb]

Hi-

My OS uses the European date format: DDMMYY. Some of the .csv files I import contain dates in US format, which I would like to convert on import.

I have the file options set to always use system formats, but the US origin files still import with MMDDYY dates.

I've tried setting the date field itself to display in European format, but that doesn't convert the dates themselves, it just makes the field unable to display any date beyond the 12th of the month.

Is there a script for converting date formats that I can include in my import script?

Thanks for your help.

Jon

 

 

[Lee Smith]

If you have FileMaker Pro Advance, then this Custom Function TextToDate might help you.

[comment]

3 hours ago, Jondb said:

Some of the .csv files I import contain dates in US format,

If some of the files you import are in MMDDYY format and others are in DDMMYY format, then you will need two separate procedures to import each kind. And you will have to select between them manually, because Filemaker's cannot tell if 010217 is January 2nd or February 1st just by looking at them - no one can.

You could script the import of US-format files so that immediately after the import (while the found set still contains only the just now imported records), the date field's contents are replaced with a calculation that rearranges the imported string.  You may also prefer to map the date to a text field during the import and take the string from there.

Obviously, you cannot solve this by having an auto-entered calculation attached to the date field - because that would be also applied when users try to enter dates into the field in the normal course of things.

 

3 hours ago, Jondb said:

I have the file options set to always use system formats, but the US origin files still import with MMDDYY dates.

If your system uses a European date format, and your file is set to use the system formats, then importing a US file will result in either a wrong date or an invalid one (with the exception of dates like 040417). In theory you could switch your OS to US locale before importing, but that's not practical.

 

3 hours ago, Jondb said:

I've tried setting the date field itself to display in European format

That has nothing to do with the problem. You need to make sure the field contains the correct data. The formatting only affects the way the data is being displayed in this particular field instance on this particular layout.

 

3 hours ago, Jondb said:

Is there a script for converting date formats that I can include in my import script?

As I mentioned, you could do Replace Field Contents[]. The exact calculation depends on the exact format (I doubt it's really MMDDYY, with no separators).

 

 

Edited July 16, 2017 by comment