Skip to main content
S
SKannapiran12-Amethyst
12-Amethyst
September 28, 2023
Solved

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

  • September 28, 2023
  • 1 reply
  • 3512 views

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

 

Best answer by Velkumar

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

1 reply

V
19-Tanzanite
Velkumar19-Tanzanite
19-Tanzanite
September 28, 2023

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

 

S
SK_107523824-Participant
4-Participant
September 28, 2023

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. 

V
19-Tanzanite
Velkumar19-TanzaniteAnswer
19-Tanzanite
September 29, 2023

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

Terms & ConditionsAccessibility statement