cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

The community will undergo maintenance on October 16th at 10:00 PM PDT and will be unavailable for up to one hour.

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

SKannapiran
11-Garnet

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

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);

SKannapiran_0-1695902040854.png

Kindly assist

 

ACCEPTED SOLUTION

Accepted Solutions

Hi @SK_10752382 

 

Please find below code:

 

I have a dummy input 

 

Velkumar_0-1695965738975.png

 

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 :

Velkumar_1-1695965862615.png

/ VR

View solution in original post

7 REPLIES 7

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
4-Participant
(To:Velkumar)

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. 

Hi @SK_10752382 

 

Please find below code:

 

I have a dummy input 

 

Velkumar_0-1695965738975.png

 

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 :

Velkumar_1-1695965862615.png

/ VR

SK_10752382
4-Participant
(To:Velkumar)

Hi @Velkumar ,

 

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

SK_10752382_0-1697110773070.png

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.

SK_10752382_2-1697111081351.png

 

SK_10752382_1-1697110904530.png

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

 

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

 

 

Hi @Velkumar ,

 

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

 

SKannapiran_0-1697113768232.png

 

Hi @SKannapiran 

 

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

 

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

 

/VR

Announcements


Top Tags