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
  • Hi Team,

    We are facing a strange issue with CSV to DB 

    Summary -Issue with CSV to Db Smart service - CSV is not getting uploaded with more than 48 columns Previously we had 48 columns in the csv , and it was working fine , Later we had a new requirement to add new column, after adding the 49 column it stops working and showing error as attached. We have done few digging and found that if we remove any one column ( either new one or any another one), and remove the same in column mapping in the column parameter of the csv to DB smart service it works fine . Until we keep the column count within 48 all works well . 

    While checking the documentation , I can see that we can see the generated logs of this plugin , Can you please help how to get the logs of its prepare statement.

    I can see below error in tomcat logs- 

    ERROR ImportCsvToDatabase8 Error executing SQL
    java.sql.SQLSyntaxErrorException: (conn=3176834) You need to set exactly 48 parameters on the prepared statement
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:176)
    at org.mariadb.jdbc.ClientSidePreparedStatement.addBatch(ClientSidePreparedStatement.java:256)
    at sun.reflect.GeneratedMethodAccessor9526.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
    at com.sun.proxy.$Proxy218.addBatch(Unknown Source)
    at com.appiancorp.ps.exceltools.util.DatabaseUtils.addBatchInsert(DatabaseUtils.java:594)
    at com.appiancorp.ps.exceltools.util.CsvHelperUtils.importDataWithHeaderFromFile(CsvHelperUtils.java:181)
    at com.appiancorp.ps.exceltools.smartservice.ImportCsvToDatabase8.processTheRequest(ImportCsvToDatabase8.java:90)
    at com.appiancorp.ps.exceltools.smartservice.ImportCsvToDatabase8.run(ImportCsvToDatabase8.java:70)
    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:27)
    at com.appiancorp.process.runtime.framework.DefaultActivityExecutor.execute(DefaultActivityExecutor.java:130)
    at com.appiancorp.process.engine.UnattendedJavaActivityRequest.execute(UnattendedJavaActivityRequest.java:88)
    at com.appiancorp.process.engine.UnattendedJavaActivityRequestResponseCreator.getJavaActivityResultResponse(UnattendedJavaActivityRequestResponseCreator.java:22)
    at com.appiancorp.process.engine.UnattendedJavaActivityRequest.getJavaActivityResultResponse(UnattendedJavaActivityRequest.java:67)
    at com.appiancorp.process.engine.UnattendedJavaActivityRequest.execute0(UnattendedJavaActivityRequest.java:55)
    at com.appiancorp.common.persistence.data.AppianDataUserContextProvider.executeWithConsistentDataReads(AppianDataUserContextProvider.java:143)
    at com.appiancorp.process.engine.ProcessContinuationRequest.executeOuter(ProcessContinuationRequest.java:68)
    at com.appiancorp.process.engine.ContinuationRequest.execute(ContinuationRequest.java:129)
    at com.appiancorp.process.workpoller.UnattendedRequestHandlerBean.onMessage(UnattendedRequestHandlerBean.java:36)
    at com.appiancorp.process.workpoller.WorkItem.run(WorkItem.java:110)
    at org.springframework.jca.work.SimpleTaskWorkManager$DelegatingWorkAdapter.run(SimpleTaskWorkManager.java:241)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:750)

Comment
  • Hi Team,

    We are facing a strange issue with CSV to DB 

    Summary -Issue with CSV to Db Smart service - CSV is not getting uploaded with more than 48 columns Previously we had 48 columns in the csv , and it was working fine , Later we had a new requirement to add new column, after adding the 49 column it stops working and showing error as attached. We have done few digging and found that if we remove any one column ( either new one or any another one), and remove the same in column mapping in the column parameter of the csv to DB smart service it works fine . Until we keep the column count within 48 all works well . 

    While checking the documentation , I can see that we can see the generated logs of this plugin , Can you please help how to get the logs of its prepare statement.

    I can see below error in tomcat logs- 

    ERROR ImportCsvToDatabase8 Error executing SQL
    java.sql.SQLSyntaxErrorException: (conn=3176834) You need to set exactly 48 parameters on the prepared statement
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:176)
    at org.mariadb.jdbc.ClientSidePreparedStatement.addBatch(ClientSidePreparedStatement.java:256)
    at sun.reflect.GeneratedMethodAccessor9526.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
    at com.sun.proxy.$Proxy218.addBatch(Unknown Source)
    at com.appiancorp.ps.exceltools.util.DatabaseUtils.addBatchInsert(DatabaseUtils.java:594)
    at com.appiancorp.ps.exceltools.util.CsvHelperUtils.importDataWithHeaderFromFile(CsvHelperUtils.java:181)
    at com.appiancorp.ps.exceltools.smartservice.ImportCsvToDatabase8.processTheRequest(ImportCsvToDatabase8.java:90)
    at com.appiancorp.ps.exceltools.smartservice.ImportCsvToDatabase8.run(ImportCsvToDatabase8.java:70)
    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:27)
    at com.appiancorp.process.runtime.framework.DefaultActivityExecutor.execute(DefaultActivityExecutor.java:130)
    at com.appiancorp.process.engine.UnattendedJavaActivityRequest.execute(UnattendedJavaActivityRequest.java:88)
    at com.appiancorp.process.engine.UnattendedJavaActivityRequestResponseCreator.getJavaActivityResultResponse(UnattendedJavaActivityRequestResponseCreator.java:22)
    at com.appiancorp.process.engine.UnattendedJavaActivityRequest.getJavaActivityResultResponse(UnattendedJavaActivityRequest.java:67)
    at com.appiancorp.process.engine.UnattendedJavaActivityRequest.execute0(UnattendedJavaActivityRequest.java:55)
    at com.appiancorp.common.persistence.data.AppianDataUserContextProvider.executeWithConsistentDataReads(AppianDataUserContextProvider.java:143)
    at com.appiancorp.process.engine.ProcessContinuationRequest.executeOuter(ProcessContinuationRequest.java:68)
    at com.appiancorp.process.engine.ContinuationRequest.execute(ContinuationRequest.java:129)
    at com.appiancorp.process.workpoller.UnattendedRequestHandlerBean.onMessage(UnattendedRequestHandlerBean.java:36)
    at com.appiancorp.process.workpoller.WorkItem.run(WorkItem.java:110)
    at org.springframework.jca.work.SimpleTaskWorkManager$DelegatingWorkAdapter.run(SimpleTaskWorkManager.java:241)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:750)

Children