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
A possible totally different approach might be to load the Excel file directly to a "staging" table in the database and then use either an Appian query (entity or record) or a call to a Stored Procedure to retrieve the data and process that way...loading to the table is in my experience pretty quick, as is fetching data by either of the methods I've described.
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 ?
Agree with Mike - you should be able to use fn!readexcelsheetpaging(), which will map your entire excel file to a CDT. From there, all your data points will be in the same order - instead of reading say row 39 column 4 individually, the same value will be in your local CDT at row 39, 4th data point..
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.