7 Replies Latest reply: May 15, 2014 9:55 AM by Dee Moore RSS

    Removing Spaces

    Ontiveros _

      I have a report that has fileds output in the following format (spaces between each character).  What I am looking for is a way to remove all of the spaces between all words>  Below is an example:

       

      1 1 / 1 4 / 0 5

       

      What I want is:

       

      11/14/05

       

      Does anyone have any ideas as to how I would go about it?

        • Removing Spaces
          Data Kruncher

          I would first capture the entire date as a string, called OriginalDate.

           

          Now create a calculated date field called FinalDate (or whatever the date really represents) whose formula is ctod(strip(OriginalDate," "),"m/d/y").

           

          This first removes all the spaces from the text date, then converts the text to a true date format.

           

          That ought to do it for you. Please do let us know how you make out.

           

          Kruncher

           

          Todd, you got in there while I was typing.

          • Removing Spaces
            Nick Osdale-Popa

            I forget which Version introduced the Strip() function. Ontiveros, if you can't find the function, you may have to go with Todd's suggestion.

            • Removing Spaces
              Grant Perkins

              Hi Todd.

               

              I seem to recall Mike U referring to 'elegant solutions' on occasion so I claim no rights to the concept!      

               

              Ontiveros,

               

               

              My first inclination is to go with Nick O-P's suggestion and use the Strip function.

               

              If the entire file was like that and all spaces were to be removed it might be worth considering using MSRP on the file prior to input.

               

              Otherwise an alternative to SUBSTR in this example might be to use one (or both, though I can't think of a good reason to do so) of LSPLIT or RSPLIT.  But in effect the formula would be pretty much the same as for SUBSTR so it would seem to make little difference which way one goes.

               

              The fields look like date fields. Are they? And does the problem only arise with date fields or are other fields involved? (I seem to recall we had an example of a similar problem relating to a report's header section a while ago ...)

               

               

              HTH.

               

               

              Grant

               

              Edit to add that Data Kruncher's post has the full formula I would use for the calculated field. The only potential issue I can see from specifying the field as a date fields is that the "05" would be presented as "2005" ( I assume your settings would not imply an "05" in any other century!) Would that be a problem?

               

              [size="1"][ March 07, 2006, 06:46 PM: Message edited by: Grant Perkins ][/size]

              • Removing Spaces
                Grant Perkins

                Originally posted by Todd Niemi:

                Grant, my apologies for incorrectly attributing the phrase "elegant solution" to you. In light of my error, I've edited the related posts. /b[/quote]Todd, there was really no need to go that far ...     

                 

                Originally posted by Todd Niemi:

                 

                In my defense, though, it seems appropriate to associate you with "elegant solutions" since, of all the people who have posted on this forum, you have provided far more of them than anyone else. /b[/quote]Well, thank you kindly sir. It's very nice of you to say so. Perhaps I am lucky in that the time difference for the UK has often allowed me to research and post a suggestion before the sun rises over North America from where many excellent suggestions emanate  (when they get the chance!!!)

                 

                 

                Back to the subject of the post though - does Ontiveros have a solution I wonder?

                 

                Grant

                • Removing Spaces
                  Ontiveros _

                  I have found a solution in having a program called Miraplacid 4.0 take all reports used, combine them into one single report and output them in a different format which removes spaces between characters. Thank you both for your advice, though.  If it were only a date field, the STRIP function would have worked perfectly.  Have a nice day.

                  • Removing Spaces
                    Grant Perkins

                    Originally posted by Ontiveros:

                    I have found a solution in having a program called Miraplacid 4.0 take all reports used, combine them into one single report and output them in a different format which removes spaces between characters. /b[/quote]That sounds like an interesting program to be aware of, though I guess some judicioous editing using something like vi could give a similar result.

                     

                    Does Miraplacid have some clever setting to allow valid spaces to be retained - for example leaving a single space if the is a space pair? (The alternative is a 3 or more pass routine to change space pairs into something else in pass one, then remove all the remaining spaces, then convert the space pair identifier characters back to a single space. That would be a simple situation - it could get more complex of course.)

                     

                    Grant

                    • Removing Spaces
                      Dee Moore

                      Hi,

                       

                      Sorry I didn't see this sooner.......

                       

                      Files of this type are generally written in Unicode. And, yes, they need to be converted to a more workable format.

                       

                      Usually resaving as a Plain Text Format from WordPad will take care of the problem.

                       

                      Dee Moore

                      Datawatch Tech Support