7 Replies Latest reply: May 15, 2014 10:01 AM by Grant Perkins RSS

    How to trap comma-delimited data within report

    RonaDT _

      I have a report which is mostly fixed width, except for one field which includes somewhat comma delimited data like this:

                                                                                      [FONT="Courier New"]            

      CONCONT1          Company 111111        23532                                                  03/03/08   

                         P.O. Num: 16024                                                                       41100-10-30

                         Job Num: 10431-01($2,686.50), 10431-02($895.50)                     /U                                                                               



      CONCONT2          Company 2222222      23535                                                  03/04/08   

                         P.O. Num: 16025                                                                       41100-10-30

                         Job Num: 10122-01($3,089.75), 10122-01($2,385.95),                                               

                         10122-02($5,096.60), 10122-02($3,848.80)                 /U                                                                               

      41100-10-30 /FONT


      I can trap the whole Job Num field with it's varying number of lines (as underlined), but I'm really stuck trying to figure out how to break it out into individual lines. Ideally, each job number has it's own record, with the neighbouring dollar amount.


      I want to end up with something like:


      CONCONT1      10431-01     2,686.50

      CONCONT1      10431-02        895.50

      CONCONT2      10122-01     3,089.75

      CONCONT2      10122-01     2,385.95

      CONCONT2      10122-02     5,096.60



      Any help would be greatly appreciated.

        • How to trap comma-delimited data within report
          Grant Perkins

          Hi RonaDT and welcome to the forum.


          You have an interesting challenge there.


          My first thought would be to use Monarch to reformat the report as a first phase then use a second model in a second phase to extract the resulting 'easy to extract' data.


          If you are unlikely to have a very large number of Job Numbers (say not more than 40) you could split your extracted multiple job number field using LSPLIT and the comma as the split character and create a number of new columns. Hide the original, export the new 'version' of the report and then run a separate model on the newly output file that uses Multi Column Region functionality to treat each job number/amount as a complete field in each column as a detail record. The number and amount can be separated in the final stage.


          Another option might be to pre-process the file with the Monarch utility (or another suitable application for the purpose) and replace the separator commas - i.e. ")," - with carriage return/line feed combinations ")CR/LF" to put each job number on its own line. Then run a second model against that. Basically the same idea but kicked off a different way. However you would need to be sure that there were no other commas in the file that would get changed in the process ...


          I can think of a few other possible approaches if the number of job numbers is large and exceeds a reasonable MCR solution limit but none right now that do not require at least 2 steps to make the job numbers into individual records that can be used as detail records. So to keep things as simple as possible at this stage I will wait until you had a chance to consider these suggestion before seeking other approaches.


          Perhaps someone else can offer the killer solution?





          Grant Perkins

            • How to trap comma-delimited data within report
              Data Kruncher

              I was experimenting the Monarch Utility a bit while Grant replied, with unusally poor results. Instead of replacing the ", " sequence - to keep the trailing parenthesis - the Utility deleted the remainder of the line after where the comma was. ???


              The beauty of simple text files is that you can always process them (search/replace) with MS Word. Just open your report in Word, and search and replace the comma space combination with a paragraph mark (a hard return).


              This, as Grant discussed, will give you a Monarch friendly file.


              It's a bit of extra work, but it'll make the job possible.

                • How to trap comma-delimited data within report
                  Grant Perkins



                  One might hope that the line deletion would not be the case with the real file. There are alternative editors that would be quicker than Word, as I know you know. There are also some powerful replacement utilities around  - I know of one rather comprehensive one that is free.


                  However, I would favour the MCR route if the number of job numbers does not compromise its potential, simply because it removes any concerns about ")," or ", " combinations occurring elsewhere in the report. By keeping the changes  within known bounds it would be easier to monitor, automatically, for problems as well offering the potential for greater reliability.


                  I also have a sneaking suspicion that step two could be processed directly as an Excel activity based on the step one in Monarch - possibly without the field splitting requirement? But Excel is not my area of expertise at all si I will leave you to comment on that one!



                    • How to trap comma-delimited data within report
                      Data Kruncher

                      Regarding using Word vs. others, Word is simply a lowest-common-denominator, in that for better or worse, most all business users have it, and will other tools likely do things better or faster, Word works. As opposed to MS Works, which, the last time I saw it (admittedly many years ago), well, didn't work well.


                      I'll ponder the Excel bit...

                        • How to trap comma-delimited data within report
                          Grant Perkins

                          Fair point DK but having used it for a while some years ago for some formatting (on what was usually a smallish file) pretty much along the lines you suggested I have to say it is not something I would recommend if it is a task that will be repeated daily. Also does not really lend itself to batch file usage unless things have changed a lot, but then maybe the batch file requirement is absent here ...


                          Ok for prototyping whilst model building though.




                            • How to trap comma-delimited data within report
                              RonaDT _

                              Thanks very much for the ideas.  The comma idea might be problematic in that there is also a comma separator for the thousands in the number formatting. That formatting is built into the program and can't be changed (without paying for a programming change, that is).  I'll play with the second idea for a bit & post back with my results.




                                • How to trap comma-delimited data within report
                                  Grant Perkins

                                  Hi Rona,


                                  Yep, spotted the potential complication of the thousands separator but forget to return to update the post. Apologies for that.


                                  At the risk of the formulas becoming a little (but only a very little) more complicated you could split on ")" rather than the comma and then add back the ")" to the result for data before the split and remove the ", " for data after a split point depending on how you have set it up.


                                  Remember you can manipulate the results as many ways and as many times as you need to for the result.