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

    10.5 Truncating Data

    lcwalker _

      Hello all.  I have a file with approximately 174k rows of purchase order data.  Our PO numbers are system generated at 7 digits.  However, our capital purchases are manually designated with the format F12-ABC-01.  When opening my database (pipe delimited text file)  Monarch is truncating this entire column to 7 digits.  So our capital PO numbers are appearing as F12-ABC which is meaningless in our sequential numbering system.  Any ideas how to correct this?  10.5 does not offer a way that I've found to specify field length during the open database function.

        • 10.5 Truncating Data
          KeyserSoze _

          You can adjust the field properties in the Field List dialog. Set the Data Length to 10 and OK the Field List, then save your model and project files.

           

          Monarch sets the default data length values by scanning the first 250 records of the data source upon import and using what is required for those records.

            • 10.5 Truncating Data
              lcwalker _

              You are a life saver KeyserSoze.  I had been adjusting the display width via the right click rather than the field list data length.  Very nice, thank you!

              • 10.5 Truncating Data
                elginreigner _

                You can adjust the field properties in the Field List dialog. Set the Data Length to 10 and OK the Field List, then save your model and project files.

                 

                Monarch sets the default data length values by scanning the first 250 records of the data source upon import and using what is required for those records.[/QUOTE]

                 

                The scanning of the first 250 records has it's pros and cons as you are seeing. I have always wanted more control over this during database file setup instead after the fact.

                  • 10.5 Truncating Data
                    Olly Bond

                    Hello Elgin,

                     

                    I might be dreaming, but I have a feeling that there is a registry key with this value which would allow you to increase it, subject to taking a possible performance hit...

                     

                    Best wishes,

                     

                    Olly

                      • 10.5 Truncating Data
                        Grant Perkins

                        Reading a real database would not normally give this sortof problem - the database definition should be picked up and field ought to be the correct sizes.

                         

                        Reading a formatted text file as a database is another matter. It really depends on the reliability of full representation the 'sample' provides.

                         

                        The safest approach is to ensure that the model development is undertaken with a properly representative file for input. Of course that can be difficult to confirm! No one ever seems to know what the structure may look like.

                         

                        Get the first line of the input file right and there will be no problems. Or simply establish what can be established using the 'report' to create the basic model and then tune as needed using the Field List.

                         

                        There's really no reliable way to be certain that all possibilities are covered from exiisting samples and whatever may later come down the supply line unless you can obtain a spec of the field definions for reference (and get updates too when and if they are required.)

                         

                        Grant

                        • 10.5 Truncating Data
                          elginreigner _

                          Hello Elgin,

                           

                          I might be dreaming, but I have a feeling that there is a registry key with this value which would allow you to increase it, subject to taking a possible performance hit...

                           

                          Best wishes,

                           

                          Olly[/QUOTE]

                           

                          It would be nice if there is a registry key for this. I've been using Excel to help determine the maximum length of the fields for very large files.

                            • 10.5 Truncating Data
                              Grant Perkins

                              It would be nice if there is a registry key for this. I've been using Excel to help determine the maximum length of the fields for very large files.[/QUOTE]

                               

                              Have you experimented with the Verify feature?

                               

                              (Notably Table Verify[/B] from version 10.5 onwards.)

                               

                              Grant