Information
  • Author: Appian Corporation
  • Type: Plug-in (Function & Smart Service)
  • Support: This plug-in or utility is not supported by the author. The content is free to use with a licensed product, but it is made available as-is and Community supported.
  • Industry: All Industries
  • Compatible Version(s): 23.2+
  • First Released:
  • Last Updated:
  • Cost: Free
Resources
Share
Home » AppMarket » Excel Tools

Excel Tools

Overview

Provides the following custom smart services and functions for handling data in Excel.

Key Features & Functionality

Smart Services included are:

  • Merge Excel Documents
  • Import CSV to Database
  • Import Excel to Database
  • Load CSV to Database
  • Convert HTML to CSV  
  • Convert Excel to CSV
  • Encrypt Excel with Password

Functions included are:

  • Read Excel File Paged
  • Read Excel Cell by Name
  • Read Excel Cell by Number
  • Query Appian Logs
  • Export Datasubset to Base64
  • Validate Document Headers
Anonymous
Parents

  • Hey all, we're on the latest version of the plugin (with Appian 19.1) and do not see correct results when utilizing queryAppian

  • in reply to Chris

    *..when utilizing queryAppianLogs() with hasHeader=false.  For instance:

    queryAppianLogs(
      sqlStatement: "select * from login-audit",
      hasHeader: false
    )

    returns a result set with the column name the same as the column value, not "COLUMN1,COLUMN2.." as defined.  Is anyone else having this issue?

  • in reply to Mike Schmitt

    Hi,

    The latest version of excel tools is now live on the App market
    Release notes are as follows:
    2.2.7 Release Notes

    Functionality Updates:
    • The “Import Excel to DB” smart service no longer errors if no value is passed for the inputs “New
    • Columns” and “New Columns Values”
    • The “Import CSV to DB” smart service now returns a readable error message if there are extra blank
    • columns in the CSV file
    • The “Query Appian Logs” function no longer skips the first row of data is the input “hasHeader” is passed
    • as false
    • The “Convert Excel To CSV” smart service has been updated to utilize the inputs
    • “numberOfColumnsToReadInRow” and “rowNumberToReadFrom”
    Documentation updates:
    • Documentation has been re-added to the AppMarket listing
    • For “Import Excel to DB”, the accepted formats for dates and date times in the sheet are now clearly
    • documented
    • For “Import CSV to DB” and “Import Excel to DB”, the descriptions and example values for the smart
    • service inputs have been updated
    • For “Query Appian Logs”, the passing of inputs by order is explained and an example function call has
    • been added
  • in reply to Steven Miccile

    Thanks for confirming - as far as I remember, I'd tried it both ways (i figured dictionary syntax wouldn't work but also didn't produce specific errors).  

    Also as a side note, Community doesn't seem to send any sort of notification of a reply here even though you had replied directly to me (this seems to hold true for any sort of comment activity within the App Market space).

  • in reply to Mike Schmitt

    So the first issue here is the syntax for calling the function. You cannot use keyword syntax when calling a plugin, you must use position syntax: docs.appian.com/.../Expressions.html

    An example syntax would be queryAppianLogs("select * from login-audit",null,{"String", "String", "String","String","String","String"},null,false) to fill out the inputs for sqlStatement, subFolder, columnTypes, timestampFormat, and hasHeader respectively.

    Testing using this syntax produces more expected results however passing false for hasHeader seems to skip the first row of data in the file so we will look into fixing that. In the meantime, the Log Reader plugin can also pull csv data.

  • in reply to Chris

    Yes, I've reproduced this issue just now as well.  It appears that the queryAppianLogs() function doesn't actually support all of the parameters that are listed.  Maybe  or  have some insight into this issue.

Comment Children
  • in reply to Mike Schmitt

    Hi,

    The latest version of excel tools is now live on the App market
    Release notes are as follows:
    2.2.7 Release Notes

    Functionality Updates:
    • The “Import Excel to DB” smart service no longer errors if no value is passed for the inputs “New
    • Columns” and “New Columns Values”
    • The “Import CSV to DB” smart service now returns a readable error message if there are extra blank
    • columns in the CSV file
    • The “Query Appian Logs” function no longer skips the first row of data is the input “hasHeader” is passed
    • as false
    • The “Convert Excel To CSV” smart service has been updated to utilize the inputs
    • “numberOfColumnsToReadInRow” and “rowNumberToReadFrom”
    Documentation updates:
    • Documentation has been re-added to the AppMarket listing
    • For “Import Excel to DB”, the accepted formats for dates and date times in the sheet are now clearly
    • documented
    • For “Import CSV to DB” and “Import Excel to DB”, the descriptions and example values for the smart
    • service inputs have been updated
    • For “Query Appian Logs”, the passing of inputs by order is explained and an example function call has
    • been added
  • in reply to Steven Miccile

    Thanks for confirming - as far as I remember, I'd tried it both ways (i figured dictionary syntax wouldn't work but also didn't produce specific errors).  

    Also as a side note, Community doesn't seem to send any sort of notification of a reply here even though you had replied directly to me (this seems to hold true for any sort of comment activity within the App Market space).

  • in reply to Mike Schmitt

    So the first issue here is the syntax for calling the function. You cannot use keyword syntax when calling a plugin, you must use position syntax: docs.appian.com/.../Expressions.html

    An example syntax would be queryAppianLogs("select * from login-audit",null,{"String", "String", "String","String","String","String"},null,false) to fill out the inputs for sqlStatement, subFolder, columnTypes, timestampFormat, and hasHeader respectively.

    Testing using this syntax produces more expected results however passing false for hasHeader seems to skip the first row of data in the file so we will look into fixing that. In the meantime, the Log Reader plugin can also pull csv data.

© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.