CODEWITHSUNDEEP
pentahokettle
t_barbz
t_barbz

Reputation: 757

Pentaho Spoon - Validate Fixed Width Input File Format

I'm trying to process a fixed width input file in pentaho and validate the format. The file will be a mixture of strings, numbers and dates. However when attempting to process a number field that has an incorrect character present (which i had expected would throw an error) it just reads the first part of the number and ignores the bad char.

I can recreate this issue with a very simple input file containing a single field:

enter image description here

I specify the expected number format, along with start position and length: enter image description here

On running the transformation i would have expected the 'Q' to cause an error instead the following result is displayed, just reading the first two digits "67" and padding the rest to match the specified format:

enter image description here

If the input file is formatted correctly it runs perfectly well, but need it to throw an error otherwise. Any suggestions would be awesome. Thanks!

Upvotes: 1

Views: 3429

Answers (1)

t_barbz
t_barbz

Reputation: 757

Just an FYI in case someone stumbles accross this question after hitting the same issues as myself.

I was able to construct a workaround by reading all values in the "Text File Input" step as strings, and then using a "Data Validator" step equipped with regex evaluation to ensure numbers were correctly formatted before parsing to number type with a following "Select Values" step.

Takes a bit longer to do this for every field, but was the most robust solution i could come up with.

Thanks

Upvotes: 1

Related Questions