12 Replies Latest reply: May 15, 2014 10:05 AM by adonis _ RSS

    inserting a header and trailer in output file

    adonis _

      curious, is it possible to create a header above the normal header in an output file and also insert a trailer at the bottom?

       

      Being asked to create a header file above the csv format I created and a trailer at the bottom.

       

      please let me know if possible.

       

      thank you all for your help, much appreciated.

        • inserting a header and trailer in output file
          Data Kruncher

          Although I haven't tried it myself, you ought to be able to run a couple of Monarch project exports to be able to do this. I don't think that you can do this with a single Monarch model/project.

           

          The first export would append your data output to an existing header text file. The next would append the trailer text file, or footer, to the result of that export, resulting in a final file.

           

          Barring that you could always use a DOS batch file to tie it all together. The following would concatenate three individual files (A, B, and C text files) into a final file D text file:

          copy A.txtB.txtC.txt D.txt[/code]

            • inserting a header and trailer in output file
              Olly Bond

              Hello Adonis,

               

              If you want to output data with header and footer, you can do this using the Summary window.

               

              Define the first key column as the field you want in your header, and check the boxes for this to be hidden column. Also check the box on the general summary tab to show a total - this will give you your footer.

               

              If you have particular requirements to force particular data into these positions, it can be done using some calculated fields and filters prior to the summary.

               

              Best wishes,

               

              Olly

                • inserting a header and trailer in output file
                  adonis _

                  Thanks Olly

                   

                  question is I want to create a header and trailer that is not available in the data set that I have.  The data is from a database type of table, which I would be appending a header and trailer.

                  would the same still hold true?

                    • inserting a header and trailer in output file
                      Olly Bond

                      Hi Adonis,

                       

                      Yes, as long as you can define the data you want logically, you can get the result you want in Monarch.

                       

                      To join the other data in from another table, you'd need to have Monarch Pro, and you'd use an external lookup.

                       

                      Best wishes,

                       

                      Olly

                        • inserting a header and trailer in output file
                          adonis _

                          Thanks Olly

                           

                          I do have monarch pro for version 10, since I never attempted something like this, what is the best way to add the stuff together from your point of view?

                           

                          basically the data is in a table,

                          is it possible to do a total count of records too or is that asking too much also within the header trailer?

                          apologies for my noviceness.

                            • inserting a header and trailer in output file
                              Olly Bond

                              Dear Adonis,

                               

                              It's easy to make a count appear in the footer line of your export - simply add the function Count() as a Measure in your summary.

                               

                              Without seeing your data and having a clear understanding of what you want to do with it, I can't give you practical advice on how best to approach it.

                               

                              What I can give you, if it's any help, is reassurance that you can do it in Monarch. I've not seen any data yet that couldn't be handled.

                               

                              Best wishes,

                               

                              Olly

                                • inserting a header and trailer in output file
                                  adonis _

                                  Thanks for the feedback Olly

                                  Olly not using summary table, basically i transformed the database data, from a csv format into table did some simple field insertions with some formulas based on what the business user wanted.

                                   

                                  That was all that was needed

                                   

                                  so you are telling me that i need to create a summary to do this which I was trying to avoid.

                                    • inserting a header and trailer in output file
                                      adonis _

                                      i thought also that external lookups was to add columns.

                                      I wanted to put a header on top of the data and a footer below the data.

                                      please advise

                                        • inserting a header and trailer in output file
                                          Data Kruncher

                                          You're correct: external lookups add fields to the table.

                                           

                                          I suppose what we should have asked originally is: What type of content do you wish to include in the header and footer blocks? Will they just be descriptive text - like regular paragraphs - or will they be a mix of labels and statistical or summarization amounts, based on the data in the body of the report? Or something else?

                                            • inserting a header and trailer in output file
                                              adonis _

                                              The header & Trailers are below,

                                              unfortunately gets skewed some in this web page but here is what it looks like.

                                               

                                               

                                               

                                              Field Name           Format      Rules     Col        Notes

                                              Header Control Record                    

                                              Rec Type     Char (3)     Man-1                  1     HDR

                                              COB Date     Date     Man-1                  2     YYYYMMDD actual Business date               

                                              Data Source Code     Char (6)     Man-1     10     i.e. CMKTC6

                                                                   

                                              Trailer Control Record                    

                                              Rec Type     Char (3)     Man-1     1     TLR

                                              Feed Control Count     Number (5)     Man-1     2     Total # of Data Records/rows               

                                              Data Source Code     Char (6)     Man-1     10     i.e. CMKTC6

                                                • inserting a header and trailer in output file
                                                  Data Kruncher

                                                  Best equip yourself with your favorite caffeinated beverage now, I'm afraid.

                                                   

                                                  The good news is that by using only Monarch and a little batch programming, you can do what you want.

                                                   

                                                  There are a few things that you'll need to lay the ground work for this.

                                                   

                                                  You'll need three individual Monarch projects to export the COB Date, the data source code and the number of data records. Each of these will export only the relevant value, without the field name, to a text file. Export to cobdate.txt, sourcecode.txt and recordcount.txt respectively.

                                                   

                                                  For the COB Date and the Source Code I imagine that you can export from the table window easily enough. For the record count, you'll want to create a summary. You may have to turn around and import the summary export again so that you can export a single value to a text file, this time from the table window. Maybe filter on the total, and hide the total name, whatever it is. The end result that you're after is a text file that contains only a number.

                                                   

                                                  Next, create a text file that contains a single word. It doesn't matter what it is. You'll use this as the record source, but you'll hide the word in the table, so it's irrelevant. You're only using it to create a single record for a model.

                                                   

                                                  This becomes the input source for the first model, the "Header" model/project. Capture the word, then go to the Table. Add a calculated runtime parameter field named CodeChar. For now give it a value of "TEST".

                                                   

                                                  Create a calculated Memo field named "Header", and give it this formula:

                                                  "Field Name Format Rules"chr(13)

                                                  "Header Control Record"chr(13)

                                                  "Rec Type Char (3) Man-1 1 HDR"chr(13)

                                                  "COB Date Date Man-1 2 YYYYMMDD"chr(13)

                                                  "Data Source Code Char (6) Man-1 10 i.e. "+CodeChar

                                                  /CODE

                                                   

                                                  Notice that I didn't work on the YYYYMMDD; I'll leave that for you to tackle for now. Hide the captured field from the report, and hide CodeChar.

                                                   

                                                  Go to the Options, Export and Clipboard and turn off the option to export field names as the first row of output.

                                                   

                                                  Create a new project export to output this field to a file named "Final Report.txt", fixed-length text, and set the overwrite option. Save the model and the project. Header.xmod and Header.xprj.

                                                   

                                                  Now for the next project file, Body.xprj, use your existing model to output all of your data to the "Final Report.txt", fixed-length text, using the append option.

                                                   

                                                  For the last project file, create Footer.xprj using your single word text file as the input source. Again, that value will be ignored.

                                                   

                                                  In the Table window, create two runtime parameter fields, RecordCount and CodeChar. Now create a Memo calculated field with this formula:

                                                  [SIZE=2]

                                                  "Trailer Control Record"chr(13)

                                                  "Rec Type Char (3) Man-1 1 TLR"chr(13)

                                                  "Feed Control Count Number (5) Man-1 2 "trim(str())chr(13)+

                                                  "Data Source Code Char (6) Man-1 10 "+CodeChar

                                                  /SIZE[SIZE=3][/CODE][/SIZE]

                                                  [/SIZE]

                                                  Set the option to not export the field names, and define a project export to again append to the "Final Report.txt" file.

                                                   

                                                  That's the hard work done.

                                                   

                                                  Now, nobody wants to jump through all of those hoops every time an update is required, so let's automate it.

                                                   

                                                  Fire up Notepad, and assuming you stored all of these files in C:\mytest (edit the script below as necessary), paste in this bit:

                                                  c:

                                                  cd \mytest

                                                  start "Pass1" /wait "C:\program files\monarch\program\monarch.exe" /prj:cobdate.xprj /pxall

                                                   

                                                  start "Pass1" /wait "C:\program files\monarch\program\monarch.exe" /prj:sourcecode.xprj /pxall

                                                   

                                                  start "Pass1" /wait "C:\program files\monarch\program\monarch.exe" /prj:recordcount.xprj /pxall

                                                   

                                                  set /p sourcecode=<datasourcecode.txt

                                                   

                                                  start "Pass1" /wait "C:\program files\monarch\program\monarch.exe" /prj:header.xprj /set:codechar=%sourcecode% /pxall

                                                   

                                                  start "Pass1" /wait "C:\program files\monarch\program\monarch.exe" /prj:body.xprj /set:codechar=%sourcecode% /pxall

                                                   

                                                  set /p records=<recordcount.txt

                                                   

                                                  "C:\program files\monarch\program\monarch.exe" /prj:footer.xprj /set:sourcecode=%sourcecode% /set:recordcount=%records% /pxall[/CODE]

                                                   

                                                  Now save the file as "FinalReport.bat".

                                                   

                                                  Provided everything's been built and each of the exports defined properly then executing the batch file should build your report for you. You may have to edit the program paths to suit your installation.

                                                   

                                                  Each of the "set /p" commands creates a variable in memory using the values that are exported to the small text files. Those variables are then passed to Monarch using the /set command line parameter.

                                                   

                                                  It is a number of steps, but the requirements you've laid out are somewhat complex as far as using Monarch goes, IMO.

                                                   

                                                  HTH,

                                                  Kruncher