For input string: "1 ano"

Six Stars

For input string: "1 ano"

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)

image.png

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

image.png

The Field "1 ano" is inside [Garantia_Balcao]

image.png

You can check this out in my xls file above

 

Anyone can help me in this issue?

 

 

 


Accepted Solutions
Community Manager

Re: For input string: "1 ano"

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. 

Seven Stars

Re: For input string: "1 ano"

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.
Regards
Abhishek KUMAR
( Please mark the post as resolved if you found your solution )

All Replies
Community Manager

Re: For input string: "1 ano"

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

Six Stars

Re: For input string: "1 ano"

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

Re: For input string: "1 ano"

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 :-)

 


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Six Stars

Re: For input string: "1 ano"

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

Employee

Re: For input string: "1 ano"

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 :-)


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Six Stars

Re: For input string: "1 ano"

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,
Employee

Re: For input string: "1 ano"

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 :-)


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Six Stars

Re: For input string: "1 ano"

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

image.png

 

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

 

Seven Stars

Re: For input string: "1 ano"

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.

Regards
Abhishek KUMAR
( Please mark the post as resolved if you found your solution )
Six Stars

Re: For input string: "1 ano"

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

Seven Stars

Re: For input string: "1 ano"

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 ?
Regards
Abhishek KUMAR
( Please mark the post as resolved if you found your solution )
Six Stars

Re: For input string: "1 ano"

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

Six Stars

Re: For input string: "1 ano"

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

Re: For input string: "1 ano"

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.

Six Stars

Re: For input string: "1 ano"

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

Community Manager

Re: For input string: "1 ano"

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....

Screenshot 2019-02-07 at 18.21.13.png

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?

Six Stars

Re: For input string: "1 ano"

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

Six Stars

Re: For input string: "1 ano"

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

Re: For input string: "1 ano"

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

Community Manager

Re: For input string: "1 ano"

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. 

Screenshot 2019-02-07 at 19.03.06.pngMy schema looks like this....

Screenshot 2019-02-07 at 19.03.32.png

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

Six Stars

Re: For input string: "1 ano"

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)

image.png

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,

 

 

 

Community Manager

Re: For input string: "1 ano"

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. 

Seven Stars

Re: For input string: "1 ano"

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.
Regards
Abhishek KUMAR
( Please mark the post as resolved if you found your solution )
Six Stars

Re: For input string: "1 ano"

@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,

Community Manager

Re: For input string: "1 ano"

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.

Six Stars

Re: For input string: "1 ano"

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.

Cloud Free Trial

Try Talend Cloud free for 30 days.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.