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

    Open Database problem

    Babs _

      I receive a huge flat file which I process in Monarch 8.  One of the fields is 10 varchar format and when the file is opened (as file, open database), it truncates this field to 6 characters....any idea why?  And how can I correct this?

       

      When I open the same file as a text import into MS Access, the field comes across fine with all 10 chrs.

        • Open Database problem
          Grant Perkins

          Out of interest, is this a delimited file of some sort or a columnar type of print file?

           

          Does the 10 character field have values with more than 6 characters near (relatively) the start of the file?

           

          Are you able to change the definition of the field, save the model and then re-import the database with 10 chars in the problem field?

           

          And the silly question (just in case ...) is the data size of the field set to 6 characters as well as the display width?

           

           

          Grant

          • Open Database problem
            Babs _

            As it invariably happens...once you post the problem, you find the solution ...

             

            The file was rather large so I didn't want to recreate the whole model.  But, I did open the file and imported just that field....and it worked--it had all 10 characters.  I then opened the model (created in v7), found the field, and changed its hard coded value of 6 to 10.  Concerning....how that works. 

             

            But to answer your questions it was a delimited text file, comma separated, fields in quotes.

            • Open Database problem
              Babs _

              And where are my manners! 

              Thank you Grant for stepping up to assist  smile.gif[/img]

              • Open Database problem
                Grant Perkins

                Glad you got it sorted.

                 

                It is almost as if the checking program auto assessing and creating the field sizes for the import didn't get a 10 character hit for that field. I have come across that before on various packages where, say, only the first xxx records are checked. To be honest I can't recall what  V8 does these days. The problem with checking the entire file is, of course, that to do so would be rather slow and people, me for example, would complain!

                 

                If the model pre-existed it may have been 'set' when the field in question was smaller in an earlier extract. Adjustment would not be dynamic of course, whereas loading into Access afresh would be.

                 

                One of the problems with working with database extracts and delimited OR fixed width outputs is that the sizes will be fixed by the data extracted. So care is required to ensure the sample has maximum fills. If problems are likely  I tend to set up a dummy header row which describes the maximum fills for each field. Monarch will then use those fills to set the values for the import there should be no problems - unless the source database changes!

                 

                I include these thoughts in case later visitors finding this thread remain puzzled - hopefully this will click with them.

                 

                 

                Have fun.

                 

                 

                Grant