Iterating through an array of infotable collection and export the infotable elements to excel

[SKannapiran]

Hi,

I have a collections of infotable as shown below. Each table has different column structure and no tables are same. Now I need to iterate through each infotable and export the data as a excel report.

using the snippet.

let params = {
infotable: undefined /* INFOTABLE */
};// result: STRING
let result = Resources["InfotableExporterFunctions"].ExportInfotableAsExcel(params);

Kindly assist

[Velkumar]

Hi @SKannapiran 

Could you please provide some sample data and how exactly Excel should look.

Because I can see each info table has a different row count how do you want to manage those into a single Excel sheet.

If fieldNames are not defined or dynamic, you can iterate over fieldName to populate data

// infotable datashape iteration
let dataShapeFields = yourInfotableHere.dataShape.fields;
for (let fieldName in dataShapeFields) {
    //logger.warn('field name is ' + dataShapeFields[fieldName].name);
    //logger.warn('field basetype is ' + dataShapeFields[fieldName].baseType);
}

Please provide some sample data it will help us to understand requirements better.

Thanks.

VR

[SK_10752382]

Dear @Velkumar ,

I have attached an excel workbook, having multiple sheets (Table0, Table1, Table2, Expected_Result)

Each tab represents an infotable with static columns. The expected result is shown in the last tab.

Hope this answer to your request. 

[Velkumar · Accepted solution]

Hi @SK_10752382 

Please find below code:

I have a dummy input 

Code to combine all tables into a single InfoTable

// Dummy Input
var dummyInputInfoTable = me.dummyInput();

/* Find maximum number of fields in Table */
var maxFieldCount = 0;
var tempInfoTable = null;
var tempInfoTableFields = null;
// Iterate over each infoTable from input
let dataShapeFields = dummyInputInfoTable.dataShape.fields;
for (let fieldName in dataShapeFields) {
	tempInfoTable = dummyInputInfoTable[fieldName];
	tempInfoTableFields = tempInfoTable.dataShape.fields;
	var tempFieldCount = Object.keys(tempInfoTableFields).length;
	if (tempFieldCount > maxFieldCount) {
		maxFieldCount = tempFieldCount;
	}
}

/* Create result infoTable to store data */
let params = {
	infoTableName: "result" /* STRING {"defaultValue":"InfoTable"} */
};

// result: INFOTABLE
let result = Resources["InfoTableFunctions"].CreateInfoTable(params);

// Add fields to result infoTable
for (var i = 0; i < maxFieldCount; i++) {
	result.AddField({
		name: "field" + (i + 1),
		baseType: "STRING"
	});
}

for (let fieldName in dataShapeFields) {
	tempInfoTable = dummyInputInfoTable[fieldName];
	// Add Fields into result infoTable
	tempInfoTableFields = tempInfoTable.dataShape.fields;
	var fieldCount = 0;
	var fieldValues = new Object();
	for (let fieldName in tempInfoTableFields) {
		fieldValues["field" + (fieldCount + 1)] = fieldName;
		fieldCount++;
	}
	result.AddRow(fieldValues);
    
    // Add data into result InfoTable
    for(var infoLength = 0; infoLength < tempInfoTable.length; infoLength++)
    {
        var dataValues = new Object();
        var dataFieldCount = 0;
        for (let fieldName in tempInfoTableFields){
            dataValues["field" + (dataFieldCount + 1)] = tempInfoTable[infoLength][fieldName];
            dataFieldCount++;
        }
        result.AddRow(dataValues);
    }
}

Expected Output :

/ VR

[SK_10752382]

Hi @Velkumar ,

Sorry for the delay in reply, Your code works fine and combined all the info table as expected in the console.

But when I tried to write the result in a CSV file as output the column (field name) order shuffled and the CSV output is different from the console output.

Is there any correction needed in the Script? Please assist. Thank you

[Velkumar]

Hi @SK_10752382 

Sorting of column names in CSV should be handled in CSVParser Extension / Service. 

We don't have much control over it. 

May I know which service/extension you are using to export the CSV file or is this an OOTB data exporter widget?

You can also build your own Excel Export extension using Apache POI to get the desired output format.

/VR

[SKannapiran]

Hi @Velkumar ,

We are using ThingWorx Snippet to write the result as a CSV.

[Velkumar]

Hi @SKannapiran 

Unfortunately, we don't have much control over it. 

You can create an Excel extension and can handle this one.

/VR