Community Tip - Did you get an answer that solved your problem? Please mark it as an Accepted Solution so others with the same problem can find the answer easily. X
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
Solved! Go to 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
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
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
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
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
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 @SKannapiran
Unfortunately, we don't have much control over it.
You can create an Excel extension and can handle this one.
/VR