Question
Excel Tools Plugin (v2.8.2): Handling "Error executing SQL" due to unexpected trailing whitespace in CSV import
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!