Excel Tools

hi - I am using Import CSV to Database smart service and facing issues with some of the rows/records in the CSV.  

For one of the records it  give NumberFormatExceptions e.g "VALE SA-SP ADRCLIENT REF  41807091" .

Plug-in is not able to read entire string instead it gives me  java.lang.NumberFormatException: For input string: "CLIENT REF  41807091"" . In DB(MySQL) I am capturing this field as VARCHAR (35) .

Is there any limitation in terms of data/text which should be in CSV file ?

Please suggest !

@Agustinc - I took a closer look at the error and I agree with Mike.

The function that you are using is reading an Excel file, loading the content of a sheet into memory in the application server and converting this content into a dictionary that is then returned by the function. All of this processing and data transformation is handled by the application using heap memory.

Depending on the size of the spreadsheet, this is known to consume a lot of memory and in some cases, lead to a error when running out of memory. However, this does not indicate a defect.

I am not familiar with your use case but in general, I recommend against loading large amounts of data in Appian processes or expressions. The pattern I have used successfully in the past is to load the data from a spreadsheet directly into a database table - using the plugin Import CSV to Database - and then process this data in small batches. This prevents spikes in memory.

I hope this helps.

As suggested by Support, please update to the latest plugin.

That said, the plugin CAN consume a lot of memory depending on the file size - but that does not mean there is a mem leak. If there are old objects in the heap dump, then yes, but it sounds like you have normal usage and then execute the plugin that bumps your memory higher. Please verify your heap dumps and let the community know.

This plugin crashes our Cloud environment about once per month due to an apparent memory leak which slowly consumes all of our EC2 memory the more we use it. Appian support has been having to restart our environment to temporarily fix this. We have been trying to get to the bottom of this to see if we can fix it, or figure out if we are misusing Excel Tools in some way, but have not had any luck. We are running the latest version of Excel Tools. Any advice or support would be highly appreciated!

Appian Support commented the following:

"I ran a leaked suspects analysis and the largest consumer of heap during the high load was from some exceltool classes. The following snippet from the stack trace shows the exceltool classes of note:

at com.appiancorp.ps.exceltools.util.ExcelHelperUtils.getWorkbook(Ljava/lang/String;Ljava/lang/String;)Lorg/apache/poi/ss/usermodel/Workbook; (ExcelHelperUtils.java:562) at com.appiancorp.ps.exceltools.expression.ReadExcelFileFunction.readExcelSheet(Lcom/appiancorp/suiteapi/content/ContentService;Lcom/appiancorp/suiteapi/type/TypeService;Ljava/lang/Long;IILjava/lang/Integer;Ljava/lang/String;)Lcom/appiancorp/suiteapi/type/TypedValue; (ReadExcelFileFunction.java:51) I see that the site is using an older version of the execltools plugin, V 1.9.4. From the changelogs there are some changes that improve the stability of the plugin. I recommend deploying the latest version of the plugin and retesting to see if the behavior improves."

Question:  When using an excel template; is it possible to set the Word Wrap for a column so that when the spreadsheet is opened then the data from a paragraph field is wrapped in a column cell versus the default single line.?

Ben Dudley  - I just downloaded this plug-in today and the Readme file doesn't appear to have any documentation or examples related to the "Export CDT data to Excel" smart service. Can you assist with this? Thanks! JT

Hello, I am getting error below when deploying the plugin. I've deleted the old CDT. Kindly, please shed some light on the issue.

ERROR com.appiancorp.plugins.LoggingPluginEventListener - Failed to enable Plug-in 'com.appiancorp.ps.exceltools' (com.appiancorp.ps.exceltools) version 2.0.2: 'Could not create types for [class com.appiancorp.ps.exceltools.datatype.ExportableDataSubset]. Cause: An error occurred while trying to save the type information to the Appian data source. No types have been imported. Cause: Type definitions could not be found in the Appian data source for the following type ids: 110 (APNX-1-4166-003)

Same with previous posters, we were looking at migrating a lot of our functionality away from the older style deprecated plugs ins that the HEalthcheck has highlighted to use the Excel Tools functionality but the loss of the Export CDT to Excel smart service now prohibits that activity (if we wanted to use the up to date version of the plug in - bit of a catch 22 situation).  

For the CSV to database V3 smart-service, we have also found that the insert scripts from the CSV file for databases that use Schemas are generating incorrectly.

The insert scripts are generated like the below,

insert into [schema.tableName] (column1,colum2) values (value1,value2)  -- WRONG

But it should be generated as,

insert into [schema].[tableName] (column1,colum2) values (value1,value2) – CORRECT

Any chance this could be addressed in a future update?

Same with Mike Schmitt comment above, our project also make use of the Export CDT to Excel smart service quite heavily. Is there any plans underway for adding this back to the Excel Tools plugin?

Is there any particular reason the Export CDT to Excel functionality has apparently been removed as of the last update?  I rely on this smart service in a few different places in a few different projects, and want to be able to upgrade plug-in versions in the future without losing this ability.

For clarification, I use Export CDT to Excel for use cases which are NOT handled by Export DSE to Excel (such as reformatting data post-query before inserting into excel).