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
  • There is an issue with the "Import Excel to Database" smart service.
    Getting this error - "???Unknown column '' in 'field list'???"
    The issue is occurring if I dont pass "New Columns" and "New Columns Values". If nothing is passed then it forms the insert statement with '' blank column name which causes this error.
    Once I passed a column name into the "New Columns" and a value to "New Columns Values", it worked.
    Example insert statement which gives the exact error in mysql: 

    INSERT INTO `TEST_EXCEL1`(`numberColumn`, `textColumn`, ``) VALUES ("111","333","")
    MySQL said: 
    #1054 - Unknown column '' in 'field list'

    Please look into this and fix it.

Comment
  • There is an issue with the "Import Excel to Database" smart service.
    Getting this error - "???Unknown column '' in 'field list'???"
    The issue is occurring if I dont pass "New Columns" and "New Columns Values". If nothing is passed then it forms the insert statement with '' blank column name which causes this error.
    Once I passed a column name into the "New Columns" and a value to "New Columns Values", it worked.
    Example insert statement which gives the exact error in mysql: 

    INSERT INTO `TEST_EXCEL1`(`numberColumn`, `textColumn`, ``) VALUES ("111","333","")
    MySQL said: 
    #1054 - Unknown column '' in 'field list'

    Please look into this and fix it.

Children
  • 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