Hello, is there a way to optimize the reading of excel file with Excel Tools Plug-in. In general the reading time it takes to long, for example to read 70 cells it takes about 5 minutes, for something like that :
a!localVariables( local!data: 'type!{urn:com:appian:types:BTR}BTR_D1_B'( /* 18 */ c18_1: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {4}, rowNumbers: {39}), c18_2: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {10}, rowNumbers: {39}), c18_3: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {12}, rowNumbers: {39}), c18_4: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {14}, rowNumbers: {39}), c18_5: tointeger(excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {17}, rowNumbers: {39})), c18_6: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {20}, rowNumbers: {39}), c18_7: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {23}, rowNumbers: {39}), /* 19 */ c19_1: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {4}, rowNumbers: {40}), c19_2: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {10}, rowNumbers: {40}), c19_3: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {12}, rowNumbers: {40}), c19_4: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {14}, rowNumbers: {40}), c19_5: tointeger(excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {17}, rowNumbers: {40})), c19_6: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {20}, rowNumbers: {40}), c19_7: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {23}, rowNumbers: {40}), /* 20 */ c20_1: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {4}, rowNumbers: {41}), c20_2: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {10}, rowNumbers: {41}), c20_3: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {12}, rowNumbers: {41}), c20_4: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {14}, rowNumbers: {41}), c20_5: tointeger(excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {17}, rowNumbers: {41})), c20_6: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {20}, rowNumbers: {41}), c20_7: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {23}, rowNumbers: {41}), /* 21 */ c21_1: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {4}, rowNumbers: {42}), c21_2: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {10}, rowNumbers: {42}), c21_3: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {12}, rowNumbers: {42}), c21_4: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {14}, rowNumbers: {42}), c21_5: tointeger(excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {17}, rowNumbers: {42})), c21_6: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {20}, rowNumbers: {42}), c21_7: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {23}, rowNumbers: {42}), /* 22 */ c22_1: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {4}, rowNumbers: {43}), c22_2: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {10}, rowNumbers: {43}), c22_3: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {12}, rowNumbers: {43}), c22_4: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {14}, rowNumbers: {43}), c22_5: tointeger(excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {17}, rowNumbers: {43})), c22_6: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {20}, rowNumbers: {43}), c22_7: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {23}, rowNumbers: {43}), /* 23 */ c23_1: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {4}, rowNumbers: {44}), c23_2: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {10}, rowNumbers: {44}), c23_3: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {12}, rowNumbers: {44}), c23_4: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {14}, rowNumbers: {44}), c23_5: tointeger(excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {17}, rowNumbers: {44})), c23_6: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {20}, rowNumbers: {44}), c23_7: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {23}, rowNumbers: {44}), /* 24 */ c24_1: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {4}, rowNumbers: {45}), c24_2: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {10}, rowNumbers: {45}), c24_3: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {12}, rowNumbers: {45}), c24_4: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {14}, rowNumbers: {45}), c24_5: tointeger(excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {17}, rowNumbers: {45})), c24_6: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {20}, rowNumbers: {45}), c24_7: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {23}, rowNumbers: {45}), /* 25 */ c25_1: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {4}, rowNumbers: {46}), c25_2: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {10}, rowNumbers: {46}), c25_3: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {12}, rowNumbers: {46}), c25_4: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {14}, rowNumbers: {46}), c25_5: tointeger(excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {17}, rowNumbers: {46})), c25_6: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {20}, rowNumbers: {46}), c25_7: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {23}, rowNumbers: {46}), /* 26 */ c26_1: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {4}, rowNumbers: {47}), c26_2: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {10}, rowNumbers: {47}), c26_3: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {12}, rowNumbers: {47}), c26_4: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {14}, rowNumbers: {47}), c26_5: tointeger(excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {17}, rowNumbers: {47})), c26_6: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {20}, rowNumbers: {47}), c26_7: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {23}, rowNumbers: {47}), /* 27 */ c27_1: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {4}, rowNumbers: {48}), c27_2: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {10}, rowNumbers: {48}), c27_3: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {12}, rowNumbers: {48}), c27_4: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {14}, rowNumbers: {48}), c27_5: tointeger(excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {17}, rowNumbers: {48})), c27_6: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {20}, rowNumbers: {48}), c27_7: excelreadcellsbynumber(excelDoc: ri!document, sheetNumbers: {6}, columnNumbers: {23}, rowNumbers: {48}), ), local!data )
Any suggestions are welcome !
Thanks.
Discussion posts and replies are publicly visible
Is there any particular reason you're not using readExcelSheet()?
Mike Schmitt Well, this is the way I know how to read specific cells from excel file. Is there a better way ?
Exactly as Chris mentioned already, it would be far more efficient to read the entire sheet data into a local variable and then step through that to parse it, rather than reading one cell at a time over dozens of cells.
Even your current call seems like it could be far more efficient by passing in arrays of column and row numbers (considering that you're reading the same columns across all rows), however it'd probably be easier just to read the entire sheet to local data and parse that as needed.
To add, here's some sample code from one of my excel reading functions which converts the document to a CDT. Note this is on an older version of Excel Tools which utilizes fn!readexcelsheet(), the newer version to use is fn!readexcelsheetpaging().
a!localVariables( local!data: readexcelsheet( ri!doc, 0, 2 ), if( not(local!data.success), {}, reject( rule!APN_isEmpty, a!flatten( a!forEach( items: local!data.result, expression: { /* fv!index will tell you which row is currently being referenced */ if( rule!APN_isEmpty(index(fv!item.values,1,"")), null, 'type!{urn:com:gdit:types}Your_Data_Type'( field1: index(fv!item.values,1,""), field2: index(fv!item.values,2,""), field3: index(fv!item.values,3,""), field4: index(fv!item.values,4,"") ) ) } ) ) ) ) )
I have tried this solution but the problem is that this is complex excel file, with this function what happens is that it does not return data or returns only one small part of the data.
It would be tough to troubleshoot without some additional samples. Are you able to include a scrubbed version of the Excel file here, etc?
https://www.bakertilly-digital.com/ExampleExcel.zip
Can be downloaded here, example in the first sheet M and N columns are only information of value for extraction.