Jump to content

Date format issue on import


This topic is 2627 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I'm doing an XML import of data. In the date field of the XML it's formated as YYYY-MM-DD.

 

we want the date to be formatted in Filemaker at System Short.. In doing this we get the ?.

 

Why can't Filemaker understand the date and reformat?

How can I go about changing the format on import or right after import?

 

Thanks

Link to comment
Share on other sites

Are you getting any sort of error importing the data into the field in the first place? If you enter a YYYY-MM-DD into a date field it automatically tells you the format is wrong. If you import the data as MM-DD-YYYY you will have no issue and then can work with the data any way you want. All you need to do is change the data formatting on the Inspector/Data Tab while selecting the field. Just set the format to Custom and change the arrangement of the date to your specific format. 

Link to comment
Share on other sites

If you cannot alter how the data is imported, then another option would be to import the data into a holding field and parsing the YYYY-MM-DD format into the correct date field. When importing any outside data into any of my FM systems, I've always built a importing holding table, especially when data comes from outside the company. In this way if an error comes across i can easily check the data, clean it up and then import it into my live data. Once you get the process down you can make this as simple as clicking on button.

  • Like 1
Link to comment
Share on other sites

There is no error on import. If I leave the date filed as entered is will show the date as YYY-MM-DD but we want it to show as DD/MM/YY.

 

I've not been able to get custom to work.

Link to comment
Share on other sites

The field is a date field.

 

I will bring the data into a temp filed for importing, that's a great idea.

The import is an ODBC import will happen several hundred times a day. 

 

You have a calc that will parse this to the right format.

It's mind blowing that filemaker can not re-parse it on it's on. YYYY-MM-DD is an ISO standard. (Other words correct way to transfer date information)

Link to comment
Share on other sites

YYYY-MM-DD is not a date, it is text. Your import field has to be type of Text. Once it is in the field, you can then change the format to what you want.

 

See if this helps.
 
Let ( [
	Parts = Substitute ( YourDatefield ; [ "/" ; " " ] ; [ "-" ; " " ] ) ;
	Month = RightWords ( Parts ; 1 ) ;
	Day = MiddleWords ( Parts ; 2 ; 1 ) ;
	Year = LeftWords ( Parts ; 1 )
] ;
	Day & "/" & Month & "/" & Year
)         

Automatic message

This topic has been moved from "Conditional Formatting" to "Calculation Engine (Define Fields)".

 

Conditional Formatting is for formatting a text field.

Link to comment
Share on other sites

Lee,

 

YYYY-MM-DD is a date. Who would look at that and say it's just text and has nothing to do with a date :)

 

To get back to fixing my issue.

I'm going to import the records into holding fields.. AS TEXT.

To Parse the data, should this be done a script or as a calculation on the filed on import.

 

Thanks for your help

Link to comment
Share on other sites

What Lee is referring to is the field that you are importing into within your Filemaker database. The field on the database structure side is a text field as it is the only field that would except the data you are importing without generating an import error.

 

Current Import

----------------------------------------------------------

XML(data is text)     >>    FILEMAKER

date information               myfmfield (text)

 

Correct Import

----------------------------------------------------------

XML                        >>       FILEMAKER

date information                 myfmfield (date)

 

If Filemaker is set to treat the data as a date only field, the import would have failed. Because you are importing XML text into a text structured field it will accept almost any data. Unfortunately a text field doesn't automatically understand what a date is only each character. A date field on the other hand will see and expect MM/DD/YYYY. Your data will need to be scrubbed and changed over to the MM/DD/YYYY setup. Once it is inputted into a date structured field in the expected MM-DD-YYYY format, you can display the data in any format you deem correct including the YYYY-MM-DD.

Link to comment
Share on other sites

I'm doing an XML import of data. In the date field of the XML it's formated as YYYY-MM-DD.

 

we want the date to be formatted in Filemaker at System Short..

 

The simplest way to do this would be to alter your XSLT stylesheet (you do use an XSLT stylesheet in your import, don't you?) so that it transforms the YYYY-MM-DD date to the format used by your file.

 

 

 

 

YYYY-MM-DD is a date.

 

It is a date only if Filemaker will recognize it as such. If your file is set to use another date format, then YYYY-MM-DD is a meaningless text string.

 

 

 

 

 

The field on the database structure side is a text field as it is the only field that would except the data you are importing without generating an import error.

 

I am afraid you are mistaken about that: Filemaker allows you to import any kind of junk into a date field, resulting (as in this case) in invalid dates.

  • Like 1
Link to comment
Share on other sites

I am afraid you are mistaken about that: Filemaker allows you to import any kind of junk into a date field, resulting (as in this case) in invalid dates.

 

True, i guess it will ignore the extra data and form strange dates.

 

On the XML side, good call on the stylesheets, perfect avenue to prevent bad data importation.

Link to comment
Share on other sites

I got the issue resolved.

The issues was that when we ran the SQL Query we applied a TRIM the date field. :idot:

We are using a 360works plugin for our SQL query and import and I think that's using a stylesheet for it's import.

 

Thanks guys for helping!

Link to comment
Share on other sites

This topic is 2627 days old. Please don't post here. Open a new topic instead.

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
 Share

×
×
  • Create New...

Important Information

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