10 Replies Latest reply on Jan 31, 2016 3:30 PM by MacDevGuy

    How to clean text and format during or after import

    MacDevGuy

      Title

      How to clean text and format during or after import

      Post

       

      I have a text file which is a log from TeamViewer. The log lists the sessions by client number "757123456" (which is usually maintained for the client but sometimes changes), start and end times as "DD-MM-YYYY<sp> HH:MM:SS" and a session id number as "{D44E5CD7-30F6-762B-A331-9B72031CB247}"

      So a single entry would look something like this:

      (The header is mine...)
      Client ID               Sessoin Start                   Session End               admin    Type of session      Session ID
      757123456          20-02-2015 10:25:22       21-02-2015 00:09:10  Billy   RemoteControl         {D44E5CD7-30F6-762B-A331-9B72031CB247}

      The information is separated with spaces and as you can see the dates and times are combined for start and end time. What I want to do is strip the spaces and substitute them for tabs. I also want to (or maybe I should say I *may* need to) separate the date and times with the intent to write a calculation which gives me the elapsed time for the session.

      So how do I deal with this? Can I import it into a container then use a calc to clean it and call a script to insert the resulting field data into their respective fields?

        • 1. Re: How to clean text and format during or after import
          MacDevGuy

          Ok so I was just fooling around with calculating the time and I got a strange result. I used the following formula to calculate the elapsed time:
          ((DateEnd - DateStart) * 86400) + TimeEnd - TimeStart

          The elapsed time field shows "1e+12" and "999999767942" when I click into the field. I am guessing I need a calculation to convert the number to hours and minutes. So I searched 1e+12 and found some stuff in the knowledge base about Excel compatibility which brings more questions... Is a timestamp in filemaker compatible with Excel timestamps? Does Excel even have a timestamp function - Is a timestamp a universal thing like for example? I entered the number into Epoch Converter and it gave me a date back in 2011 so I am guessing this was't epoch time 

          • 2. Re: How to clean text and format during or after import
            SteveMartino

            You may want to look at the following functions to help parse the info

            GetAsDate(text)

            GetAsNumber(text)

            GetAsText(text)

            GetAsTime(text)

            GetAsTimestamp(text)

            along with the Left, LeftWords, Middle, and MiddleWords and of course Substitute functions.  If that's the exact way the string comes in, specifically noting the space between the date and time, then it should be a breeze, one calculation for each category.  Your strange time issue might just be a simple solution as converting the text with GetAsTimestamp and subtracting the two.

            • 3. Re: How to clean text and format during or after import
              philmodjunk

              In FileMaker , subtracting one time value from another gives you elapsed time in seconds. The same result is produced via the difference between two timestamp values, but now you get an accurate result even when midnight falls between start and stop. 

              Also, you can select either time or timestamp result types when your result is elapsed time in seconds. 

              • 4. Re: How to clean text and format during or after import
                MacDevGuy

                Steve,
                I jumped in on trying some things and didn't get very far. I am going to take a look at the kb for each function and see if it sheds some light. Any suggestions on basic calculation tutorials I might work through?

                Phil, 
                I assume that means that a former version of FMP didn't and these are feature additions, would that be correct? By the way... You are helping everywhere in the forums. Are you a moderator?

                 

                • 5. Re: How to clean text and format during or after import
                  raybaudi

                  "The information is separated with spaces"

                  Are you sure that those are spases? Could they be tabs?
                  Could you post a real log ?

                  • 6. Re: How to clean text and format during or after import
                    philmodjunk

                    The features that I describe have been around for a long time. They are not new.

                    A few years back, I was recruited to be the first (and so far only) volunteer "community leader" of this forum.

                    • 7. Re: How to clean text and format during or after import
                      MacDevGuy

                      Hi Ray, yes I am pretty sure since I took it into TextWrangler using "display invisibles". Unless you know something I am not aware of.

                      Phil, I left Filemaker back at around V 3.x and so I have a lot of new ground to cover. Thanks for the time you put in. I am sure many appreciate it.

                      • 8. Re: How to clean text and format during or after import
                        philmodjunk

                        MsWord can also be used to show invisible characters. (Click the "Pilcrow" button).

                        • 9. Re: How to clean text and format during or after import
                          MacDevGuy

                          Hi Phil;

                          Yes you are right but I find TextWrangler has a lot less overhead and bloat so at launch it opens quicker and generally feels much more responsive. I do use it quite a bit as my default though. I use TextWrangler when I want to be sure I am not getting any MS Control Codes secretly inserted.
                          smiley

                           

                          • 10. Re: How to clean text and format during or after import
                            MacDevGuy

                            I gave Steve the "game point" on this one since it was the closest to what I needed. I elected to do some research on the topic and found some interesting stuff on time.

                             

                            With regard to Excel:


                            Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2008, is serial number 39448 because it is 39,448 days after January 1, 1900.

                            ----
                            More and more I have come to believe that it's better not to export things to Excel from Filemaker as the results can sometimes turn into a frustrating experience. My most recent "surprise" was exporting a repeating field and gaining a 'full understanding' of what it means to lose subsequent values in a repeating field.