4 Replies Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Field Truncating Problem

    Bradl Vogl

      I am opening a .CSV file of which I have a few lines copied below.

      The results in Monarch truncates fields as shown.

      CABGR-362-001 becomes CABGR-362-

      The field setting in Monarch is 17 characters. It truncates all data to 10 characters.

      Can anyone please help? 

       

      [font="courier"]2580,128038,CABGR-362-001,1,2/15/2007 9:43,2/15/2007 12:11,,WIRE PREP 1,FALSE,,,0

      2581,128038,CABGR-359-001,1,2/15/2007 9:43,2/15/2007 12:11,,PINNING 1,FALSE,,,0 /font[/quote][quote][font="courier"]

       

      Assembly #     Qty     Start Time     End Time     Process     S/O #     Time     UNIT TIME

      CABGR-362-     1     2007-02-16 9:43:00 AM     2007-02-16 1:39:00 PM     ASSEMBLY 2     128038     14160     14160

      CABGR-355-     1     2007-02-16 9:43:00 AM     2007-02-16 1:39:00 PM     ASSEMBLY 3     128038     14160     14160

        /font[/quote]Thanks,

      Brad Vogl

        • Field Truncating Problem
          Data Kruncher

          Hi Brad,

           

          I'm assuming that you're reading the CSV file in as a database source.

           

          I think you might be running into the problem which occurs due to the way in which Monarch scans your data to determine the field type. There are a few posts in the forum wherein the speculation is that the first 250 rows or so get read to determine field type and width. Here's a  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000899#000001"]similar discussion[/url].

           

          I think Nigel had a possible solution for your problem in his reply within that post.

           

          I don't know a "fix" for you within Monarch at the moment.

           

          Is Nigel's suggestion a functional, if admittedly not ideal, solution?

           

          Kruncher

          • Field Truncating Problem
            Grant Perkins

            Hi Brad,

             

            Making the the same assumption as Kruncher about treating the file as a csv database, If I import the 2 sample lines everything is fine.

             

            The symptoms you have are, sort of, those that would suggest the field has been created as a Numeric with 17 characters but, unless very large numbers are present, only 10 displayed. Have you looked at changing the display width of the field/column in the table? Did anything change?

             

            I imported all columns (including the 2 to the left of your problem column). Those leftmost two imported as numeric, field size 17, display width 10.

             

            The odd thing is that even if the earleir rows all came out numeric for column 3 (and so set the sizes) you are actually displaying alpha characters. So the field cannot be numeric (is it?) unless something else is going on.

             

            What does the field list tell you about the field properties?

             

            HTH.

             

            Grant

             

            The odd thing is that

            • Field Truncating Problem
              Bradl Vogl

              Grant,

                 After looking at the field list I noticed the field being trubcated to 10 characters is a Character Field with 17 character view and also had the Data Length at 10 characters.

                 I changed the 10 to 17 and all was okay. It picked up the missing characters.

                 In field properties you can only change the ccharacter view, correct?

                 Where does Monarch pickup the data length in the first place?

                 I never had this happen in the past.

                 Thanks for your suggestion to look in the field list......all appears to be fine now.

               

              Brad Vogl

              • Field Truncating Problem
                Grant Perkins

                Originally posted by BradVogl:

                In field properties you can only change the ccharacter view, correct?

                   Where does Monarch pickup the data length in the first place?

                   I never had this happen in the past.

                   Thanks for your suggestion to look in the field list......all appears to be fine now.

                 

                Brad Vogl /b[/quote]Hi Brad,

                 

                Good to know you have found a way to resolve it.

                 

                As far as I know when Monarch analizes the incomeing records from a 'database' if a field is identified as likely to be a Date a typical standard date field format is used, if it seems to be numeric the field size is set to 17 digits (as per one of the standards for numeric fields usually applied to fiscal related databases)  but the display width will be set to something that represents the max number in the data sample OR 10 as a minimum. Character fields will, I think, be set to the max width found in the data sample.

                 

                This means that dates, if correctly assessed at the time of first import, will probably work OK in most cases. Numeric fields will probably work for the report from which the data sample for the model's template as set BUT may not work for other iterations of the report which may have larger values to display. (The data will be there but not always displayed - a bit like you example.)

                 

                Character fields are, in my view, always best set manually if possible. Typically there will be a max possible size that the incoming field could have in the originating database. If this is known it should be used thought there might be exception where the max possible data size (of known data entries) should be used.

                 

                Of course this assumes that you would have access to the information about the max design size of the originating field!

                 

                These things are important to consider if the model is intended to be used for repeated analysis of files for the future. Who knows what future files might contain?

                 

                HTH.

                 

                Grant