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
  • Is there an issue with the excel import smart service in 19.4?  Was working fine in 18.2.  I get the following error:  ???ORA-01741: illegal zero-length identifier
    ???

  • Hi - can you please provide some more context for when you're getting this error and what's not working?

  • Hi, I am using date format like "2020-08-12" or "2020-08-12 03:43:13" in "Import CSV to DB", but get the error message "Date Format must be one of the following 'yyyy-MM-dd', 'yyyy-MM-dd HH:mm:ss', 'HH:mm:Ss'",

    Can anybody help me out?

  • Hi ,

    We are using Appian Cloud Environment, So do we still need to delete ExportableDataSubset CDT?

  • Yes, please delete the old plugin, then please delete all old versions of the "ExportableDataSubset" CDT 
    After that, deploy paste the new file in the folder.
    Then update your model to point to the new version {urn:com:appian:ps:excel:types}ExportableDataSubset.

  • while tried using the plugin version 2.7.7 on Appian 20.1 Cloud there were no issues reported on process model but on log found below error

    2020-06-02 08:09:22,051 [Appian Work Item - 4821 - execution00 : UnattendedJavaActivityRequest] ERROR com.appiancorp.ps.exceltools.smartservice.ImportCsvToDatabase5 - Unknown column 'ID' in 'field list'
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'ID' in 'field list'

    But the column ID exists on .csv file and Db table as first column.
  • , we have older installed on premise env. And the newer version file name is "com.appiancorp.ps.exceltools-2.2.7.jar". 

    How do we install this new version on our on-permise? we have already used smart services from this plugin on few PM.

    1. Do we delete the old plugin on _plugin folder and place the new file? 

    2. or can the new file exisits along with the old plugin ?

    please suggest

  • Hi Bhargav,

    Is this plugin not compatible with 19.4 yet? I'm trying to use it in 19.4. When I executes the process, it completes successfully, but data doesn't seem to import into DB.

    I tried both 'Import Excel to database' and 'Import CSV to database' with no success.

    Not sure if anyone facing similar issues.

  • 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
  • Hi April.  When I execute the excel import task in my work flow, it errors out with the above mentioned error.  The error hints at the smart service not correctly reading the column names in the first row of the excel.  Similar to trying to run this query on a DB: select "" from [table].

Comment
  • Hi April.  When I execute the excel import task in my work flow, it errors out with the above mentioned error.  The error hints at the smart service not correctly reading the column names in the first row of the excel.  Similar to trying to run this query on a DB: select "" from [table].

Children
  • Hi, I am using date format like "2020-08-12" or "2020-08-12 03:43:13" in "Import CSV to DB", but get the error message "Date Format must be one of the following 'yyyy-MM-dd', 'yyyy-MM-dd HH:mm:ss', 'HH:mm:Ss'",

    Can anybody help me out?

  • Hi ,

    We are using Appian Cloud Environment, So do we still need to delete ExportableDataSubset CDT?

  • Yes, please delete the old plugin, then please delete all old versions of the "ExportableDataSubset" CDT 
    After that, deploy paste the new file in the folder.
    Then update your model to point to the new version {urn:com:appian:ps:excel:types}ExportableDataSubset.

  • while tried using the plugin version 2.7.7 on Appian 20.1 Cloud there were no issues reported on process model but on log found below error

    2020-06-02 08:09:22,051 [Appian Work Item - 4821 - execution00 : UnattendedJavaActivityRequest] ERROR com.appiancorp.ps.exceltools.smartservice.ImportCsvToDatabase5 - Unknown column 'ID' in 'field list'
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'ID' in 'field list'

    But the column ID exists on .csv file and Db table as first column.
  • , we have older installed on premise env. And the newer version file name is "com.appiancorp.ps.exceltools-2.2.7.jar". 

    How do we install this new version on our on-permise? we have already used smart services from this plugin on few PM.

    1. Do we delete the old plugin on _plugin folder and place the new file? 

    2. or can the new file exisits along with the old plugin ?

    please suggest

  • Hi Bhargav,

    Is this plugin not compatible with 19.4 yet? I'm trying to use it in 19.4. When I executes the process, it completes successfully, but data doesn't seem to import into DB.

    I tried both 'Import Excel to database' and 'Import CSV to database' with no success.

    Not sure if anyone facing similar issues.

  • 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