Excel Tools Plugin (v2.8.2): Handling "Error executing SQL" due to unexpected trailing whitespace in CSV import

Question

Hi everyone,I am currently using the Excel Tools Plugin (v2.8.2) and encountering unpredictable loading issues caused by the length of incoming excel cell values.Our Setup & Scenario:Data Volume: 140 columns and approximately 10,000 rows.	Database: Staging table columns are already configured with large text lengths.	Process Flow:	Convert Excel to CSV using the "Excel to CSV" smart service (Processes successfully).		Import CSV to Database using the "Import CSV to Database" smart service.				The Issue:During step 2, the process fails with the error message: "Error executing SQL". Upon investigation, we found the root cause is hidden trailing whitespace. For example, some cells contain a short text value followed by up to 5,000 trailing spaces, causing the total string length to exceed the database column limits.My Questions:Does the "Import CSV to Database" smart service have a built-in configuration to automatically trim trailing/leading whitespaces from cell values during processing?	Are there alternative AppMarket smart services or best practices you recommend to clean this data before database insertion?	If you have faced this specific plugin limitation, what approach did you take to resolve it efficiently at this data volume?	Appreciate any insights or workarounds the community can share!

mikes0011 · Accepted Answer

@vigneshkumarr0001- i mean you would build an Appian-based custom parser tool which you run manually over a given page of data from the large input file. this would use a “read CSV with paging” smart service rule, e.g. “previewcsvforimport()” from Excel Tools.For dev purposes, set your paging size to something trivial like 5 rows (something very very easy), build outcolumn-by-column parsing logic, and incorporate into an interface that wraps that raw data up into CDT or RecordType data and you can write rows with a button click. Subsequent pages can be loaded with additional button click(S). The parser can do a “sanity check” while reading each row to determine whether a matching DB entry already exists. After you have test instances working, increase the paging read size to something larger, plug in the full-size file, and iterate through it a page at a time. Even a full 10,000 row CSV can be manually churned through in a minute or two with modestly large page sizes like 500 items, especially if this is something you need infrequently (like once or only once in a while).Attaching screenshot of the top portion of one such CSV parser I wrote and use actively.parser

mikes0011 · Answer

I usually just build a custom parser in an Appian interface as an ad-hoc tool. This allows you to read in pages of data at a time (at least while testing and scaling up), and do bespoke data cleansing tasks on a column-by-column basis as you see fit, without relying on the (sometimes unreliable) black-box mechanics of the fully-automatic plugin importer.

Sign up

Login with SSO

Login to the community

Login with SSO