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

    Opening large dbf file

    RicG _

      I'm trying to open a dbf file which has 287001 rows with Monarch Pro but I get the message "Maximum record length exceeded". Is there a way of just selecting the first half of the records say or is this not the problem ?

       

      Thanks for any help,

       

      Richard.

        • Opening large dbf file
          Grant Perkins

          Richard,

           

          Are you opening the dbf as a database or as a report?

           

          I would expect to see the message if opening as a report but think it would be unlikely, unless the dbf file was corrupt or of a spec not supported for some reason, if you are opening it as a database.

           

          Unless the error message is misleading us, the problem is with the length of the records (or a record) not the number of records.

           

          Dealing with reports Monarch limits to 1000 characters per record whereas dBase as a database allows 4000.

           

          However, opened as a database the 4000 char record will be accomodated by the routines employed.

           

          I hope this helps somehow. Let me know if I have missed the point.

           

           

          Grant

           

          Originally posted by RicG:

          I'm trying to open a dbf file which has 287001 rows with Monarch Pro but I get the message "Maximum record length exceeded". Is there a way of just selecting the first half of the records say or is this not the problem ?

           

          Thanks for any help,

           

          Richard. /b[/quote]

          • Opening large dbf file
            RicG _

            Thanks for the reply. I am going to File then 'Open database', selecting the dbf file from browse, then 'select all' for the columns. When I click finish i get the error.

             

            Could it be thinking several fields are actually one large field due to a 'bad' character, hence causing the error?

            • Opening large dbf file
              RicG _

              It does seems that it doesn't like more than a set amount of columns (that exceeds a total text length). I import one column at a time and it eventually says 'the selected imported fields would exceed internal record length limits by 9 characters'. Is there an obvious way of solving this ?

               

              Thanks.

              • Opening large dbf file
                Grant Perkins

                Richard,

                 

                Sorry, somehow failed to observe your post on the 27th.

                 

                Does seem like you have hit a limit with the size of your dbf records. Other than not loading certain fields but I suspect you need them all - unless the 'last' field is a problem. Have you tried not loading that one but including a few others?

                 

                The off thing is that, by my understanding, DBF records have a limit of 4000 characters which is the same as Monarch, so how one the records can be larger puzzles me. There must be some other factor in play. Are you near the end of the DBF fields to be loaded when you get the error?

                 

                Is there anything noteworthy about any of the fields - particularly the last one loaded?

                 

                One thought that occurs to me is that with mdb files, and I assume that dbf will be the same, loading the database causes Monarch to default to some rather large field sizes. 18 for numerics (10 display width) and IIRC 20 data and display width for text as a minimum. You can change these values using the 'Field List' feature. Then save to model. If you save the project as well, next time you open the database the model will be applied automatically.

                 

                Now, a quick experiment with an Access file shows that you can make the changes to a number of fields then go back to "File >> Database" and resume the definition, adding more fields to the previously modified fields.

                 

                My database records are not large enough to trouble the 4000 character limit but presumably you could  iteratively load some fields, modify the parameters, load some more fields and so on until all required fields are loaded. Save the final version of the model. Save the project (for ease of access later) and that might solve the problem for both current and future analysis. Also makes the working internal database smaller.

                 

                It may be you need to save and then reload afresh using the most recent version of your model each time if the number of changes get very large. I'm guessing on that though.

                 

                I'm a bit busy for the next day or so and I also have never used Monarch with dbf files so I'm working in the dark a bit, but if I come up with anything more I will let you know.

                 

                Grant

                 

                 

                Originally posted by RicG:

                It does seems that it doesn't like more than a set amount of columns (that exceeds a total text length). I import one column at a time and it eventually says 'the selected imported fields would exceed internal record length limits by 9 characters'. Is there an obvious way of solving this ?

                 

                Thanks. /b[/quote]

                • Opening large dbf file
                  RicG _

                  I tried importing a row at a time and pruning the field size and that seems to have sorted it for the dbf file I was working on. So it seems the total of the columns exceeded a maximum. Not sure if i can prune all the databases enough though, we'll see...

                   

                  Thanks Grant.

                   

                  Two more problems now have appeared. One being that when importing a FALSE field from a database it reads it as a zero. The other problem being that when exporting the table to a csv file the dates which appear in the format dd/mm/yyyy in the table view change to yyyymmdd format in the csv file. Any suggestions ?  smile.gif[/img]

                  • Opening large dbf file
                    Grant Perkins

                    Richard,

                     

                    Good news about the import/pruning result!

                     

                    I believe that you should be able deal with any likely input databasesince Monarch should be able to deal with 4000 character records and that is the same as the maximum value for chars in a dBase database. So at worst case the imploarted fields simply need to be made the same size as the dBase data structure rather than the Monarch defualt field sizes for the field type.

                     

                    Anyone got a good fast way to do that?

                     

                    Re: False inports as zero.

                     

                    I guess it is simply importing as the normal numeric value for zero. If you need to see it displayed as true or false one way would be to set up a calcualted field using an internal lookup table that just translates 0 to False and 1 to True. There may be another way. Not something I have had a need to deal with so far.

                     

                    The dates may be a function of the local settings on the PC in conbination with the Date format setting for Input file in Monarch, resulting in the export, obeying the system local format instructions, appearing the wrong way round. Difficult to be sure without knowing the system settings and the date format of the data file you ar using. Dates can be a real pain to get right when systems and inputs can have formats specified and varied 'on the fly'. Sorry I can't add much to that. Again others may have a more productive suggestion or you.

                     

                    Grant

                     

                      Originally posted by RicG:

                    I tried importing a row at a time and pruning the field size and that seems to have sorted it for the dbf file I was working on. So it seems the total of the columns exceeded a maximum. Not sure if i can prune all the databases enough though, we'll see...

                     

                    Thanks Grant.

                     

                    Two more problems now have appeared. One being that when importing a FALSE field from a database it reads it as a zero. The other problem being that when exporting the table to a csv file the dates which appear in the format dd/mm/yyyy in the table view change to yyyymmdd format in the csv file. Any suggestions ?    smile.gif[/img]   /b[/quote]

                     

                    [size="1"][ September 01, 2004, 07:04 PM: Message edited by: Grant Perkins ][/size]

                    • Opening large dbf file
                      RicG _

                      Thanks again for all your help Grant. I think I will just have to write a little program to fix the database fields or use the calculated fields as you suggested.

                       

                      Originally posted by Grant Perkins:

                       

                      Re: False inports as zero.

                       

                      I guess it is simply importing as the normal numeric value for zero. If you need to see it displayed as true or false one way would be to set up a calcualted field using an internal lookup table that just translates 0 to False and 1 to True. There may be another way. Not something I have had a need to deal with so far.

                       

                      The dates may be a function of the local settings on the PC in conbination with the Date format setting for Input file in Monarch, resulting in the export, obeying the system local format instructions, appearing the wrong way round. Difficult to be sure without knowing the system settings and the date format of the data file you ar using. Dates can be a real pain to get right when systems and inputs can have formats specified and varied 'on the fly'. Sorry I can't add much to that. Again others may have a more productive suggestion or you.

                       

                      Grant[/b][/quote]

                      • Opening large dbf file
                        zlatan24 _

                        I'm trying to open a dbf file which has 287001 rows with Monarch Pro but I get the message "Maximum record length exceeded". Is there a way of just selecting the first half of the records say or is this not the problem ?

                         

                        Thanks for any help,

                         

                        Richard.[/QUOTE]

                        You may use present tool for working out given trouble. url=http://www.dbfrepair.orgfix dbf[/url] can work with all system configuration of your computer and all popular Windows OS.

                          • Opening large dbf file
                            elginreigner _

                            Late to the Party << Monarch is most likely setting the field size too large, thus causing your error. You can use a utility to fix the size, but this may not fix it in Monarch.

                             

                            Another approach could be to import a few columns, set the column size, add the next and so forth until all columns are imported with their correct sizes. Dont' forget to save your model.

                             

                            You could also open the DBF file and export/convert it into another format with better control.

                              • Opening large dbf file
                                Grant Perkins

                                No I don't think you are late to the party. The thread dates back to 2004 but ...

                                 

                                Post 9 looks like a rather speculative trawl through the mass of forum entries has offered an opportunity to post some sort of hardly related links to a dbf related product.

                                 

                                If that is all that happens it seems relatively harmless but I would imagine the age of the thread may make the suggestion obsolete.

                                 

                                If it's not so innocuous we can get rid of it.

                                 

                                 

                                 

                                Grant

                                  • Opening large dbf file
                                    elginreigner _

                                    No I don't think you are late to the party. The thread dates back to 2004 but ...

                                     

                                    Post 9 looks like a rather speculative trawl through the mass of forum entries has offered an opportunity to post some sort of hardly related links to a dbf related product.

                                     

                                    If that is all that happens it seems relatively harmless but I would imagine the age of the thread may make the suggestion obsolete.

                                     

                                    If it's not so innocuous we can get rid of it.

                                     

                                     

                                     

                                    Grant[/QUOTE]

                                     

                                    Seriously, where was my head yesterday. Fooled by 2 spammers, geez.

                                      • Opening large dbf file
                                        Grant Perkins

                                        Seriously, where was my head yesterday. Fooled by 2 spammers, geez.[/QUOTE]

                                         

                                        Well, it's not something you expect to see regularly on the Monarch forum so we are all easily caught out when they sneak in.

                                         

                                        I'm usually fooled when they post at around the anniversary point of the last post. I mean, it looks like a viable continuation  - who ever checks the year? 

                                         

                                         

                                        Grant