3 Replies Latest reply on Jan 6, 2012 1:29 AM by aikiko

    Exporting dates into CSV: 2-digit not  4-digit year

    aikiko

      Title

      Exporting dates into CSV: 2-digit not  4-digit year

      Post

      Dear All,

      I have exported my FMP file as a CSV file but the years are in 2 digits, and I need them to be 4 digits to work in my anaylsis software, Stata. 

      I have checked and un-checked the "Apply current layout’s data formatting to exported data" option

      In the options for the date field, I have already checked "Strict data type: 4 digit year date"

      I have exported under the "Merge" option to make the CSV file.

      If I export the FMP file as an Excel file, I will get the 4 digit year, then I can export it as a CSV file. However this will slow me down and is not so elegant.

      Is this a CSV issue? Merge issue? Or am I missing something obvious? Thoughts appreciated.

      Kind regards

      Aikiko

      OSX 

      FMP11

       

       

       

       

        • 1. Re: Exporting dates into CSV: 2-digit not  4-digit year
          philmodjunk

          Data formatting does not have any effect on exported data. This option allows the layout format (structure might be a better term) such as the existance of a portal to affect the export.

          You may need to set up a calculation field that produces text in the needed date format and export it in order to get the needed data format for your dates.

          • 2. Re: Exporting dates into CSV: 2-digit not  4-digit year
            Sorbsbuster

            When I:

            - set up an FM file with a date field (no validation)
            - set it to display ('format as') dd/mm/yy
            - enter the date as dd/mm/yy
            - export as a CSV file,
            - open in Excel 2007

            It shows at import, imports, and displays the dates in yyyy format.

            The same happens if I export as a Merge file.  In fact, I thought I made every effort to stop it exporting as 4-digit years, and I can't stop it...

            Filemaker went to a lot of trouble around Y2K to interpret 2-digit dates, but the rules all suggest that they would enforce the 4-digit export you want.  I note, of course, that my tests were importing into Excel, not Stata.

            So far, so much the same as you've found.  But if I open the csv files in Notepad, the data already shows as yyyy.  Is that what you find?  If so, it suggests a problem in Stata.

            • 3. Re: Exporting dates into CSV: 2-digit not  4-digit year
              aikiko

              Dear PhilModJunk & Sorbsbuster,

              Thanks for your suggestions.

              I have played around with it, and I think it was an Excel issue I was having as I managed to export the CSV file as previously stated, and could see the 4 digit year.

              When I was editing the CSV file in Excel 2011 prior to use with Stata, it saved any 2 digit year into the 20** date format instead of 19** date format.

              I subsequently changed the preferences in Excel to always show 4 digit years.

              Apologies for what I thought could have been an FMP problem I was having, but confirming that FMP exports 4 digit years when asked to.

              Aikiko