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

    Monarch Pro Newbie - Open Database

    Richard Corby

      Just upgrade to Pro in order to use .txt files without needing to use Prep.  Perhaps I'm missing something, but everything comes in as a "character" vs. numbers.  The file is comma delimited and has quotes around each field.

       

      How do I get numbers?

        • Monarch Pro Newbie - Open Database
          Grant Perkins

          Richard,

           

          The import will try to ascertain if it needs to make a column alpha or numeric. To decide on numeric ALL fields in the first XXX rows (I think it checks 250 rows if my memory is correct) must be numeric, otherwise it will default to alpha for safety. You can always change that in the model definition and then simply save the model and apply it each time you open another issue of the file.

           

          That said does you input file have a column header row? If so, and assuming that the headers are alpha and are in the first row, how have you set the 'First Row Contains Column Names' tick box on the Sample Data screen? If you have not ticked it the row will be included as 'data' thus suggesting all data fields are to be Character rather than Numeric (or Date.)

           

          There may be some other reason but this would be the first thing to check I think.

           

          Hope this helps.

           

          Grant

           

          Originally posted by Richard Corby:

          Just upgrade to Pro in order to use .txt files without needing to use Prep.  Perhaps I'm missing something, but everything comes in as a "character" vs. numbers.  The file is comma delimited and has quotes around each field.

           

          How do I get numbers? /b[/quote]

          • Monarch Pro Newbie - Open Database
            Richard Corby

            The "First Row" check box is checked.  The data however is mixed between alpha and numberic.

             

            Perhaps I don't understand the process.  When I used Prep, I would open a data file, apply a model, then create summaries.  I then saved each summary as a project, and then just opened the Project.

             

            What are the basic steps with Pro and opening a Database?  Do I Open Database, Create a Model, then do Summary, etc?  Can I use my existing models with the Database vs. the old Prepped Text File?

             

            Thanks.

            • Monarch Pro Newbie - Open Database
              Grant Perkins

              Richard,

               

              OK, basic possibilities checked and now we can proceed to the more 'flexible' aspects.

               

              I guess the simplest way to think of this is thet the first time you open the file as a database it's a bit like doing the PREP definition. The only difference is that the MS methods attempt to work out the field structures and lengths for you son you don't have to do it yourself. Naturally that is not always a perfect result.

               

              However, once you have identified the appropriate seperator and the columns to be imported look about right, don;t worry too much about the definition of the field type, just accept what is there, complete the loading process and save the model that you have just generated using the load process.

               

              Now you can go to the table view and make any changes required. Using Field List is the fastest way if you know what you need to do.

               

              So you can change the definitions of the fields (mostly) but remember that you will get some anomalies if you have mixed data in the same column. You may also need to consider whether the columns are at the maximum width you will ever be likely to need. De-limited files may not contain data to the maximum width of a field in every instance of the file. Not a problem unless your master model ends up with the wrong sizes.

               

              Once done, save the changes.

               

              (Calculated fields and so on can be created at this point. Also external lookups if required.)

               

              You can also save the Project which will not create a connection to the Data file/Database at its current location and the Model file wherever it is.

               

              Next time you want the same database or the latest iteration of the file (with the same name) just open the Project.

               

              If you don't have a project just open the data file/database and then apply the model you created previously before completing the load.

               

              Likewise if you have a new file or database with the same staructure (as far as the model is concerned at least) just open that as a database and apply the previously created model.

               

              It can all look rather daunting at first sight but is really quite straightforward once you have been through the process a few times and it starts to become familiar.

               

              As for using the previous models, hmm. Not had to consider that and not tried it. I would doubt that it is possible but you could try creating one new process model and then comparing the two model files (assuming you save as .mod rather than .xmod) using an editor. I would be surprised if there was much that could be used safely for the new model, part of the reason being that the way the data to field name connection is defined is unlikely to be compatible.

               

              However the orignal model might make a good basis of information to use with the Field List to apply any changes required to the new model.

               

              Calculated fields and similar should be transferable using WorkStore if you have it though you may find a few anomalies that would need to be corrected.

               

              I hope this helps you to move forward. I'm happy to provide further input if it would help.

               

              Grant

               

               

              Originally posted by Richard Corby:

              The "First Row" check box is checked.  The data however is mixed between alpha and numberic.

               

              Perhaps I don't understand the process.  When I used Prep, I would open a data file, apply a model, then create summaries.  I then saved each summary as a project, and then just opened the Project.

               

              What are the basic steps with Pro and opening a Database?  Do I Open Database, Create a Model, then do Summary, etc?  Can I use my existing models with the Database vs. the old Prepped Text File?

               

              Thanks. /b[/quote]

              • Monarch Pro Newbie - Open Database
                Richard Corby

                When I go to the Field List, I cannot change any of the data types ..character is all that is available.

                 

                Also, can you elaborate a bit regarding the field widths?  My main reason for upgrading to Pro was because of field widths - they do change from day to day - I assume Open Database would accomodate me?

                • Monarch Pro Newbie - Open Database
                  Grant Perkins

                  Hi Richard,

                   

                  Your Field List problem is one I have only found, quite reasonably, when connecting directly to a database (as far as I recall). The way around it relies on creating alternative fields using calculated fields and the appropriate conversion functions.

                   

                  I got the impression you are using comma delimited sources with all the fields 'quoted', in which case it should be possible to change the definitions. At least that is what I have found so far. I have more often used TAB delimited files and certainly there are no problems with changing the definition once the model has been created.

                   

                  I must be missing something. If it's releasable and not too large I will happily have a look at a file sample and see what result I get.

                   

                   

                  Field widths.

                   

                  A delimited file will automatically set to the maximum field width uncovered in the data file loaded and that will often be fine. But if you then save that model the field widths in the model will be set for all future uses of the model. Both 'template' and display sizes. (This of course is also true of models for reports!)

                   

                  It may be that the report has, say, a selection of records with a particular code that is most commonly 2 characters but sometimes 3. Or a name or description field which in the original database could be up to 40 chars but in the initiating report sample does not exceed 25 chars.

                   

                  If you save the model the field will be set to 2 chars and 25 chars respectively by the automatic analysis. You may want to change that (maybe even make the fields smaller sometimes?) to allow for diffent data field sizes on subsequent reports.

                   

                  Checking the sizes of the important fields against the field specification in the originating database (or data extraction program in the extraction tool used), if available, is often a worthwhile step to take. It doesn't usually take long once the information is available. But getting the information can be another matter all together ...  !

                   

                  I hope this helps some.

                   

                   

                  Grant

                   

                  Originally posted by Richard Corby:

                  When I go to the Field List, I cannot change any of the data types ..character is all that is available.

                   

                  Also, can you elaborate a bit regarding the field widths?  My main reason for upgrading to Pro was because of field widths - they do change from day to day - I assume Open Database would accomodate me? /b[/quote]

                  • Monarch Pro Newbie - Open Database
                    Grant Perkins

                    Hi Richard,

                     

                    I have had another look at this and remembered that reason. Unlike Excel (or at least the version I have on my system) Monarch, when reading a csv file as a database, fixes the field type as character if the data is contained in quotation marks. Remove the marks from all row entries for that field and it will set an appropriate field type and allows changes. I wonder if there is a regitry setting somewhere that alters that behaviour.

                     

                    So, that is the probable answer, based on you previous description. The solution to the problem is another matter.

                     

                    I gues the options include:

                     

                    Try to obtain a different version of the output file which does not encapsulate all the fields in quote marks (should be optional to only "quote" pure text fields ... providing that they are identified as such).

                     

                    Pre-process the file to remove all or selected quote marks.

                     

                    Accept what comes in and use calculated fields to work around the problem.

                     

                     

                    I think I would favour them in that order.

                     

                     

                    Over to you for now.

                     

                    Grant

                     

                    Originally posted by Richard Corby:

                    When I go to the Field List, I cannot change any of the data types ..character is all that is available.

                     

                    /b[/quote]