Import data with transform map into "decimal" field not working

[angel_jimenez]

Hi community!

I'm experiencing some issues with data import formats. I have an excel file which has (among other fields) one field with decimal data on it. This column may have decimal or not, depending on the row:

We are using "General" as excel format for those columns:

The target field of this column in the import is the "Actual hours" field on the resource_plan table, which is a "Decimal" field:

We have configured a staging table, where we are importing data and after that we have configured a transform map to bring data from staging table to the target table. The "Actual hours" field on the staging table have been configured on the same way as target field. It is a decimal field with same length and same attributes as target field:

We have configured a "transform entry" for this field on the transform map, which maps the source field on the staging table with the target field on the resource_plan target table. But when we import data, the system obviates the comma and decimals and in all cases import data as integer.

To solve this issue we have tried different approaches, but none of them worked fine:

• Use source field as string, and use parseFloat() on mapping field into decimal target field
• Use field script to "split" string in tow parts, and them compose decimal target field
• Use transform script "onBefore", "anAfter" or "onCompletion" using "update()" of the target field in order to parse the decimal characters of the target field
• Modify data format in excel file and use number

Any idea about what I'm missing in the approach?? Any help will be happily received!!

Thanks in advance!

[arielgritti]

Hi Angel,

Another test, change the format to use "." as decimal separator instead of "," in the excel data.

Please mark helpful or correct if I helped you
Thanks
Ariel

[angel_jimenez · Accepted solution]

Finally we managed to solve the issue. We ave modified the staging table field format from decimal to string and then use following script:

answer = (function transformEntry(source) {

    var ret = 0;
    if (source.u_actual_hours.indexOf(".") >= 0) {
        var int = source.u_actual_hours.toString().split(".")[0];
        var dec  = source.u_actual_hours.toString().split(".")[1];
		ret = int + "," + dec;
		return ret;
    }else{
		return source.u_actual_hours;
	}

})(source);