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
  • 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.
  • Did you find any way to execute this requirement?

  • Has anyone used the queryAppianLogs function to select a count from the files with a group by and a where condition ?

    No matter what I try I'm getting a null value return ... using the query without the group by returns data, using the count without the where also returns data but when combining the two together to get a simple count from the removed processes log where the timestamp is today returns nothing.

    Is this is a feature of the plug in ? We're using 2.5.0 

    Any hints / tips would be appreciated. 

    Thanks 

    Paul

  • Hi,

    We have a Appian 24.1, is this plugin compatible for this version?

    We are facing issues while performing export of records. It is not downloading the file even after clicking the Export option. Also, do you any recommendations on how many records we need to download at a time

  • Team,

    I am using the smart service - Import Excel to Database - in one of my development projects to read an excel files and write the values as-is to the database tables. The excel file is huge and is having 2 sheets that contains data to be written to 2 different table. As per the implementation, I have created 2 different tables for storing this information and the excel column names and table column names were specifically mentioned along with the mandatory information required for the smart service to work.

    Data is getting written into the database table, but not fully. The first sheet has 83621 rows (out of 83625), and the second sheet has 137738 rows (out of 137742) of data that needs to be written to the database table. The batch size of the smart service is configured as 1000. After successful execution of the process model, I could see that the 82994 rows were written from the first sheet and 136994 rows were written from the second sheet of the excel file.

    I believe it is something related to the batch size, as I modified the batch size from 1000 to 500 and the number of rows processes got changed to 83494 and 137494 respectively, which is 500 rows more than what was the previous outcome. Further, I reduced the batch size to 100 and the number of rows processes got changed to 83594 and 137694 respectively, which is again 100 more than was the previous outcome.

    Because of this issue, we are not able to extract the data completely from the excel files. Kindly help me in resolving this issue.

    Regards,
    Deepak

  • Is there a way in this plugin by which we are able to get the sheet name (tabs name) in the excel spreadsheet from this smart service ?

  • v2.6.3 Release Notes
    • Added support for PostreSQL.