Data Type Conversion Failure. Numeric Fields Must Be Treated as Text to Prevent ETL Errors

[emy-devfullstack]

Guys, I’m a beginner in the Apache Hop community, but I use this tool every day at my company.
I noticed a strange behavior when I import an Excel file. Hop doesnt understand the data format, and it is not possible to convert fields that have two different formats.
To solve this, I created a JavaScript function, but this is very laborious and I don’t think it is the best method to solve the problem.
My question is, before i could import the Excel file without any problems, but now I can’t. Has anyone experienced this?

[hansva]

Best way to do this would be to mark the field as a string. As the error suggests you have text in a field you defined as a number

[emy-devfullstack]

Yes, I define them as text fields and then convert them using JavaScript. However, these fields are not actually strings.

[hansva]

The error states that the in the 2nd row the 3rd column contains text. Maybe you have 2 header rows in that file?

[emy-devfullstack]

In my Excel file, there is no specific data type assigned because the cells are formatted as general. Therefore, i would expect Hop to allow me to interpret the data as either a number or a string.

[hansva]

In Hop each field has a specific type, there is no "mixed" type, look at it as a database table. If you define a column as a number you can't insert strings to that column. I don't know the goal you have to achieve with that column that has a mixed type or what your end result or destination looks like. But using a script transform with some logic on what to do if it is a string or a number could be the right approach... In this case it just looked like you had 2 header rows so my suggestion was to configure the transform to have 2 header rows. You can also use an error handling hop to ignore all rows which can't be converted. But I don't know your end goal and maybe just dropping the rows that can't convert that "codigo do vendor" value to a number isn't correct.