7 Replies Latest reply on Mar 13, 2015 4:30 AM by PaSav-ICT

    Splitsing Text field in correct data

    PaSav-ICT

      Hi,

       

      We have a field that is imported with this information :

      Mon, 25 Aug 2014 07:11:00 -0500 (CDT)

      Now we want to make shure we filter the date out always correctly. And the Time and the TimeZone.

      I tryed it with location of the parts, but that is to insucure. Sometimes (CDT is there sometimes not for example)

      Ore Mon is written monday. We don't know what data we get with the import.

       

      The import is done for mail archiving from a account using Mailit plugin.

        • 1. Re: Splitsing Text field in correct data
          Markus Schneider

          THere are some custom functions available from briandunnings.com - but it might be easier to separate the values into a list:

          YourList = substitute(YourDateTime;" ";"FileMaker_CarriageReturn)

           

          will produce

          Mon,

          25

          aug

          2014

          etc.

           

          YOu can get the individual items with GetValue(YourList;2) for the second value (25) and so on. You could build a custom function that creates the desired output-format

          • 2. Re: Splitsing Text field in correct data
            erolst

            PaSav-ICT wrote:

            […] Sometimes (CDT is there sometimes not for example) Ore Mon is written monday.

            We don't know what data we get with the import. […]

             

            I think you can ignore both the CDT part (an add-on to the numerical time zone difference) and the weekday name (can be calculated from the date).

             

            Here's a calculation that parses out the desired parts based on your sample; adjust as need for any variations.

             

            Let ( [

              input = "Mon, 25 Apr 2014 07:11:00 -0500 (CDT)" ;

              inputList = Substitute ( input ; [ " " ; ¶ ] ) ; // returns "Mon,¶25Apr201407:11:00-0500(CDT)"

             

              dateStringList = MiddleValues ( inputList ; 2 ; 3 ) ; // returns "25Apr2014¶"

             

              theMonthName = GetValue ( dateStringList ; 2 ) & ¶ ;

              monthNameList = "Jan¶Feb¶Mar¶Apr¶May¶Jun¶Jul¶Aug¶Sep¶Oct¶Nov¶Dec¶" ;

              theMonth = ValueCount ( Left ( monthNameList ; Position ( monthNameList ; theMonthName ; 1 ; 1 ) ) ) ;

             

              theDate = Date ( theMonth ; GetValue ( dateStringList ; 1 ) ; GetValue ( dateStringList ; 3 ) ) ;

              theTime = GetAsTime ( GetValue ( inputList ; 5 ) ) ;

              TZD = GetValue ( inputList ; 6 )

              ] ;

              List ( theDate ; theTime ; TZD )

              // for demo purposes; returns "8/25/2014¶7:11:00¶-0500" for the sample data

              // if used in a script, create a Set Variable with this Let() where you define theDate, theTime, TZD as $vars and return a dummy result; then distribute the $vars via Set Field[]

            )

            • 3. Re: Splitsing Text field in correct data
              Benjamin Fehr

              We have a field that is imported with this information :

              Mon, 25 Aug 2014 07:11:00 -0500 (CDT)

              Now we want to make shure

               

              … another fellow with a background in Sound-Engineering?

              Shure use to be my favorite Microphones in Live-Environment

              • 4. Re: Splitsing Text field in correct data
                PaSav-ICT

                Thanks Erols for this calculation, it works like a charme.

                But what about when a import field does not have the Mon, in the field than you get the wrong information.

                How can i filter this out ?

                Like first trim out 3 characters left from the , and the , it self and if it is not there than don't clean in out.

                • 5. Re: Splitsing Text field in correct data
                  coherentkris

                  Trying to compensate for every possible input anomaly in the calculation is going to be near impossible.

                  The input domain (all the potential permutations of imported data going into the calculation ) has to be known in its entirety and the calculation needs to be built to function correctly every time.

                  Can it come on as

                  Mon,25 Apr 2014 07:11:00 -0500 (CDT)

                  Mon, 25 Apr 2014 07:11:00 -0500 (CDT)

                  Mon ,25 Apr 2014 07:11:00 -0500 (CDT)

                  Mon , 25 Apr 2014 07:11:00 -0500 (CDT)

                  Monday,25 Apr 2014 07:11:00 -0500 (CDT)

                  Monday, 25 Apr 2014 07:11:00 -0500 (CDT)

                  Monday ,25 Apr 2014 07:11:00 -0500 (CDT)

                  Monday , 25 Apr 2014 07:11:00 -0500 (CDT)

                   

                  You get the picture...

                   

                  Erolst gave you a great starting point and said "Here's a calculation that parses out the desired parts based on your sample; adjust as need for any variations."

                   

                  Adjust as needed.

                   

                  Kris

                  • 6. Re: Splitsing Text field in correct data
                    PaSav-ICT

                    It is a great starting point, it is also working for i think 95% of the records until now.

                     

                    The thing i run in to is, that some systems are not placing Mon, in front of the date. So then the date is not a middle value but a first value. Is it possible to make a clean up function that removes Mon, if it is there, and if it is not there there is no need to remove it.

                    • 7. Re: Splitsing Text field in correct data
                      PaSav-ICT

                      I got it, substitute the version that are used. It is working now like a charm, thanks for helping me think this out. Not giving a answer but directions is good way to learn.