Excel Tools

Hello,

Could you please redirect us to latest documentation/readme.pdf?

Hi Corrado de Candia where can we find more documentation of the plugin? Like a User Guide. Thanks !!!

When will you re-add the ability to take an arbitrary set of data, do appian-side processing/transformation to it, and write the result easily to an Excel file complete with word wrap enabled on the written cells?  The only previous way to do all of this was the Export CDT to excel, now removed, with no functional replacement at all.

I know I could create a scrap DB table with arbitrary text columns and write data to it, then invoke it with the SQL to excel smart service, but even this super cumbersome approach still doesn't (as far as I'm aware) contain the way to set / preserve word wrap on filled cells.

Hello,

We have upgraded to the latest version of this plug-in and encountering 'ERROR com.appiancorp.ps.exceltools.smartservice.ImportCsvToDatabase - Invalid object name' issue for the tables that were defined under a schema. It was working before. Kindly resolve the same.

Can we add documentation for the new functions and smart services added? THe one included does not have docs for Import Excel to Database

* Version 2.2.0  - 09 Nov 18

- Added Convert Excel to CSV Smart Service

- Added Import Excel to DB Smart Service

- Added LOAD CSV to DB Smart Service - fast CSV import for MySQL database that are not hosted on Appian Cloud

- Import CSV to DB can now decrypt EncryptedText fields (for importing into external DS)

- Deprecated CDT to/from Excel, Export Report, Export Excel Servlets (existing use will continue to work)

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."