7 Replies Latest reply: Apr 4, 2017 10:45 AM by Grant Perkins RSS

    Have pipe separated values in .txt file. How to define Model?

    Bill Deville

      I have a text file that has columns separated by the "|" (pipe) character. Some fields are variable in length, so the columns following are not in the same place as the row above or below. This is probably a beginner question. I want to define a model that recognizes the text file similar to a .csv file. Am using  Monarch Pro V11.2

        • Re: Have pipe separated values in .txt file. How to define Model?
          Steve Caiels

          Hi Bill,

           

          You should be able to open it as a database.

           

          From the File-Open Database menu, browse to the location, then specify the pipe as the delimiter along with any other options such as 'skip lines', column headings etc.

           

          This should work fine if it's a neat 'csv' file.  If you need to use more traditional Monarch techniques such as Appends, Footers, Multi Line etc, then the floating trap should help.  You'd need to select the alternate Trap Characters set as the pipe is reserved for the Numeric OR trap.  And you may need to extend the field template width to take account of fields longer than your sample.

           

          If the "Open database" option doesn't work for you, please post back and I can corroborate on the floating trap option.

           

          Regards,

          Steve.

          • Re: Have pipe separated values in .txt file. How to define Model?
            Chris Porthouse

            Although I verified this on Monarch v14, I am pretty sure this functionality existed in your version as well.  Simply open your report as a database and then you should be able to define and import the fields, selecting the pipe (|) character as the delimiter.

                • Re: Have pipe separated values in .txt file. How to define Model?
                  Grant Perkins

                  Hi Bill,

                   

                  With V11 (And indeed earlier) version of Monarch the typical way to open a pipe delimited output would be as a "database" just as Steve and Chris suggested. Think of it as a csv file with a different separator to choose and that should be it. You may need to redefine a few field types if the auto assessment settles on character instead of numeric or vice versa, for example.

                   

                  On very rare occasions a pipe separated file of records may be something better handles as a standard text report (hence the suggestion of floating traps).

                   

                  Even extremely rare situations the incoming information might benefit from some sort of combination of approaches. But if you have got to that point of need you are likely dealing with something rather extreme in its input!

                   

                  HTH.

                   

                  Grant

                    • Re: Have pipe separated values in .txt file. How to define Model?
                      Bill Deville

                      Thanks Grant,

                      That makes sense. I need to read up on 'floating traps'. That may be the best answer. Steve Caiels also mentioned it.

                      I'm not sure where to define a floating trap. I'll search the index. I'm sure it must be easy. I have recently defined several models and it went pretty smooth.

                       

                      This is turning out to be a pretty good resource.

                        • Re: Have pipe separated values in .txt file. How to define Model?
                          Grant Perkins

                          Hi Bill,

                           

                          The basic rule is if your trap line is a variable  - so csv type files and things like computer log files  - it's probably going to be fairly obvious that if you are in the trapping game and the traps and fields are probably going to need to be moving horizontally on a line by line basis.

                           

                          There are some "best practice" concepts that are worth deploying in those circumstances that may not mean much until you actually need them!

                           

                          These things tend to work pretty well with single line report records.

                           

                          When a report is more of a mess (although in terms of human readability it can be OK), teaching a machine to read it is more challenging -  something not at all uncommon with "report writer" style output delivered in PDF sources for example.

                           

                          The floating trap concept can also be deployed for this situation but may very likely require some special "engineering" to maximise the results. It would typically be a part of a wider solution.

                           

                          Once again that is something be addressed if and when you need to do so.

                           

                          Enjoy your modeling!

                           

                           

                          Grant