2 Replies Latest reply on May 30, 2011 6:16 PM by TomGallivan

    importing timestamp as date

    TomGallivan

      Title

      importing timestamp as date

      Post

      I'm working with data that will be imported from a csv. One of the fields, "Created Date" appears as" 'Oct 30 2009 9:34 AM" when viewed in a spreadsheet program (Open Office Calc). The leading apostrophe identifies it as being a numeric format, I believe. In Open Office if I use the function DATEVALUE on data in that field and then format the cells as Date I receive the rseult I'm looking for, e.g. 10/30/2009, which can then be sorted in chronological order.  

      I can't figure out how to achieve the same result in FM Pro , having tried all sorts of appraoaches (nooby here and to FM  btw). Any ideas?

      Thanks,

      Tom

        • 1. Re: importing timestamp as date
          LaRetta_1

          "I can't figure out how to achieve the same result in FM Pro ,"

          What result is that?  Do you mean that you want to import the timestamp only as a date?  Or do you mean that you want the result to display on your layouts as Oct 30 2009 9:34 AM?

          Have you tried importing the timestamp into a date?  It should work fine.  If it does not work then you the actual data must be Oct 30 2009 9:34 AM as text which will mean a conversion once inside FileMaker.  You would need to import into a text field and then a date field can convert it.  Here is one way:

          Create a text field called textDate.  You will import your pseudo timestamp into textDate.  Then create a calculation (result is date) with:

          Date (
          Ceiling ( Position ( "janfebmaraprmayjunjulaugsepoctnovdec" ; LeftWords ( text ;  1 ) ; 1 ; 1 ) / 3 ) ;
          MiddleWords ( text ; 2 ; 1  )  ;
          MiddleWords ( text ; 3 ; 1 )
          )

          After your import, check this date calculation.  If it displays as you wish, go back and change it to a date field.  The values from the calculation will remain as actual data and you can then delete the text field.


          • 2. Re: importing timestamp as date
            TomGallivan

            Thanks LaRetta! works perfectly and I appreciate your help very much. I'll get back to trying to flatten my FM learning curve and, hopefully, sometime in the future be able to help out a nooby too.