Why an error occurs when writing a file read with fixed length connector to a table?

Question

When I tried to write a file read with the fixed length connector to a database table, I got the error: 'Could not convert from String type to BigDecimal type.'

The column in the table is a numeric type and allows for the storage of null data.
Also, the error can be avoided if the null value is converted to zero after trimming with Mapper's logic, but if the trim excludes whitespace, the error occurs.

What is the cause of the error and how can I deal with it?

Answer

Cause

This error occurs when a string cannot be converted to a numeric value.
For example, an empty string cannot be converted to a numeric value, so the error occurs when an empty string is set to a numeric type item.
　
When a value is set to a numeric item in a database or application as in this case, HULFT Square internally converts string data to numeric values.
Therefore, if you try to set empty string data to a numeric type item, an error will occur.
　
In the Read Fixed-Length File operation of the fixed-length connector, values of columns with no data are read as null or empty characters, depending on the setting.
Please refer to the manual below for the condition of null or empty character.


Workaround

Use Mapper's Replace Null/Empty String logic to convert empty characters to arbitrary numbers such as zero.

Read Fixed-Length File - Null and empty string
https://www.hulft.com/help/en-us/HULFTSquare/Content/Designer/Connector/fixedlength_get_data.htm

　
Replace Null/Empty String
https://www.hulft.com/help/en-us/HULFTSquare/Content/Designer/Mapper/ReplaceNullBlankWithNumber.htm