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

    Open Database - command line?

    debijo _

      I have a csv file with 35 variable length columns.  I first tried prep to process, but the variable length caused me to continually change my model.  So, after searching this board, I find out about the open database method.  It works great.  Now I want to automate it in a command line script.  But I can't find the command syntax.  I have to process this file every month and I hate the thought of having to do one manual step each time.  Any ideas?

        • Open Database - command line?
          Grant Perkins

          Hi debijo,

           

          Firstly, which version of Monarch do you have?

           

          Secondly, have you saved both the model for your cvs file and the Project that ties everything together to ensure that you open the project and the model in one step?

           

          If so I assume you are just looking to automate the running of the project and the resulting output(s). (If there are several outputs version 7 will be beneficial - hope that's what you have.)

           

          Is the file name always the same or does it vary from run to run? If it varies, does it do so in a way that can be predicted and programmed or will you need a method of specifying the file name as part of the automated process?

           

          I think that's about all the questions for now. Knowing the answers to them will help to point you towards the methods you need.

           

          Have fun.

           

          Grant

           

          Originally posted by debijo:

          I have a csv file with 35 variable length columns.  I first tried prep to process, but the variable length caused me to continually change my model.  So, after searching this board, I find out about the open database method.  It works great.  Now I want to automate it in a command line script.  But I can't find the command syntax.  I have to process this file every month and I hate the thought of having to do one manual step each time.  Any ideas? /b[/quote]

          • Open Database - command line?
            Bob Cline

            It's the incredible shrinking and expanding text file problem!

             

            I do most of my work with DOS batch files, so once this is setup -- it's easy to maintain.

             

            As you have discovered, Prep creates an exported .txt file with numeric fields as large as the largest field in the input file -- it is possible to produce columns of different widths each time you run Prep.

             

            Create a dummy .csv file with one record that contains column headings that match your .csv file.  Create a second record (CR/LF) with a sample record that has maximum size fields (both numeric and text).  Make one of the fields in the record unique i.e. 'ZZZZZ' so that you can filter the record out later.

             

            Concatenate the maxfield.csv with your input.csv file using the COPY command:

             

               COPY maxfield.csv+your.csv combined.csv

             

            Run the combined file through Prep:

             

               PREP combined.csv combined.txt AD

             

            Yielding: combined.txt, a consistent .txt output that can be easily modeled.

             

            Then, in your model, define combined.txt and eliminate the one maxfield record that you created.

             

            I use 'ZZZZZ' in a key field to id the maxfield record for filtering.

             

            Hope this helps you out.

             

            Charleston

            • Open Database - command line?
              debijo _

              I'm running V7.01.  The .csv file is the same name each time.  And yes, I saved the model & project. 

               

              Thanks for the prep advice.  I thought about adding a record with the maximum column width for each.  However, with the nature of this file I cannot ever be sure what the maximum field length would be.  I actually thought I had it last time, but when I ran it, it was off again.  I'm tired of messing with it.  The database option looked alot cleaner and more reliable.

               

              Thanks again,

              Debi

              • Open Database - command line?
                Grant Perkins

                Hi Debi,

                 

                Looks like you have sussed the way to automate the process by making the command run the project but have a bit of a problem with the field sizes still?

                 

                Not much a of way around it but to try to pin down the max width of the fields (or set to the maximum ?) and bear in mind that the display width in the model may be less than the data width so may need looking at.

                 

                If you have really big fields with lots of text you may need to look at using MEMO fields.

                 

                Does anyone have the data structure information, perhaps the schema, handy? Any database or report will usually have known maximum field sizes associated with it. Of course you probably already know that  - but I thought I had better mention it in case someone has misled you about such availability somewhere along the line!     

                 

                Grant

                 

                Originally posted by debijo:

                I'm running V7.01.  The .csv file is the same name each time.  And yes, I saved the model & project. 

                 

                Thanks for the prep advice.  I thought about adding a record with the maximum column width for each.  However, with the nature of this file I cannot ever be sure what the maximum field length would be.  I actually thought I had it last time, but when I ran it, it was off again.  I'm tired of messing with it.  The database option looked alot cleaner and more reliable.

                 

                Thanks again,

                Debi /b[/quote]

                • Open Database - command line?
                  debijo _

                  I gave up on the prep.  My fields would just change and I'd have to redo everything.  The open database works great.  If only I didn't have to do that piece manually.  I have my script now just opens a monarch session and will wait until I close it.  So, while it's open, I click the open database and model then do the export.  It works, just not as automated as I would like it.  It's a shame there is not a /dtb: like there is the /rpt:.  Oh well.  Maybe someday???

                  Thanks,

                  Debi

                  • Open Database - command line?
                    Grant Perkins

                    Debi,

                     

                    Just thinking about the maximum field size thing again, if you can work out what max sizes are likely to be you could create a dummy first line for the file (in another file) and simply concatentate the 2 'files' using the DOS "copy" command in the script (in the right order of course) as part of the process. The max field sizes should then establish the field sizes for the import. Then just filter out the dummy line ...

                     

                    I know some of us have been able to use that idea successfully in the past. Might be worth a try.

                     

                    Grant

                     

                     

                    Originally posted by debijo:

                    I gave up on the prep.  My fields would just change and I'd have to redo everything.  The open database works great.  If only I didn't have to do that piece manually.  I have my script now just opens a monarch session and will wait until I close it.  So, while it's open, I click the open database and model then do the export.  It works, just not as automated as I would like it.  It's a shame there is not a /dtb: like there is the /rpt:.  Oh well.  Maybe someday???

                    Thanks,

                    Debi /b[/quote]