Pentaho Kettle conversion from String to Integer/Number error

csv pentaho etl kettle data-integration
user2552108 picture user2552108 · Jun 18, 2016 · Viewed 8.5k times · Source

I am new to Pentaho Kettle and I am trying to build a simple data transformation (filter, data conversion, etc). But I keep getting errors when reading my CSV data file (whether using CSV File Input or Text File Input).

The error is:

... couldn't convert String to number : non-numeric character found at position 1 for value [ ]

What does this mean exactly and how do I handle it?

Thank you in advance

Answer

user2552108 picture user2552108 · Jun 21, 2016

I have solved it. The idea is similar to what @nsousa suggested, but I didn't use the Trim option because I tried it and it didn't work on my case.

What I did is specify that if the value is a single space, it is set to null. In the Fields tab of the Text File Input, set the Null if column to space .

enter image description here

Related questions

How to concatenate text from multiple rows into a single text string in SQL server?

Consider a database table holding names, with three rows: Peter Paul Mary Is there an easy way to turn this into a single string of Peter, Paul, Mary?

Read More
Writing a pandas DataFrame to CSV file

I have a dataframe in pandas which I would like to write to a CSV file. I am doing this using: df.to_csv('out.csv') And getting the error: UnicodeEncodeError: 'ascii' codec can't encode character u'\u03b1' …

Read More
How to output MySQL query results in CSV format?

Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format? Here's what I'm doing now: mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/ /,/g' | …

Read More