4 Replies Latest reply: May 15, 2014 9:57 AM by Gareth Horton RSS

    Open Database - Maximum Rows?

    debijo _

      I'm doing an open database on a .csv file that has about 270,000 rows.  I open the .csv file in notepad (as it's too big for excel) and verify the last lines.  I open it as a database in Monarch, it only outputs 266,850 rows.  This is with no filter.  Is there a maximum number of rows the database can handle?

       

      Monarch V8.0

       

      Thanks in advance,

      Debi

        • Open Database - Maximum Rows?
          Grant Perkins

          Debi,

           

          There is a limit for csv (and similar) files ini terms of the total size of work file that the underlying engine can handle but this is a very large number an is dependant on many factors not just the number of rows. One of my applications hit it at around 5 million records - but I did have about 100 fields in the database some calculated and with long text values.

           

          So I suspect you are seeing some other factor at work here.

           

          From what you wrote I assume you have verified that there are a few thousand data rows missing not just a row count mismatch?

           

          You also mentioned using notepad for the row count information. That's not something I have seen in notepad - in fact I would have guessed the notepad might object to the size of the file. I'm interested to know more.

           

          Grant

          • Open Database - Maximum Rows?
            debijo _

            I did open it in Notepad to verify the last few rows.  I opened it as a file in Monarch to get the row count.  When I open the file in Monarch as a database (with no filter), it just stops at line 266,850. 

             

            Thanks again,

            Debi

            • Open Database - Maximum Rows?
              Grant Perkins

              Hi Debi,

               

              OK, now I understand what you did  - if notepad would open it it does suggest it is not so big a file really.

               

              Anyway, what you have seen is that the row appearing in both Notepad and Monarch (when the file is opened as a report)have records at the end that do not appear when you open the same file as a csv database. Is that correct?

               

              Two thoughts occur to me.

               

              1. Something in the file is acting as an end of file marker for a database read but not for opening as a report.

               

              2. The available workspace in the temporary WorkPath that Monarch uses to create the table from the database is being used up.

               

              To check the former you could try deleting a few lines from the beginning of the file and then seeing if you get less lines loaded in the Monarch table when reading as a database.

               

              If you get the same number of lines but still not the entire set, check the WorkPath (See the Help reference in "Settings") and then have a look in that folder for files with names "FILE<number>.mdb" . If you look at the creation times of the files you should be able to work out which one relates to your process - or simply monitor the folder when you start a new process.

               

              As far as I can tell on my system if the .mdb reaches about 1 GB  (circa 1,043Mb) the process stops reading in any more records. From what you have reported I doubt you would reach that limit but there may be some odd factor in the file that causes the system to create a much larger work file than we might assume.

               

              I also wonder about running out of allocated disk space for the work area but if you are always seeing it stop at exactly the same place that seems unlikely unless that is the only application of Monarch running at the time.

               

              I would be happy to try the file on my installations to see what happens but I suspect you may have data security issues that would prevent you from  sending the file to me. Let me know.

               

              Grant

              • Open Database - Maximum Rows?
                Gareth Horton

                Debi,

                 

                I would be happy to take a look at it also.

                 

                If it is less than 1MB (zip the file) then

                you can send it to me at gareth_horton@datawatch.com[/email].

                 

                If it is larger than that, then drop me a mail so we can make arrangements.

                 

                 

                Gareth

                 

                Originally posted by Grant Perkins:

                Hi Debi,

                 

                OK, now I understand what you did  - if notepad would open it it does suggest it is not so big a file really.

                 

                Anyway, what you have seen is that the row appearing in both Notepad and Monarch (when the file is opened as a report)have records at the end that do not appear when you open the same file as a csv database. Is that correct?

                 

                Two thoughts occur to me.

                 

                1. Something in the file is acting as an end of file marker for a database read but not for opening as a report.

                 

                2. The available workspace in the temporary WorkPath that Monarch uses to create the table from the database is being used up.

                 

                To check the former you could try deleting a few lines from the beginning of the file and then seeing if you get less lines loaded in the Monarch table when reading as a database.

                 

                If you get the same number of lines but still not the entire set, check the WorkPath (See the Help reference in "Settings") and then have a look in that folder for files with names "FILE<number>.mdb" . If you look at the creation times of the files you should be able to work out which one relates to your process - or simply monitor the folder when you start a new process.

                 

                As far as I can tell on my system if the .mdb reaches about 1 GB  (circa 1,043Mb) the process stops reading in any more records. From what you have reported I doubt you would reach that limit but there may be some odd factor in the file that causes the system to create a much larger work file than we might assume.

                 

                I also wonder about running out of allocated disk space for the work area but if you are always seeing it stop at exactly the same place that seems unlikely unless that is the only application of Monarch running at the time.

                 

                I would be happy to try the file on my installations to see what happens but I suspect you may have data security issues that would prevent you from  sending the file to me. Let me know.

                 

                Grant /b[/quote]