Jump to content
Sign in to follow this  
Jondb

Converting date formats on import

Recommended Posts

Jondb    0

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

 

 

Share this post


Link to post
Share on other sites
Lee Smith    144

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

Share this post


Link to post
Share on other sites
comment    1,370
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 by comment

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.