Excel Input Pentaho - String to Number

I'm trying to make a xlsx input in Pentaho, but it keeps giving me this error message:
"Unexpected conversion error while converting value [v String] to a Number"

I have a value column that I'm trying to transform from string to number.

In the line 245 of my excel I have USD 11100.00 and in other lines just the values without the USD, could that be the problem? If so, do you guys have any idea how to solve it?

I need to transform from string to number without pulling the USD. Just the numbers.

I need to transform from string to number without pulling the USD. Just the numbers.

The answer is going to depend on how much room you have to impose your format or how many "problems" on your entry data you are going to find.

You have a non-standard input in the sense that the file has rows/columns mixing formats. You have various options:

1. This file you need to read is generated by your company/someone you know and can talk to so you can reject the file because not following the standard format, and they can generate a new one with the format expected.
2. You don't have any say on the file, you are processing data from the internet or public data and you don't have any say on the data.
   • You ignore all the rows that don't follow the format you are expecting, maybe you generate a file with all the rejected rows (there are additional options in the Input step for that) to process these rejected rows manually.
   • You treat initially that column as a string, and then use the Regexp step to extract only the numbers, if the regexp expression can't extract numbers, the processed column will be a null and load the data with null values when you can't read it as a number.

Depending on the nature of your project, volume of data, and expected volume of non-standard data any of the proposed solutions might work.