GoAnywhere Forum

Hi there,

i have an issue addressing the data fields from an excel import named "exceldata".
Before i write this data field (index 9) out into an AS400 file, i want to do a substring but cannot get it to work.

I have tried like ${substring(exceldata[9],1,40)} in the "Map from" definition, but this always throws an error.

I never tried this before, can someone please point me in the right direction here?
XM code is attached.

As a second question, is there any way to have a numeric field rounded to 2 decimals during the mapping if it has more than 2 decimals in the excel cell?

Thank you,
Axel

AxelN,

First, I would look into the "ModifyRowset" task. This might help you get what you're looking for instead of the "Map From" definition. It's much simpler to handle these types of situations.

Also, this seems like it might be an issue with "Truncation" errors when inserting data into your SQL Table. You might want to review your JDBC URL to accommodate truncation. Something like:

jdbc:as400://192.168.1.54;data truncation=false;dateformat=iso;errors=full;naming=sql;prompt=false;time format=iso;transactionisolation=none;translate binary=true

This way, any data added during that connection would truncate at max width of the field.

Second, your rounding needs to be handled by the insert statement or the select statement of your SQL Function. It's much simpler that way.