For input string: "1 ano"

[iansoares]

Hello, i have a large document in tfileinputExcel that pass trough an tMap

in metadata all the fields are String with only two exceptions(prices)

This is my metadata, and when i run my job i got this stupid issue "For input string"

The Field "1 ano" is inside [Garantia_Balcao]

You can check this out in my xls file above

Anyone can help me in this issue?

[rhall_2_0 · Accepted solution]

What you could try is this. I am not promising it will solve your problem, but I think there is a good chance it will.

1) Create a job which has a different flow for each each Excel sheet. So if you have 10 sheets, create 10 flows (Subjobs). One for each sheet. When you set up the Excel input file for each sheet, limit it to the sheet you want and specify only the schema for that sheet.

2) Create a parent job which will use the tFileList to pull the files in one at a time and iterate through the job I have described above. So essentially you have a tFileList---->tRunJob (calling the child job described above).

First test this with 1 flow in the child job. So something like this....

tFileInputExcel (only sheet1) -----> tLogRow

Then if that works, add the following....

tFileInputExcel (Sheet 1)----->tLogRow

|

| (OnSubJobOk)

|

tFileInputExcel (Sheet 2) -----> tLogRow

Then add a third sheet and fourth, etc.

Please be aware that any changes to the file that is supplied to you will create issues for your job. So it might be a good idea to try and negotiate a slightly better format from your supplier of files. 

[akumar2301 · Accepted solution]

Another suggestion , convert all Double(in schemas) to String and after tJoin convert Price column to Double using Double.parseDouble(row1.price) with you formula.

You will not have parsing error string.

[rhall_2_0]

Is the data returned properly or is it blank? Do you get any other messages other than that one?

[iansoares]

Yes the data return properly,
I only got this "For input string" in other fields too in others Sheets

[nikhilthampi]

Hi,

    I believe the metadata in the schema defined and your actual file are not matching.

     There are some previous posts in our Talend community on the same issue. Could you please refer them too?

https://community.talend.com/t5/Design-and-Development/resolved-error-talend-For-input-string/td-p/1...

https://community.talend.com/t5/Design-and-Development/Error-on-run-shows-For-input-string-quot-599-...

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

[iansoares]

Hi,
I Already read all of these topics, but this is an alternative solution.
For one, or two fields this methods works properly
I got like 863 lines of this error "For input string" in differente Inputs of Excel.

" I believe the metadata in the schema defined and your actual file are not matching."
How my actual file have different schema ? could u please explain better?
I am using the Schema, and loading the same XLS.

Kinds Regards

[nikhilthampi]

Hi,

     Could you please take a good record and bad record (which is giving this error) and compare them with text tool like notepad++ instead of Excel? Excel will auto format lot of details and may not be giving right results. Most probably you will find that the schema will have error where "1 ano" is going to Double datatype Street_price column (or even worse Gross price).

     It might be for only certain records where the schema mismatch is happening. 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

[iansoares]

Hello,

You mean to compare fields? Copy and Paste in Notepad ++ (or some text editor) two columns, one which error appears and another that works properly?

Please be more specified what u want me to do, with examples will be better to understand.

Regards,

[nikhilthampi]

Hi,

    I was trying to say to open the input data file with textpad or notepad++ instead of excel. You do not have to use the entire data set and you can use a sample of 10 records for verification.

    There could be layout changes in the data file. If you can share the current data file (after removing any sensitive information) and your job, I can also look the flow from my end.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

[iansoares]

Hi,

I did not understand how to open the input data in Notepad.

I Uploaded a sample of the Content in XLS for u and a Sample Job, in this job "1 ano" doesnt appear the error of For input string, but appears in other field

For input string: "UPC"

Also an image of my current job

You can check that the content of error changed, but still the same issue.

If u cant solve the problem, i will upload my full job and sheet.

Regards

[akumar2301]

for UPC , it is not able to parse 1st record which is header.Please add  header as 1 in tfileinputexcel.

I guess  '1 ano issue' is resolved as it is parsed well in your sample job.

Also You can select die on error in tfileinputexcel to see where parsing fails actually.

[iansoares]

Yes, UPC is the header i understand, but my error persists in my big Job, the error appears random form, i cant spread my full current Job

To Remind, my data parse correctly, but this console is annoying "For input for string" like 1 hundred times

I also uploaded my Sample Input in "Zip" with my current Test Job

[akumar2301]

Did you try "Die on Error" option on Input file component?
did it fail during Parsing ?

Is the column position is same in all sheets ?

[iansoares]

I will try the Die on Error, but the data parse correctly, but this console still saying this annoying message

And the columns doesnt still the same in all sheets

But i have a Metadata for every Sheet, i think this isnt the problem

[iansoares]

When i use t Die Error on Input, nothing happen, the console still the same and the job complete normally

[rhall_2_0]

Hi @iansoares is it possible for you to post a copy of your file that causes this so that I can take a look at it? Please don't post it if it contains any personal or confidential data. If it does contain personal or confidential data, could you remove that data or create a version of the file that is safe to share, but still exhibits the same problem? I *think* I might know why this is happening, but I need to see the file to test my theory.

[iansoares]

I sent to your inbox the Input File, feel free to test your theory

[rhall_2_0]

Forget the previous post, I see that you have already uploaded your data. I have tested it and the only way I get anything like the UPC error is if I do not filter out the header row. Once I do that, the data runs OK. My config looks like this....

Can you share a file which does exhibit the original problem. If the file you shared is supposed to exhibit the original problem, it didn't when I imported it through the metadata ---> File Excel import and used that metadata for the component's schema. Can you try that?

[iansoares]

Did u enter the Schema?
Yes i sent you the file whose contains error

[iansoares]

I sent you in your Private Message the whole Input file, check it!

[rhall_2_0]

Sorry, you sent me that message while I was writing my last response. I will check it now :-)

[rhall_2_0]

Wow, that is a nasty Excel file to work with. You have your work cut out. The problems I noticed were as follows...

1) The sheets are littered with merged cells

2) The format of the data is somewhat random in parts. For example, some columns have primarily numbers, but then randomly have text. The Talend metadata can fall over on this sometimes.

3) The sheets have frozen cells

4) The sheets never start at the top of the page

However, I got round this by removing ALL formatting from all sheets, removing the blanks rows at the top and then generating the schemas for all sheets. Then I ran those schemas against the original file and debugged from there.

I found that some of the columns said they were floats, but further down the data seemed to have text. So I manually changed the column types for those. My schema ended up looking like yours, but I used float instead of double.

I then set the tFileInputExcel config up as below. Notice I set one sheet and point to it. If you read all sheets you will get errors. I also set the header to be row 8. 

My schema looks like this....

Once I had this setup, I didn't suffer from the issue you were suffering from anymore

[iansoares]

Yeah mate, thats a nasty Excel file hahaha. For me whose is starting in talend were insane to work with.

1) I know that excel contains Merged Cells (the file is sent every month from the partner, if i have to treat the data was not fit to my process)

2) I know that issue, i think this make Metadata confuse and get some errors

3) The same issue i told u on topic 1(if i have to treat data, will not fit to my process)

4) My Job already starts at 8 cell, i know that too .

Lets go,

I need to read All Sheets, because i need all those fields, i was working with one metadata for every Sheet, but reading in tFileList(One file)

There is a way to make "dynamic metadata" to use only one Metadata for all columns of each sheet? I believe u saw that has like 8 Sheets and in some cases 2 Sheets contain the same category of another one.

In the fields were float or something else i changed everything to String with only 2 exceptions(Street Price and Gross Price)

Unfortunally i need to read all sheets in Excel Files(Because need to me Dynamic, if someday i got from Partner the excel which contain "Acessorio" instead of "Acessorios" i will have to make all the whole job again. Imagine to do this job every month

Thanks for all the support everyone gave me in this Issue.

Btw i sent you a private message containing the screenshot of whole job to you see how insane it is.

Warms Regards,

[rhall_2_0 · Accepted solution]

What you could try is this. I am not promising it will solve your problem, but I think there is a good chance it will.

1) Create a job which has a different flow for each each Excel sheet. So if you have 10 sheets, create 10 flows (Subjobs). One for each sheet. When you set up the Excel input file for each sheet, limit it to the sheet you want and specify only the schema for that sheet.

2) Create a parent job which will use the tFileList to pull the files in one at a time and iterate through the job I have described above. So essentially you have a tFileList---->tRunJob (calling the child job described above).

First test this with 1 flow in the child job. So something like this....

tFileInputExcel (only sheet1) -----> tLogRow

Then if that works, add the following....

tFileInputExcel (Sheet 1)----->tLogRow

|

| (OnSubJobOk)

|

tFileInputExcel (Sheet 2) -----> tLogRow

Then add a third sheet and fourth, etc.

Please be aware that any changes to the file that is supplied to you will create issues for your job. So it might be a good idea to try and negotiate a slightly better format from your supplier of files. 

[akumar2301 · Accepted solution]

Another suggestion , convert all Double(in schemas) to String and after tJoin convert Price column to Double using Double.parseDouble(row1.price) with you formula.

You will not have parsing error string.

[iansoares]

@rhall_2_0 

Thanks for this method of differents sub Jobs, i will try that soon as possible (but i will have to specified every input of sheet right? Cant use "All sheets")

About the issues with the files supplied has changes will have trouble is a known issue. For my boss is not, he thinks can be dynamic as well to "understand" the changes from the Input.

@akumar2301 

Thanks mate, i will try to convert everything to String and later convert with Parse Double in tMap, i believe this could be the correct form to do this when working with many schemas in the same job(while reading all the file, all the time but using some sheets in each row)

@everyone 

There is a way to accept the two answers as solution? Haha you both help me a lot in this issue, Thanks

Kinds Regards,

[rhall_2_0]

Yes you will need to untick all sheets and only use one sheet at a time to test my solution. I suspect that the All Sheets option may be contributing to your issues.

We all have somebody we work with who thinks that computers can do magic and "understand" what a human does without A LOT of training/programming. My degree was in artificial intelligence and if computers could "understand" as much as people assume they can, we wouldn't need people like you and me to build things for them.....in fact we wouldn't need them ;-)

With regard to accepting multiple solutions, you can do that. As the person who raised the question you can just click on the Accept Solution button at the bottom of each post you think has helped you resolve this.

[iansoares]

Ok

Yes i think in IT progamming the hardest thing is to explain to ppl did not know progaming as well some things isnt easy to make. All progaming require some train and a lot of time.

Yes if computers could make all the stuff ppl can make is no needed ppl anymore.