0

I have tried pentaho for loading csv file and storing into Database. One of the column in CSV have multiple type values say 2.7777777777777E-06, 0, 2222, 2 , 35.023

My transformation doesn't support type conversion. Please help me for getting this fixed with right pattern for all type of numbers.

Error Message:

Modified Java Script Value.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Unexpected error 2017/11/24 10:17:01 - Modified Java Script Value.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : org.pentaho.di.core.exception.KettleValueException: 2017/11/24 10:17:01 - Modified Java Script Value.0 - Javascript error: 2017/11/24 10:17:01 - Modified Java Script Value.0 - 2017/11/24 10:17:01 - Modified Java Script Value.0 - Unexpected error 2017/11/24 10:17:01 - Modified Java Script Value.0 - 2017/11/24 10:17:01 - Modified Java Script Value.0 - Unexpected conversion error while converting value [Average String] to a Number 2017/11/24 10:17:01 - Modified Java Script Value.0 - 2017/11/24 10:17:01 - Modified Java Script Value.0 - Average String : couldn't convert String to number 2017/11/24 10:17:01 - Modified Java Script Value.0 - 2017/11/24 10:17:01 - Modified Java Script Value.0 - Average String : couldn't convert String to number : non-numeric character found at position 18 for value [2.777777777777775e-06] 2017/11/24 10:17:01 - Modified Java Script Value.0 - 2017/11/24 10:17:01 - Modified Java Script Value.0 - 2017/11/24 10:17:01 - Modified Java Script Value.0 - 2017/11/24 10:17:01 - Modified Java Script Value.0 - 2017/11/24 10:17:01 - Modified Java Script Value.0 - 2017/11/24 10:17:01 - Modified Java Script Value.0 - at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.addValues(ScriptValuesMod.java:475) 2017/11/24 10:17:01 - Modified Java Script Value.0 - at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.processRow(ScriptValuesMod.java:541) 2017/11/24 10:17:01 - Modified Java Script Value.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62) 2017/11/24 10:17:01 - Modified Java Script Value.0 - at java.lang.Thread.run(Thread.java:748) 2017/11/24 10:17:01 - Modified Java Script Value.0 - Caused by: org.pentaho.di.core.exception.KettleValueException: 2017/11/24 10:17:01 - Modified Java Script Value.0 - Unexpected error 2017/11/24 10:17:01 - Modified Java Script Value.0 - 2017/11/24 10:17:01 - Modified Java Script Value.0 - Unexpected conversion error while converting value [Average String] to a Number 2017/11/24 10:17:01 - Modified Java Script Value.0 - 2017/11/24 10:17:01 - Modified Java Script Value.0 - Average String : couldn't convert String to number

sangeeth kumar
  • 319
  • 2
  • 7
  • 23
  • 1
    These number can all get the type Number in the CSV reader. Your error comes form a Date, read with the type String, that does not want to be converted in a Number by a Modified Javascript Step. – AlainD Nov 24 '17 at 11:56
  • Thanks for your comment. can you please explain this error "Modified Java Script Value.0 - Average String : couldn't convert String to number : non-numeric character found at position 18 for value [2.777777777777775e-06] " Actually i need to fix this issue. – sangeeth kumar Nov 29 '17 at 08:58

1 Answers1

0

The Average String is read as a String not as Number by the CSV reader, and the PDI does not want it as a Number because it contains "e". Greetings goes to Javascript, not to PDI.

Solution 1: The best is to read is as a Number: enter image description here

Solution 2: If for some reason you cannot, you can use the Select values step to change its type in the Metadata tab.

Solution 3: If you cannot cast the String in a Number before to be in the Javascript step, you have to use an explicit javascript casting: var x = Number(x); However, your variable name contains a space, and cannot be mapped automatically in a javascript variable. The hack is do: 

this["Average String"] = Number(this["Average String"]);

Then, you can get the Average String in the field list of the bottom table as a Number.

AlainD
  • 6,187
  • 3
  • 17
  • 31