topic Re: tFileInputExcel can't read Integer value more than 2 digits in Talend Studio

tFileInputExcel can't read Integer value more than 2 digits

Anonymous — Fri, 03 Jul 2015 07:14:42 GMT

Hi, I'm a newbie in Talend.. I found some issues when I'm trying to inserting data from Excel file into Talend. I try to insert Excel file using tFileInputExcel, but in the middle of the process Talend sent me an error message that stated "For Input String : xxxxxxxxxx". But Integer value with only 1 digit (Example: 2 or 5) can be inserted while the other value with 2 digits (Example: 10 or 14) can't be inserted.

Anybody ever experienced with this issue?

Thanks and Regards,
Liong

Re: tFileInputExcel can't read Integer value more than 2 digits

Anonymous — Fri, 03 Jul 2015 07:23:29 GMT

Here's the screenshot of the error..

Re: tFileInputExcel can't read Integer value more than 2 digits

Jcs19 — Fri, 03 Jul 2015 08:19:17 GMT

Hi, I'm a newbie in Talend.. I found some issues when I'm trying to inserting data from Excel file into Talend. I try to insert Excel file using tFileInputExcel, but in the middle of the process Talend sent me an error message that stated "For Input String : xxxxxxxxxx". But Integer value with only 1 digit (Example: 2 or 5) can be inserted while the other value with 2 digits (Example: 10 or 14) can't be inserted.

Anybody ever experienced with this issue?

Thanks and Regards,
Liong

The max value for an integer is 2147483647

Are you sure it's an integer ? it looks like a string... (yes, not all numbers are integers or floats, for example if your product_code is 201507010000001, it will be a string)
if it's really a number, then you can declare it as "long", its max value is 9223372036854775807 (dont forget to do the same on your DB)

Re: tFileInputExcel can't read Integer value more than 2 digits

Anonymous — Fri, 03 Jul 2015 09:27:02 GMT

Hi Jcs19,
Actually the error came out when I used several combination from Excel column to make an aggregate count using tAggregateRow.
For Example : 4182168210
41821682 is my sample product code. Its data type is String.
10 is my quantity of my the product. The data type is Integer.
I've tried some ways just now, include try to remove 1-2 character from my product code (Ex: 41821682 to 418216) and the process accepted it. So I wonder when the combination happened my product code and quantity were blended together into Integer since max capacity of Integer is 2147483647 so if the amount is above the max value then it will error.

So there's several ways to prevent the error, whether change all data type into String for temporary and change it back to what it supposed to be when the combination is done, or change my quantity data type to Long (actually I haven't try this way yet so I dunno whether it'll works or not)
Thanks and regards,
Liong

Re: tFileInputExcel can't read Integer value more than 2 digits

Jcs19 — Fri, 03 Jul 2015 10:53:43 GMT

Hi Jcs19,
Actually the error came out when I used several combination from Excel column to make an aggregate count using tAggregateRow.
For Example : 4182168210
41821682 is my sample product code. Its data type is String.
10 is my quantity of my the product. The data type is Integer.
I've tried some ways just now, include try to remove 1-2 character from my product code (Ex: 41821682 to 418216) and the process accepted it. So I wonder when the combination happened my product code and quantity were blended together into Integer since max capacity of Integer is 2147483647 so if the amount is above the max value then it will error.

So there's several ways to prevent the error, whether change all data type into String for temporary and change it back to what it supposed to be when the combination is done, or change my quantity data type to Long (actually I haven't try this way yet so I dunno whether it'll works or not)
Thanks and regards,
Liong

Yes, that was what I thought so
In your example
"41821682" should stay as a String and "10" should stay as an integer
but your output "4182168210" should be a string and not an integer or a long.
is your issue solved?

Re: tFileInputExcel can't read Integer value more than 2 digits

Anonymous — Fri, 03 Jul 2015 11:41:07 GMT

Hi Jcs19,

You're right, my product number should stay as String and quantity as Integer. But the error still occurred. So I tried to create a new column beside my product number in my Excel file then I replace all value of the product number in original column with same value as I created in temporary column one. After that, it works.

I still don't know the reason why is this happened. I suspect that this is related to data type or formula (vlookup, etc.) issues that I used in my Excel file. Because when I replaced the original value with the duplicated one, I only copied the value instead of copy with the formula.
Thanks and Regards,
Liong