Produced file does not open in SiSense ElastiCube Manager import

[edtguy]

Am I missing a step before writing the Excel file, to make it fully Excel compatible? The file can be opened in Excel with no problem, but when I attempt to read the file I have created with the SiSense Elasticube Manager's import feature, it generates an error with the message, "Object reference not set to an instance of an object." If instead, I first open the file in Excel, do nothing but click on the save icon and exit, I notice the file size has increased, and the resultant file can now be read by SiSense. Since the file is intended to be part of an automated process, opening the file in Excel and saving it is not an optimal step. Below are the relevant excel4node statements. If there is an additional step, e.g. "finalize", that I've missed, please let me know. Thanks.

p.s. It may also be worth noting that when I compare the two files with Beyond Compare, it identifies both as MS Excel Workbooks, and reports 17277 same row(s), though the original file has 1,538,233 bytes and the file opened, then saved in Excel has 1,900,497 bytes, so I assume there is some metadata difference.

const xl = require('excel4node')
/* code to invoke a SOAP api
   that returns xml that has been
   converted to a JSON object
   from which 'headers' and 'rows'
   are extracted
*/
    let wb = new xl.Workbook()
    let ws = wb.addWorksheet('LaborSummary')
    /* only interested in first 24 columns */
    headers.forEach(function(item,i) {
        if(i>=24) {return}
        ws.cell(1,i+1).string(item.name)
    })
    rows.forEach(function(row,i) {
        row.value.forEach(function(v,col) {
            if(col>=24) {return}
            if(headers[col].type === "xs:decimal") {
                ws.cell(i+2,col+1).number(parseFloat(v))
            }
            else if(headers[col].type === "xs:integer") {
                ws.cell(i+2,col+1).number(parseInt(v))
            }
            else if(headers[col].type === "xs:dateTime" || headers[col].type === "xs:date") {
                ws.cell(i+2,col+1).date(new Date(v))
            }
            else {
                ws.cell(i+2,col+1).string(v)
            }
        })
    })
    wb.write('LaborSummary.xlsx')

[natergj]

I think you're right about the missing metadata. This is almost certainly a bug somewhere. Either I am missing a required attribute in the files the excel4node library is generating or SiSense Elasticube Manager compare is looking for something that is required when it should be using a default value.

I can run through the OOXML docs and double check that I'm adding all required props. This one might be a difficult one to troubleshoot since I won't have a way to replicate.

@edtguy, do the SiSense Elasticube Manager logs have any indication about what that "Object reference" might be pointing to?

[edtguy]

@natergj I will check the logs, but they are notoriously obtuse.

[edtguy]

@natergj The log has a combination of readable and non-readable characters. Here is the relevant portion: `</ElastiCube.ManagementService.Process.TrackingItemEvent>

Object reference not set to an instance of an object. 8/21/2018 10:48:20 AM 8/21/2018 10:48:20 AM 10 5 -1 636704453002298704 636704453002298704 SpreadsheetGear ᜂ System.RuntimeType at ᜪ.ᜂ(᠊ A_0) at ᜪ.ᜆ.ᜀ(᠊ A_0) at ៍.ᜀ(᝸ A_0) at ᜪ.ᜆ.ᜁ(᠊ A_0) at ៛.ᜁ.ᜀ() at ៍.ᜀ(᝸ A_0) at ៍.ᜀ(String A_0, ᝸ A_1) at ៛.ᜇ(᠊ A_0) at ៛..ctor(៹ A_0, String A_1, Boolean A_2, ᠊ A_3, ᠊ A_4, ᠊ A_5, ᠊ A_6, Stream A_7, Boolean A_8) at ᢵ.ᜀ(៹ A_0, String A_1, Boolean A_2) at ៹.ᜂ.ᜀ(ᢵ A_0, String A_1, Boolean A_2) at ៹.ᜂ.ᜀ(String A_0, Boolean A_1, Stream A_2) at ៹.ᜂ.Open(String filename) at Prism.Libraries.QuestionComposition.Databases.Excel.Excel2003Database.Excel2003Connection.get_Workbook() at Prism.Libraries.QuestionComposition.Databases.Excel.Excel2003Database.WorksheetExists(String name) at Prism.Libraries.QuestionComposition.Databases.Excel.Excel2003Database.GetRange(String rangeString) at Prism.Libraries.QuestionComposition.Databases.Excel.Excel2003Database.GetSchema(String tableName, Dictionary`2 columnsDics, Boolean withColumns) at Prism.Libraries.QuestionComposition.Databases.Excel.Excel2003Database.GetRawTable(String tableName, IEnumerable`1 columns) at Prism.Libraries.QuestionComposition.Databases.Excel.Excel2003Database.<>c__DisplayClass38_0.<GetReader>b__2(String file) at System.Collections.Generic.List`1.ForEach(Action`1 action) at Prism.Libraries.QuestionComposition.Databases.Excel.Excel2003Database.GetReader(String tableName, IEnumerable`1 columns) at Prism.Libraries.QuestionComposition.Databases.Excel.Excel2003TableQueryRunner.Run(Excel2003Database db, TableQuery q, IEnumerable`1 columns) at Prism.Libraries.QuestionComposition.Databases.DatabaseQueryRunner`4.Run(Object db, Object q, Object qargs) at ElastiCube.ManagementService.Process.TableConnector.GetReader() at ElastiCube.ManagementService.Process.DataTransformationService.ExecuteCopyIntoV3(ActivityBase activitiBase, TrackingItemEvent rootTrackingItem, Cloud cloud, ProcessMetadata pmetadata, String cloudName, CloudTable cloudTable, String tableTarget, IDataReader reader, Int64 rowsToProcess, Int32 chunkSize, List`1 cloudColumns, AbacusColumnType[] columnTypes, Boolean accumulative, CopyIntoMode copyIntoMode)

</ElastiCube.ManagementService.Process.TrackingExceptionEvent>

Object reference not set to an instance of an object. 8/21/2018 10:48:20 AM 8/21/2018 10:48:20 AM 11 4 7 636704453002630952 636704453002630952 SpreadsheetGear ᜂ System.RuntimeType at ElastiCube.ManagementService.Process.DataTransformationService.ExecuteCopyIntoV3(ActivityBase activitiBase, TrackingItemEvent rootTrackingItem, Cloud cloud, ProcessMetadata pmetadata, String cloudName, CloudTable cloudTable, String tableTarget, IDataReader reader, Int64 rowsToProcess, Int32 chunkSize, List`1 cloudColumns, AbacusColumnType[] columnTypes, Boolean accumulative, CopyIntoMode copyIntoMode) at ElastiCube.ManagementService.Process.GenerateDataSourceCacheFromLiveData.executeCopyIntoActivityImpl(Object sender, EventArgs e)

</ElastiCube.ManagementService.Process.TrackingExceptionEvent>

Object reference not set to an instance of an object. 8/21/2018 10:48:20 AM 8/21/2018 10:48:20 AM 13 3 12 636704453002953119 636704453002953119 SpreadsheetGear ᜂ System.RuntimeType at ElastiCube.ManagementService.Process.DataTransformationService.ExecuteCopyIntoV3(ActivityBase activitiBase, TrackingItemEvent rootTrackingItem, Cloud cloud, ProcessMetadata pmetadata, String cloudName, CloudTable cloudTable, String tableTarget, IDataReader reader, Int64 rowsToProcess, Int32 chunkSize, List`1 cloudColumns, AbacusColumnType[] columnTypes, Boolean accumulative, CopyIntoMode copyIntoMode) at ElastiCube.ManagementService.Process.GenerateDataSourceCacheFromLiveData.executeCopyIntoActivityImpl(Object sender, EventArgs e) at ElastiCube.ManagementService.Process.GenerateDataSourceCacheFromLiveData.executeCopyIntoActivity(Object sender, EventArgs e) at System.Workflow.ComponentModel.Activity.RaiseEvent(DependencyProperty dependencyEvent, Object sender, EventArgs e) at System.Workflow.Activities.CodeActivity.Execute(ActivityExecutionContext executionContext) at System.Workflow.ComponentModel.ActivityExecutorOperation.Run(IWorkflowCoreRuntime workflowCoreRuntime) at System.Workflow.Runtime.Scheduler.Run()

</ElastiCube.ManagementService.Process.TrackingExceptionEvent> `

[edtguy]

I thought this might be helpful. I just ran a simple test, creating a two column, two row spreadsheet. The resulting file (3k) does not load into SiSense, but if I open and save the file in Excel, that file (9k) is readable. If you recreate this, will you be able to tell what it is that Excel is adding to the file?

"use strict"
const xl = require('excel4node')

let wb = new xl.Workbook()
let ws = wb.addWorksheet('Metadata Test')
ws.cell(1,1).string('Column A')
ws.cell(1,2).string('Column B')
ws.cell(2,1).number(42)
ws.cell(2,2).string('Universal Answer')
wb.write('MetadataTest.xlsx')

MetadataTest.xlsx MetadataTestSaved.xlsx

[edtguy]

Update: I just loaded and tested another package, exceljs. It has the same issue. Resulting file does not load into SiSense unless first opened and saved in Excel.

[natergj]

MS Excel tends to add a LOT of stuff that is not necessary for the spec. I'm currently working on a 2.0.0 rewrite which should make it easier to add a compatibility setting when creating workbooks. I'd want to make sure that is an optional mode that can be turned on as it does increase file size. I'll let you know when I have a beta up so you can try it to see if that fixes the issue you're seeing.

[edtguy]

Thank you @natergj I'll look forward to testing it when ready, as this library has an easy to understand api. In the meantime, the package xlsx, does generate a spreadsheet file readable by SiSense, and yes, it is significantly larger. It's also more cumbersome to set up.