pentaho/pentaho-kettle · error · KettleValueException

<toString()> : couldn't convert String to number with…

Error message

<toString()> : couldn't convert String to number with <empty format|format [getFormatMask()]>: unexpected character found at position <parsePosition.getErrorIndex() + 1> for value [<string>]

What it means

Thrown by ValueMetaBase.convertStringToNumber when DecimalFormat.parse leaves unconsumed characters: ParsePosition.getIndex() < string.length() means trailing junk after the numeric portion. The message reports the 1-based position of the unexpected character, the format mask, and the input string.

Solutions

  1. Trim/clean the input string (remove trailing units, symbols, non-breaking spaces) before conversion
  2. Set the correct format mask with setConversionMask matching the data (including grouping/decimal separators)
  3. Set the proper locale via setConversionLocale so separators match
  4. Pre-validate with a regex like ^[+-]?[0-9.,]+$ before calling getNumber

Example fix

// before
Double d = valueMeta.getNumber("1.234,56 kg"); // throws at position 9
// after
String cleaned = raw.replaceAll("[^0-9,.-]", "").trim();
Double d = valueMeta.getNumber(cleaned);
Defensive patterns

Strategy: validation

Validate before calling

static boolean isFullyNumeric(String s, char groupSep, char decSep) {
  if (s == null || s.isEmpty()) return false;
  String t = s.trim();
  String regex = "^[+-]?[0-9" + java.util.regex.Pattern.quote(String.valueOf(groupSep))
      + java.util.regex.Pattern.quote(String.valueOf(decSep)) + "]+$";
  return t.matches(regex);
}

Type guard

static boolean parsesFully(java.text.DecimalFormat fmt, String s) {
  java.text.ParsePosition pos = new java.text.ParsePosition(0);
  Number n = fmt.parse(s, pos);
  return n != null && pos.getIndex() == s.trim().length();
}

Try / catch

try {
  Number n = valueMeta.getNumber(str);
} catch (KettleValueException e) {
  log.error("Bad number '" + str + "': " + e.getMessage());
  routeToErrorRow(str, "non-numeric trailing characters");
}

Prevention

When it happens

Trigger: Calling getNumber()/convertStringToNumber() with a string like '123abc', '1 234,5 EUR', or a value that does not fully match getFormatMask() (grouping separators, currency symbols, or spaces in the wrong place) when no lenient 'format.parse(string)' path is used.

Common situations: Export files with trailing units ('kg', '%') or currency symbols; wrong decimal/grouping separator for the configured locale; extra whitespace or BOM at the end of the value; mask changed after the data format changed.

Understand the failure class

Background: "Invalid ... format", "must be in format X", "does not look like a ..." — invalid argument format errors across CLI tools and libraries — this error's family across 17 libraries.

Related errors

AI-assisted analysis of pentaho/pentaho-kettle@f3058517a1 (2026-09-13). Data as JSON: /api/errors/9a4a48bd40f1b491. Report an issue: GitHub.

Appendix: source

Thrown at core/src/main/java/org/pentaho/di/core/row/value/ValueMetaBase.java:1029

    string = Const.trimToType( string, getTrimType() ); // see if trimming needs
    // to be performed before
    // conversion

    if ( Utils.isEmpty( string ) ) {
      return null;
    }

    try {
      DecimalFormat format = getDecimalFormat( false );
      Number number;
      if ( lenientStringToNumber ) {
        number = format.parse( string );
      } else {
        ParsePosition parsePosition = new ParsePosition( 0 );
        number = format.parse( string, parsePosition );

        if ( parsePosition.getIndex() < string.length() ) {
          throw new KettleValueException( toString()
              + " : couldn't convert String to number with " + ( ( getFormatMask() == null ) ? "empty format" : "format [" + getFormatMask() + "]" ) +  ": unexpected character found at position "
              + ( parsePosition.getErrorIndex() + 1 ) + " for value [" + string + "]" );
        }

      }

      return new Double( number.doubleValue() );
    } catch ( Exception e ) {
      throw new KettleValueException( toString() + " : couldn't convert String to number ", e );
    }
  }

  @Override
  public synchronized SimpleDateFormat getDateFormat() {
    return getDateFormat( getType() );
  }

  private synchronized SimpleDateFormat getDateFormat( int valueMetaType ) {

View on GitHub (pinned to f3058517a1)