5 Replies Latest reply: May 15, 2014 9:55 AM by Grant Perkins RSS

    Rows to Columns

    ozzbug _

      How can I get from:

                                                 

      Smith, John

      Units: 4C, TSI, GMU, GSU1

      Jones, Sam

      Units: TSI, PACU, RRED, EDMIN, EDMAIN

       

      to:

      UserName        Units

      Smith, John     4C

      Smith, John     TSI

      Smith, John     GMU

      Smith, John     GSU1

      Jones, Sam      TSI

      Jones, Sam      PACU

      Jones, Sam      RRED

      Jones, Sam      EDMIN   

      Jones, Sam      EDMAIN

       

      The # of units per user varies from 1 to 17 on a single line, separated by comnmas. I have trapped all units in a single field, then used the split function to parsed out the individual units into 17 columns, exported to MS Access, create a table with UserName and Units as the only 2 fields then append each of the 17 columns to the unit field of the new table. It works but it ain't too elegant. I dont think I can use MCR because the length of the units is not consistant.

       

      thanks,

      jim

        • Rows to Columns
          Grant Perkins

          Originally posted by ozzbug:

            I dont think I can use MCR because the length of the units is not consistant.

           

          thanks,

          jim /b[/quote]Jim,

           

          I think I need to consider this again in the morning (Nearly midnight UK time) with a fresh head on but it did occur to me that you you might well be able to use the PREP utility (Or Monarch Utility in V8 releases) to convert the CSV format to fixed width columns and then hit the re-formatted 'report' with the MCR idea which, I reckon, is the neatest way to get what you want.

           

          Or use your existing model but instead of exporting to export to a fixed field width report rather than Access and then run the second (MCR) model on the resulting file.

           

          Another though is to MSRP (Monarch Utility again) the original file and convert the commas to CRLF and similar with line positioning  useful for unambiguous trapping (Not sure about the UNIT: part yet) and then trap all the Unit Code lines as the detail lines and the rest as appends.

           

          Something like that should work I think.

           

          I'll see if I get any more ideas tomorrow and post if I do.

           

          HTH.

           

           

          Grant

          • Rows to Columns
            ozzbug _

            Grant,

            Thanks for responding. I tried using the prep utility w/o success. I think the problem is that the input file is not csv but an Excel worksheet saved as formated text(space delimited)*.prn. In the original Excel file all units for a single user appear in a single cell

            Also I neglected to include in my example a user assigned to a  single unit. There is no comma as a delimiter - see below.

             

            Smith, John

            Units: 4C, TSI, GMU, GSU1

            Jones, Sam

            Units: TSI, PACU, RRED, EDMIN, EDMAIN

            White, Benjamin

            Units: 4C

             

            thanks

            jim

            • Rows to Columns
              Grant Perkins

              Hi Jim,

               

              I spotted the potential problem with single unit but decided there would be a way around that if it was a problem.

               

              I suspect the Utility may not have quite all the features I recall from earlier separate programs in terms of file manipulation but never mind, there are ways around that - read on.

               

              Also the MCR has some interesting traits that can be worked around if you know how they complicate things - as is the case here where we want the columns to apply to alternate lines.

               

              So, how does this sound;

               

              Firstly run the Utility and make a change for the ':' (after units and assuming this does not occur for any other purpose - if it does you may need to make the string inclusive of "Units" as well) to  ':,,'

               

              This will have the effect of adding the 'missing' comma and in a moment creating an extra column. (If you need more column shift add more commas.)

               

              Save the modified version of the file where you can find it with a new name.

               

              Now open the new file as a database in Monarch Pro (or as a csv file in Excel).

               

              For the NAME rows.

               

              Column 1 will contain the Surname or "Units:"

              Column 2 will have the first name.

               

              The rest of the columns will be blank - unless you have 3 or more names in which case add the extra commas mentioned above and re-run tocreate a new file.

               

              For the UNITS: rows the first 2 columns will be blank and then you will see the Unit values.

               

              Set the column widths for the UNITS columns to be all the same and a size which will accomodate the widest codes. (Applies to Monarch and Excel.)

               

              Set the column widths for the Names to be wide enough that there will naver be an overlap with the first UNITS column. More on this in a moment.

               

              Save the Monarch model for future use ...

               

              Export the resulting table to a fixed width text file remembering whether you have added the extra space to the columns on export (Export Options).

               

              Open the newly created file as a report in Monarch.

               

              Add a PAGE HEADER template to capture the NAME lines by trapping on the SPACES that will appear on the line after the names.

               

              Set the MCR to be active and define the number of columns you require (max number of unit codes per NAME - there are limits in V7, less restrictive in V8). Set the MCR start to the beginning of the third report column (or fourth if you have had to allow for an extra name, etc.) and the width to what ever you set for the column widths in Monarch or Excel. (Plus 1 if you added a space on export.)

               

              Now in the MCR definition set the column start to be after your Page Header template and the end to be before the page header template. This should give you a one line MCR with each NAME giving a new page. The PAGE HEADER should give you the Surname and First Name fields.

               

              The table should now give you the data in the format you require, albeit with some blank lines where the NAMES have only some code in the UNITS columns. Filter these by looking for populated fields only.

               

              It is more complicated to try to describe in words than to do it. Try the substitution using the utility. If you get that OK but get stuck on the model I can send you the sample files I have for the converted data format and the model that reads it. Can't think of a way to do that with the Utility work.

               

              The key to the model is to ensure that any data for the appends which is NOT PART OF THE COLUMNAR DATA does not overlap the MCR. (Unless, for example, it is a column header that can be used IN THE MCR as an append field - a special case.) I have found that non columnar data which overlaps the MCR area can be a bit problematic to separate cleanly so by far the easiest way to deal with it in this example is to ensure you eliminate the overlap as part of the re-formatting of the report.

               

              Anything that is not clear (quite likely the way I write stuff) or leaves you with more questions just let me know and I will try to clarify.

               

              There may be another more sophisticated approach but this idea is quite a useful concept as a catch all for this generic type of problem.

               

              HTH.

               

               

              Grant

               

              Edit: for typos ...

               

              [size="1"][ August 15, 2006, 02:47 PM: Message edited by: Grant Perkins ][/size]

              • Rows to Columns
                ozzbug _

                Grant,

                Your solution worked without a hitch on the first attempt! This is a generic problem that we have encountered with other reports from this vendor so your solution will enjoy frequent use.

                I've lurked on the forum for a couple of years and have enjoyed reading your responses. You are amazing!!

                Thank you for taking the time to help out - I appreciate it.

                Going back in to lurk-mode.

                jim

                • Rows to Columns
                  Grant Perkins

                  Jim,

                   

                  I'm glad it worked out. You will likely spot some refinements to apply as time passes or find another approach that will work without the need for manipulating the file - though at the moment I can't think of one that would be less complex than the preparation for MCR use.

                   

                  Many thanks for your kind comments, though I have to say that my suggestions are no match for some of the solutions I have seen presented at the Datawatch User conferences by Mike Urbonas, Gareth Horton and the DW team along with some remarkable successes from the user community.

                   

                  If you get a chance to attend a conference I would strongly recommend it. If that is not possible keep an eye open for offers of a copy of the conference CD. Always interesting and insightful. The first one I had access to opened up an entirely new way for me to work with Monarch.

                   

                  Have fun.

                   

                  Grant