8 Replies Latest reply: Aug 14, 2015 5:29 AM by Tony Ruggieri RSS

    Issue with MS Excel data Connector - System.OutOfMemoryException

    Rakesh Ojha

      Hi,

       

      I'm getting the below error  in Datawatch  designer 12.xxx , while using large excel file(.xlsx) as a  data connector.

       

      [Exception] System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.

         at System.IO.MemoryStream.ToArray()

         at NPOI.OpenXml4Net.Util.ZipInputStreamZipEntrySource.FakeZipEntry..ctor(ZipEntry entry, ZipInputStream inp)

         at NPOI.OpenXml4Net.Util.ZipInputStreamZipEntrySource..ctor(ZipInputStream inp)

         at NPOI.OpenXml4Net.OPC.ZipPackage..ctor(Stream in1, PackageAccess access)

         at NPOI.OpenXml4Net.OPC.OPCPackage.Open(Stream in1)

         at NPOI.SS.UserModel.WorkbookFactory.Create(Stream inputStream)

         at Panopticon.ExcelNpoiPlugin.Plugin.GetData(String workbookDir, String dataDir, PropertyBag settings, IEnumerable`1 parameters, Int32 rowcount)

         at Panopticon.Dashboards.Data.DefaultDatasourceLoader.GetData(Datasource datasource, IEnumerable`1 parameters, OnDemandParameters onDemandParameters, Int32 rowcount)

         at Panopticon.Dashboards.Builder.Data.CachedDatasourceLoader.GetData(Datasource dataSource, IEnumerable`1 parameters, OnDemandParameters onDemandParameters, Int32 rowcount)

         at Panopticon.Dashboards.Builder.Data.DataTableView.eval_a()

       

       

      Size of Excel file is 28 MB with 65000 rows and 68 columns (Single Worksheet without any applied filter).

       

       

      Is there any input file size realted constraint in Datawatch? Can we not say whatever the volumn of data, we can able to hold in MS Excel file, the same volumn of data we can able to produce in Datawatch?

       

       

      regards,

      Rakesh.

        • Re: Issue with MS Excel data Connector - System.OutOfMemoryException
          Tony Ruggieri

          Hello Rakesh,

           

            Are you using the 32bit version of Designer?   If you are using the 32bit version , can you try installing the 64bit version of Designer

          and retest ?

           

             Regards,

           

          Tony

            • Re: Issue with MS Excel data Connector - System.OutOfMemoryException
              DataExploiter _

              Tony I'm using V12.40 32 bit with no problems with multiple tables much larger. The Excel tables 64k records and 64 fields should originally be exported to access (MDB) not Excel. BTW I tested an Excel table with 88,000 records and many columns. Its works (barely + slow) not quick and stable when using the same table in Access. John

                • Re: Issue with MS Excel data Connector - System.OutOfMemoryException
                  Rakesh Ojha

                  Hi John,

                   

                  Have to try to fetch the records in Datawatch  directly with the help of  Excel connector  having Excel file size around 28MB (Either having single or Multiple Worksheets) instead of MS Access (MDB)?

                   

                  My concen is more about Huge XLS or XLSX file size rather than fetching the 88,000 records (ROWS) or Many  column values.

                   

                  Thanks.

                  regards,

                  Rakesh.

                  • Re: Issue with MS Excel data Connector - System.OutOfMemoryException
                    Rakesh Ojha

                    Hi Tony,

                    It's confirmed now, we are using 32 bit Datawatch Designer 12.3.3 version. Let me check the same scenario with 64 bit Datawatch also.

                     

                    thanks!

                    Rakesh

                      • Re: Issue with MS Excel data Connector - System.OutOfMemoryException
                        Rakesh Ojha

                        Hi Tony,

                        We tested the same scenario with 64 bit Datawatch designer.

                         

                        a) I selected the big excel and it got read with the sheet name populating screen to select a sheet for datasource of a datatable.

                        b) Later after selecting the sheet to create preview of data in datatable it is taking nearly 3.9 GB and more is expecting, but the current machine is having 4 GB RAM only. This makes the Datawatch go in "Not Responding".

                         

                         

                        Please find below the log details for the same...

                         

                         

                        Log:

                         

                        ------------------------------------------------------------

                        2015-08-14 10:32:45.400: [Info] Executing KDB query: select CacheName, UpdateTime  from tblCachesources

                        2015-08-14 10:32:45.415: [Info] KDB plugin query completed, loaded 0 rows, 2 columns in 0 seconds.

                        2015-08-14 10:32:45.421: [Info] Executing KDB query: select CacheName, UpdateTime  from tblCachesources

                        2015-08-14 10:32:45.423: [Info] KDB plugin query completed, loaded 0 rows, 2 columns in 0.001 seconds.

                        2015-08-14 10:32:52.441: [Exception] NPOI.POIXMLException ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.TypeInitializationException: The type initializer for 'NPOI.OpenXmlFormats.Spreadsheet.ThemeDocument' threw an exception. ---> System.InvalidOperationException: Unable to generate a temporary class (result=1).

                        error CS2001: Source file 'C:\Windows\TEMP\1seguakk.0.cs' could not be found

                        error CS2008: No inputs specified

                         

                         

                           at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)

                           at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, XmlSerializerCompilerParameters parameters, Assembly assembly, Hashtable assemblies)

                           at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)

                           at System.Xml.Serialization.XmlSerializer..ctor(Type type, String defaultNamespace)

                           at NPOI.OpenXmlFormats.Spreadsheet.ThemeDocument..cctor()

                           --- End of inner exception stack trace ---

                           at NPOI.XSSF.Model.ThemesTable..ctor(PackagePart part, PackageRelationship rel)

                           --- End of inner exception stack trace ---

                           at System.RuntimeMethodHandle._InvokeConstructor(IRuntimeMethodInfo method, Object[] args, SignatureStruct& signature, RuntimeType declaringType)

                           at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

                           at NPOI.XSSF.UserModel.XSSFFactory.CreateDocumentPart(POIXMLDocumentPart parent, PackageRelationship rel, PackagePart part)

                           --- End of inner exception stack trace ---

                           at NPOI.XSSF.UserModel.XSSFFactory.CreateDocumentPart(POIXMLDocumentPart parent, PackageRelationship rel, PackagePart part)

                           at NPOI.POIXMLDocumentPart.Read(POIXMLFactory factory, Dictionary`2 context)

                           at NPOI.POIXMLDocument.Load(POIXMLFactory factory)

                           at NPOI.SS.UserModel.WorkbookFactory.Create(Stream inputStream)

                           at Panopticon.ExcelNpoiPlugin.Plugin.GetRanges(String path)

                           at Panopticon.ExcelNpoiPlugin.Plugin.eval_b(String path)

                           at Panopticon.Dashboards.Builder.ViewModel.DataSetupViewModel.eval_a(IDataPlugin A_0)

                        2015-08-14 10:33:25.093: [Info] Executing KDB query: select CacheName, UpdateTime  from tblCachesources

                        2015-08-14 10:33:25.095: [Info] KDB plugin query completed, loaded 0 rows, 2 columns in 0.001 seconds.

                        2015-08-14 10:33:25.100: [Info] Executing KDB query: select CacheName, UpdateTime  from tblCachesources

                        2015-08-14 10:33:25.102: [Info] KDB plugin query completed, loaded 0 rows, 2 columns in 0.001 seconds.

                        2015-08-14 10:33:35.695: [Exception] NPOI.POIXMLException ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.TypeInitializationException: The type initializer for 'NPOI.OpenXmlFormats.Spreadsheet.ThemeDocument' threw an exception. ---> System.InvalidOperationException: Unable to generate a temporary class (result=1).

                        error CS2001: Source file 'C:\Windows\TEMP\1seguakk.0.cs' could not be found

                        error CS2008: No inputs specified

                         

                         

                           at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)

                           at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, XmlSerializerCompilerParameters parameters, Assembly assembly, Hashtable assemblies)

                           at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)

                           at System.Xml.Serialization.XmlSerializer..ctor(Type type, String defaultNamespace)

                           at NPOI.OpenXmlFormats.Spreadsheet.ThemeDocument..cctor()

                           --- End of inner exception stack trace ---

                           at NPOI.XSSF.Model.ThemesTable..ctor(PackagePart part, PackageRelationship rel)

                           --- End of inner exception stack trace ---

                           at System.RuntimeMethodHandle._InvokeConstructor(IRuntimeMethodInfo method, Object[] args, SignatureStruct& signature, RuntimeType declaringType)

                           at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

                           at NPOI.XSSF.UserModel.XSSFFactory.CreateDocumentPart(POIXMLDocumentPart parent, PackageRelationship rel, PackagePart part)

                           --- End of inner exception stack trace ---

                           at NPOI.XSSF.UserModel.XSSFFactory.CreateDocumentPart(POIXMLDocumentPart parent, PackageRelationship rel, PackagePart part)

                           at NPOI.POIXMLDocumentPart.Read(POIXMLFactory factory, Dictionary`2 context)

                           at NPOI.POIXMLDocument.Load(POIXMLFactory factory)

                           at NPOI.SS.UserModel.WorkbookFactory.Create(Stream inputStream)

                           at Panopticon.ExcelNpoiPlugin.Plugin.GetRanges(String path)

                           at Panopticon.ExcelNpoiPlugin.Plugin.eval_b(String path)

                           at Panopticon.Dashboards.Builder.ViewModel.DataSetupViewModel.eval_a(IDataPlugin A_0)

                    • Re: Issue with MS Excel data Connector - System.OutOfMemoryException
                      Rakesh Ojha

                      Hi Tony,

                      I'm trying to reach out my IT team to find out which bit specific version (32 bit or 64 bit) of Datawatch Designer I'm using.

                       

                      However, is there any way to find out the same information in the installed tool itself or via some command?

                       

                      As such bit specific information is not available under "Help" >> "About Datawatch Desktop Designer".

                       

                      regards,

                      Rakesh.

                    • Re: Issue with MS Excel data Connector - System.OutOfMemoryException
                      DataExploiter _

                      Hi Rakesh I’m using V12.4 for much larger files with no problem, fast with ability to join other tables. The difference, the files are in Access( .MDB) format. V12 “installs a local SQL database "localdb". This dramatically increases the size of data you can handle”. Comment from Olly Bond original board administrator and monarchexperts.com. This was his full response August 5, 2014 7:02 AM (in response to Don Troglio) Hello Don, There's a setting for logging - I think it's in the registry but might be available in Options - which can be set to verbose. This can write a text file every Modeler session which times different tasks that Modeler is doing (getting a lock on the file, reading the detail template, reading the appends, preparing the table view, building the summaries, etc). The basic difference between Monarch and Modeler (from a performance viewpoint) is that the old Monarch v9 used the Microsoft JET engine to shift data around. This meant that Monarch stored data internally as an Access database (mdb) file, limited to 2GB, 254 fields etc. Monarch v11 replaced JET with the Access Database Engine, (ADE), but Modeler, or Monarch v12, installs a local SQL database "localdb". This dramatically increases the size of data you can handle. It should perform faster too - especially on large data files. To help benchmark, I can send you a million row sample report, if you like, of anonymous data? Then if you open this with Lesson11.dmod and time how long it takes to load the table, we'll have a feel for your system. Best wishes, Olly