4 Replies Latest reply: May 15, 2014 9:56 AM by Lori@UPenn _ RSS

    Removing Trailing Spaces

    Lori@UPenn _

      I am a brand new Monarch user, so I apologize in advance for my ignorance.  I need to take a fixed column report, create individual columns for first and last name (which will always be separated by a space and comma) and remove the trailing spaces from each column to make a variable column, delimited file.  Here is a copy of my report and below how I need it formatted.  I changed the names due to HIPAA regulations.

       

      Current report:

       

      DATE 02/09/06 TIME 10:33 A.M. PAGE    1 REPORT *$A2LBAX     U OF P HEALTH SYSTEM             N0FH

       

      LAST NAME|FIRST NAME|MREC#|PATNO|ADMIT DATE|DISCHARGE DT|SERVICE|PRECERT

       

       

          LAST1 ,ROSEANNE            |016817553   |31220932     |02/01/2006|02/08/2006|NEW|#NONE

          LAST2 ,PHYLLIS             |003900131   |31177173     |01/22/2006|02/08/2006|TRP|2271160

          LAST3, STAN                |043387323   |31252430     |02/06/2006|02/08/2006|CVM|2322454

          LAST4 ,RHOBERTHIA          |052475050   |31259351     |2/07/2006|02/08/2006|EMM|106020801252

               

       

      FORMAT DESIRED:

       

      DATE 02/09/06 TIME 10:33 A.M. PAGE    1 REPORT *$A2LBAX     U OF P HEALTH SYSTEM             N0FH

       

      LAST NAME|FIRST NAME|MREC#|PATNO|ADMIT DATE|DISCHARGE DT|SERVICE|PRECERT

       

       

          LAST1|ROSEANNE|16817553|31220932|02/01/2006|02/08/2006|NEW|#NONE

        • Removing Trailing Spaces
          Grant Perkins

          Originally posted by Lori@UPenn:

          Current report:

          [font="courier"]DATE 02/09/06 TIME 10:33 A.M. PAGE    1 REPORT *$A2LBAX     U OF P HEALTH SYSTEM             N0FH

           

          LAST NAME|FIRST NAME|MREC#|PATNO|ADMIT DATE|DISCHARGE DT|SERVICE|PRECERT

           

           

              LAST1 ,ROSEANNE            |016817553   |31220932     |02/01/2006|02/08/2006|NEW|#NONE

              LAST2 ,PHYLLIS             |003900131   |31177173     |01/22/2006|02/08/2006|TRP|2271160

              LAST3, STAN                |043387323   |31252430     |02/06/2006|02/08/2006|CVM|2322454

              LAST4 ,RHOBERTHIA          |052475050   |31259351     |2/07/2006|02/08/2006|EMM|106020801252

                   

           

          FORMAT DESIRED:

           

          DATE 02/09/06 TIME 10:33 A.M. PAGE    1 REPORT *$A2LBAX     U OF P HEALTH SYSTEM             N0FH

           

          LAST NAME|FIRST NAME|MREC#|PATNO|ADMIT DATE|DISCHARGE DT|SERVICE|PRECERT

           

           

              LAST1|ROSEANNE|16817553|31220932|02/01/2006|02/08/2006|NEW|#NONE /font[/quote][/b][/quote]Hi Lori,

           

          If you have Version 7 or 8 Pro Monarch here's what I would do. There may be some tings rto consider to the first one or two title and header rows but for the data it should not be a problem.

           

          Open the original file as a Database. (You may get and error warning because of the first line format. Ignore it.) Specify the separator for the data fields to be "|" in the appropriate place in the dialog offered.

           

          Load the table.

           

          Create a calculated field to LSPLIT the first column to give just the Last Name, splitting the field using the "," if possible..

           

          Duplicate that field and change the formula to get the First Name part of the field.

           

          Hide the first column imported. Move the new fields to where you want then in columns 1 and 2.

           

          Now export the table as a delimited text file and specify that the separator to be used is to be "|". This can be set in the system options feature. Ideally you will give the exported file an extension that will not be automatically interpreted by windows file associations - i.e. something line .out rather than .txt  .

           

          Trailing spaces will autoatically be discarded.

           

          Leading spaces won't be though and the LAST3 line in your sample produces one.

           

          You can eliminate this problem, if required, by using the LTRIM function.

           

          If the Database read option is not available (no Pro version) or not favoured I assume that the format of the original file will allow you to trap the columns as fields, in which case you can achieve the same result using the same method.

           

          Although I don't think you need them for your purpose here, as a reference the TRIM and RTRIM functions will deal with trailing spaces when required. If you have Version 8 check out INTRIM as well.

           

          If for some reason the file export option does not appeal there are other ways in which you could print the file in the same format but to do so would require quite a bit more work and in this case I cannot think of a good reason for considering such an option.

           

          HTH.

           

          Grant

          • Removing Trailing Spaces
            Lori@UPenn _

            Thanks Grant, I'll give that a try and let you know.

            Originally posted by Lori@UPenn:

            I am a brand new Monarch user, so I apologize in advance for my ignorance.  I need to take a fixed column report, create individual columns for first and last name (which will always be separated by a space and comma) and remove the trailing spaces from each column to make a variable column, delimited file.  Here is a copy of my report and below how I need it formatted.  I changed the names due to HIPAA regulations.

             

            Current report:

             

            DATE 02/09/06 TIME 10:33 A.M. PAGE    1 REPORT *$A2LBAX     U OF P HEALTH SYSTEM             N0FH

             

            LAST NAME|FIRST NAME|MREC#|PATNO|ADMIT DATE|DISCHARGE DT|SERVICE|PRECERT

             

             

                LAST1 ,ROSEANNE            |016817553   |31220932     |02/01/2006|02/08/2006|NEW|#NONE

                LAST2 ,PHYLLIS             |003900131   |31177173     |01/22/2006|02/08/2006|TRP|2271160

                LAST3, STAN                |043387323   |31252430     |02/06/2006|02/08/2006|CVM|2322454

                LAST4 ,RHOBERTHIA          |052475050   |31259351     |2/07/2006|02/08/2006|EMM|106020801252

                     

             

            FORMAT DESIRED:

             

            DATE 02/09/06 TIME 10:33 A.M. PAGE    1 REPORT *$A2LBAX     U OF P HEALTH SYSTEM             N0FH

             

            LAST NAME|FIRST NAME|MREC#|PATNO|ADMIT DATE|DISCHARGE DT|SERVICE|PRECERT

             

             

                LAST1|ROSEANNE|16817553|31220932|02/01/2006|02/08/2006|NEW|#NONE /b[/quote]

            • Removing Trailing Spaces
              Lori@UPenn _

              Originally posted by Lori@UPenn:

              Thanks Grant, I'll give that a try and let you know.

                /size[quote]quote:[/size]Originally posted by Lori@UPenn:

              I am a brand new Monarch user, so I apologize in advance for my ignorance.  I need to take a fixed column report, create individual columns for first and last name (which will always be separated by a space and comma) and remove the trailing spaces from each column to make a variable column, delimited file.  Here is a copy of my report and below how I need it formatted.  I changed the names due to HIPAA regulations.

               

              Current report:

               

              DATE 02/09/06 TIME 10:33 A.M. PAGE    1 REPORT *$A2LBAX     U OF P HEALTH SYSTEM             N0FH

               

              LAST NAME|FIRST NAME|MREC#|PATNO|ADMIT DATE|DISCHARGE DT|SERVICE|PRECERT

               

               

                  LAST1 ,ROSEANNE            |016817553   |31220932     |02/01/2006|02/08/2006|NEW|#NONE

                  LAST2 ,PHYLLIS             |003900131   |31177173     |01/22/2006|02/08/2006|TRP|2271160

                  LAST3, STAN                |043387323   |31252430     |02/06/2006|02/08/2006|CVM|2322454

                  LAST4 ,RHOBERTHIA          |052475050   |31259351     |2/07/2006|02/08/2006|EMM|106020801252

                       

               

              FORMAT DESIRED:

               

              DATE 02/09/06 TIME 10:33 A.M. PAGE    1 REPORT *$A2LBAX     U OF P HEALTH SYSTEM             N0FH

               

              LAST NAME|FIRST NAME|MREC#|PATNO|ADMIT DATE|DISCHARGE DT|SERVICE|PRECERT

               

               

                  LAST1|ROSEANNE|16817553|31220932|02/01/2006|02/08/2006|NEW|#NONE /b[/quote][/b][/size][/QUOTE]

              • Removing Trailing Spaces
                Lori@UPenn _

                Grant's solution worked, and now I have some follow up questions. 

                1. How would I keep the header (on the 1st page only) which is generated with the original report.  See below the line that starts with DATE 02/09/06. 

                2. How would I count the number of records - could I somehow use the RECNO function ?

                3. How could I keep the footer (on the last page only) which is generated with the original report.  Currently it looks like:

                END OF REPORT

                DATE 02/17/06 TIME 09:58 A.M. PAGE    5 REPORT *$A2LBAX     U OF P HEALTH SYSTEM             N0FH

                but I could live with just the words "End of Report".

                 

                Originally posted by Lori@UPenn:

                  /size[quote]quote:[/size]Originally posted by Lori@UPenn:

                Thanks Grant, I'll give that a try and let you know.

                   /size[quote]quote:[/size]Originally posted by Lori@UPenn:

                I am a brand new Monarch user, so I apologize in advance for my ignorance.  I need to take a fixed column report, create individual columns for first and last name (which will always be separated by a space and comma) and remove the trailing spaces from each column to make a variable column, delimited file.  Here is a copy of my report and below how I need it formatted.  I changed the names due to HIPAA regulations.

                 

                Current report:

                 

                DATE 02/09/06 TIME 10:33 A.M. PAGE    1 REPORT *$A2LBAX     U OF P HEALTH SYSTEM             N0FH

                 

                LAST NAME|FIRST NAME|MREC#|PATNO|ADMIT DATE|DISCHARGE DT|SERVICE|PRECERT

                 

                 

                    LAST1 ,ROSEANNE            |016817553   |31220932     |02/01/2006|02/08/2006|NEW|#NONE

                    LAST2 ,PHYLLIS             |003900131   |31177173     |01/22/2006|02/08/2006|TRP|2271160

                    LAST3, STAN                |043387323   |31252430     |02/06/2006|02/08/2006|CVM|2322454

                    LAST4 ,RHOBERTHIA          |052475050   |31259351     |2/07/2006|02/08/2006|EMM|106020801252

                         

                 

                FORMAT DESIRED:

                 

                DATE 02/09/06 TIME 10:33 A.M. PAGE    1 REPORT *$A2LBAX     U OF P HEALTH SYSTEM             N0FH

                 

                LAST NAME|FIRST NAME|MREC#|PATNO|ADMIT DATE|DISCHARGE DT|SERVICE|PRECERT

                 

                 

                    LAST1|ROSEANNE|16817553|31220932|02/01/2006|02/08/2006|NEW|#NONE /b[/quote][/b][/size][/QUOTE][/b][/size][/QUOTE]