Pentaho CSV File Input step - comma delimiter in enclosures

Question

I have weird problem with Pentaho 6.1 CSV file Input step.

I have following values in the CSV files:

"2016-09-16","test","","test","test, S.L.","[test]","test",39.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000

I use in CSV file Input step:

Delimiter: ,
Enclosure: "

Preview option works perfectly, but when I try to import that content to database table and execute transformation I am getting following error:

2016/09/23 22:04:46 - Write to log.0 - 2016/09/23 22:04:46 - Write to log.0 - 2016/09/23 22:04:46 - Write to log.0 - at org.pentaho.di.core.row.value.ValueMetaBase.convertStringToNumber(ValueMetaBase.java:803) 2016/09/23 22:04:46 - Write to log.0 - at org.pentaho.di.core.row.value.ValueMetaBase.getNumber(ValueMetaBase.java:1631) 2016/09/23 22:04:46 - Write to log.0 - ... 7 more 2016/09/23 22:04:46 - Write to log.0 - Caused by: org.pentaho.di.core.exception.KettleValueException: 2016/09/23 22:04:46 - Write to log.0 - requests String : couldn't convert String to number : non-numeric character found at position 1 for value [S.L.","[test]","test",38.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000

I assume that it has a problem with following value "test, S.L." (column 5). I don't understand why because Preview option works.

Answer 1

I tried on your input set, and believe it or not, I had no trouble with the embedded comma. What seemed to be the problem was conversion of the number fields. I set them to the Number data type and fiddled with masks, but the only thing that made it work was specifying a trim type of "Left". When I did that, all was well.