Skip to main content
V
vladimirv0001
December 3, 2021
Question

Optimize Excel Tools Reading

  • December 3, 2021
  • 9 replies
  • 0 views

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. 

    9 replies

    stewart.burchell
    stewart.burchell
    December 3, 2021

    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.

    mikes0011
    mikes0011
    Brainy
    December 3, 2021

    Is there any particular reason you're not using readExcelSheet()?

    The Expression Guru
      V
      vladimirv0001Author
      December 3, 2021

      [mention:b45a4f6a20b14a008e4e0ec732a8bf98:e9ed411860ed4f2ba0265705b8793d05]  Well, this is the way I know how to read specific cells from excel file. Is there a better way ?

      mikes0011
      mikes0011
      Brainy
      December 3, 2021

      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.

      The Expression Guru
        Terms & ConditionsAccessibility statement