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
  • merge excel doesn't work. We are trying to combine 2 excel data with same number of columns but still output is not showing any thing. Though cofniguration and excel are correct. It is always generating second excel as it is.

  • Hi, we are using import csv to database and it works great also for bigger files, with just one exception. Some of the fields in our CSV (which we unfortunately cannot change) contain quotes inside the text, e.g.:

    0000|Some Text|"text in quotes" more text|00|123|6789

    which is causing the error message invalid char between encapsulated token and delimiter

    when searching the internet I found that in apache csv libraries there is also the option to ignore quotes https://stackoverflow.com/questions/26729799/invalid-char-between-encapsulated-token-and-delimiter-in-apache-commons-csv-libr -> CSVFormat.DEFAULT.withQuote(null)

    are these libraries used in the plugin? if yes, would it be possible to add this parameter? this would be of great help for us!

  • Hi, we are using import excel to db plugin to dump data from to excel to DB. It is reading the data from excel cell. But if the excel cell contains "- ", / "$" then the plugin removed the value from the content. 

    For example:

    • Excel Cell Contains "Testing - Monday", then in staging table contains "Testing Monday"
    • Excel Cell Contains "Testing$", then in staging table contains "Testing".

    Is anything we missed. Can anyone help on this?          

  • Our Appian Health Check reported this plugin is using some deprecated APIs. Reading the "Readme" shows you've previously removed some deprecated APIs, is this still on the cards for future releases?

  • That version is likely too old now for 24.2 - have you had the chance to upgrade the plugin and test?

  • Is the current version 2.5.0 or 2.6.3 the page does not seem to have a version listed only compatible version and the resource link points to 2.5.0 release document

  • we are getting below error though data source is fine and working for all other activities but with this plugin getting below error

    ???java.lang.Throwable: Not able to connect to database. Name [businessds] is not bound in this Context. Unable to find [businessds].???

  • Hi,

    we are still on the 1.8.3 version of this plugin and it is working fine till 23.4.

    But we are seeing the Export SQL to Excel smart service is giving below error since we upgraded to 24.2. All other smart services of the plugin are working fine on 24.2.

    com.appiancorp.suiteapi.process.exceptions.SmartServiceException: userMsg[error.export.general=The following error was raised by the Export to Excel function: class org.apache.poi.openxml4j.util.ZipSecureFile$ThresholdInputStream cannot be cast to class java.util.zip.ZipFile$ZipFileInputStream (org.apache.poi.openxml4j.util.ZipSecureFile$ThresholdInputStream is in unnamed module of loader org.apache.felix.framework.BundleWiringImpl$BundleClassLoader @ea66ef5; java.util.zip.ZipFile$ZipFileInputStream is in module java.base of loader 'bootstrap')]
    at com.appiancorp.suiteapi.process.exceptions.SmartServiceException$Builder.build(SmartServiceException.java:145)
    at com.appiancorp.ps.exceltools.smartservice.export.ExportSqlToExcel.createException(ExportSqlToExcel.java:416)
    at com.appiancorp.ps.exceltools.smartservice.export.ExportSqlToExcel.run(ExportSqlToExcel.java:103)
    at com.appiancorp.process.runtime.framework.DefaultActivityExecutor$1.lambda$call$0(DefaultActivityExecutor.java:136)
    at com.appiancorp.plugins.PluginUsageLogger.runWithPluginInformation(PluginUsageLogger.java:54)
    at com.appiancorp.process.runtime.framework.DefaultActivityExecutor$1.call(DefaultActivityExecutor.java:136)
    at com.appiancorp.process.runtime.framework.DefaultActivityExecutor$1.call(DefaultActivityExecutor.java:130)
    at com.appiancorp.common.ContextClassLoaderSwitcher.runInContext(ContextClassLoaderSwitcher.java:28)

    • Import CSV to Database

      In this smart service if the last columns has no data in csv and so not delimited it seems to populate data with previous record data. Can this be corrected to populate null/empty value in the database.

      This would be helpful to not have random data populated for the parsed records into database.