parsing and nullpointer exception error while loading CSV file into MySQL database table

[Anonymous]

Hello everyone,

I'm trying to load multiple csv files into MySQL database( for now trying to load one single csv file ). For this, I'm using four components :

tDBConnection--> tFileInputdelimited-->tConvertType-->tMap-->tDBoutput(MySQL)

NOTE: I'm using tConvertType for retrieving empty values as null values.

So, when I run the job...I face the below error: 

Starting job load_job_source_db at 17:19 19/07/2019.

[statistics] connecting to socket on port 3618
[statistics] connected
Couldn't parse value for column 'Agglomerates' in 'row1', value is 'Carla Polson'. Details: java.lang.NumberFormatException: For input string: "Carla Polson"
Couldn't parse value for column 'Agglomerates' in 'row1', value is 'Carla Polson'. Details: java.lang.NumberFormatException: For input string: "Carla Polson"
Couldn't parse value for column 'Agglomerates' in 'row1', value is 'Dan'. Details: java.lang.NumberFormatException: For input string: "Dan"

Exception in component tMap_1 (load_job_source_db)
java.lang.NullPointerException
at java.text.SimpleDateFormat.parse(Unknown Source)
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:928)
at routines.TalendDate.parseDate(TalendDate.java:886)
at testproject1.load_job_source_db_0_1.load_job_source_db.tFileInputDelimited_1Process(load_job_source_db.java:3530)
at testproject1.load_job_source_db_0_1.load_job_source_db.tDBConnection_1Process(load_job_source_db.java:453)
at testproject1.load_job_source_db_0_1.load_job_source_db.runJobInTOS(load_job_source_db.java:4295)
at testproject1.load_job_source_db_0_1.load_job_source_db.main(load_job_source_db.java:4145)
[statistics] disconnected

Job load_job_source_db ended at 17:19 19/07/2019. [exit code=1]

I'm attaching screenshots: 

1) my design workspace

2) tFileImputdelimited component properties

3)tmap mappings

4)tDBOutput component properties

Please help me with this, also let me know if you guys need anything else.

Thanks & regards,

Praneeth Reddy

[Anonymous]

Hi,

    Your input schema seems to be wrong. It is trying to load the string value to a numeric data type column.

Couldn't parse value for column 'Agglomerates' in 'row1', value is 'Carla Polson'. Details: java.lang.NumberFormatException: For input string: "Carla Polson"

   First thing you will have to do is to double check your schema for all the components. Please also add Die on Error check box in the tFileInputDelimited so that you will get error while reading the file itself. Since you have not selected it, the wrong data is passed to next component where it is failing.

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 🙂

[Anonymous]

Hello Nikhil,

Thanks for our reply. I tried checking 'die on error' in tfileinputdelimited.

I was able to get rid of parsing error for now but I still receive another parsing error :

Exception in component tFileInputDelimited_1 (loading_SDB)
java.lang.RuntimeException: Couldn't parse value for column 'Phone' in 'row1', value is '773-6211'. Details: java.lang.NumberFormatException: For input string: "773-6211"
at testproject1.loading_sdb_0_1.loading_SDB.tFileInputDelimited_1Process(loading_SDB.java:3613)
at testproject1.loading_sdb_0_1.loading_SDB.tDBConnection_1Process(loading_SDB.java:463)
at testproject1.loading_sdb_0_1.loading_SDB.runJobInTOS(loading_SDB.java:5336)
at testproject1.loading_sdb_0_1.loading_SDB.main(loading_SDB.java:5186)
Caused by: java.lang.NumberFormatException: For input string: "773-6211"
at java.lang.NumberFormatException.forInputString(Unknown Source)
at java.lang.Long.parseLong(Unknown Source)
at java.lang.Long.parseLong(Unknown Source)
at routines.system.ParserUtils.parseTo_long(ParserUtils.java:174)
at routines.system.ParserUtils.parseTo_Long(ParserUtils.java:189)
at testproject1.loading_sdb_0_1.loading_SDB.tFileInputDelimited_1Process(loading_SDB.java:3604)
... 3 more
[statistics] disconnected

Job loading_SDB ended at 14:01 22/07/2019. [exit code=1]

I understand the value '773-6211' has a special character which is not allowing to let it parse.

I'm unable to remove special character from 'phone' column( as it's a long datatype column).

Is there any workaround for this?

Kindly, please help and looking forward to you reply.

Thanks and regards,

Praneeth

[Anonymous]

Hi Praneeth,

     The first step is to use replaceAll java function to remove the "-" from incoming string value. There are lot of posts in Talend community in this subject area like the one below.

https://community.talend.com/t5/Design-and-Development/resolved-Replace-string-with-quot-quot-with-q...

     Once you remove the problematic characters, use tConvertType component to convert the incoming String data to any numeric data type of your choice.

     I hope I have answered your query. Please spare a second to mark the topic as resolved 🙂

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 🙂

[Anonymous]

Hello Nikhil,

I agree and I've already used ReplaceAll function in tmap. As mentioned above, the incoming data from .csv file for 'Phone' column is of long datatype. So, I'm unable to apply replaceall function on this 'phone' column.

I'm attaching screenshots for my current job design. Also, I'm quite new to Talend so please let me know if I'm going in the right direction.

I'm using tmap_1 to convert Phone column(long datatype) to string datatype by using below expression:

row1.Phone.toString()

later, I'm using tmmap-2 to replaceall function as below:

conv_output.Phone.replaceAll("\\D","")

But still I'm facing the error.Sorry for the trouble, I would really appreciate if you can schedule a webinar to resolve issues faster.

Thanks and regards,

Praneeth

Thanks and regards,

Praneeth

job_design2.PNG

[Anonymous]

Hi Praneeth,

    Please read the phone data from file itself as String. Then do the replace all function to remove the unwanted characters. Once the data is in right format, you can use tConvertType to convert the value to Long or you can retain the data in String itself.

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 🙂

[Anonymous]

Hello Nikhil,

Followed your steps and I've loaded the csv file from directory as desired in tFileinputdelimited. 

I used following jjob design:

DB_connection-->tFileInputdelimited-->tConvertType-->tMap-->DB_output(Mysql)

Though, I use tConvertType to read empty values as nulls, I still receive below error:

Exception in component tMap_1 (loading_SDB_2)
java.lang.NullPointerException
at testproject1.loading_sdb_2_0_1.loading_SDB_2.tFileInputDelimited_1Process(loading_SDB_2.java:3830)
at testproject1.loading_sdb_2_0_1.loading_SDB_2.tDBConnection_1Process(loading_SDB_2.java:453)
at testproject1.loading_sdb_2_0_1.loading_SDB_2.runJobInTOS(loading_SDB_2.java:4618)
at testproject1.loading_sdb_2_0_1.loading_SDB_2.main(loading_SDB_2.java:4468)
[statistics] disconnected

Job loading_SDB_2 ended at 15:37 23/07/2019. [exit code=1]

So, I used expressions in tmap to check for nulls:if true: if false method. As I have float values while I'm reading my input file...I'm not sure how to check for nulls float datatype column. Also, I'm attaching screenshots of my tMap configurations please look into it and let me know how to solve this.

NOTE: when I uncheck 'die on error' in tMap...the job runs succesfully but I see no data in my MySQL database.

Thanks and Regards,

Praneeth

tmap_11.PNG
tmap_22.PNG

[Anonymous]

Hi Praneeth,

     The issue is because you are trying to match an input null value value in the equal function (which you have used in almost all your string variables. If you are reading from a file, the empty value is ideally considered as a null.

     Even if you have to compare empty string using an equal function, you should do it only after a Relational.ISNULL() function. So the first check should be null or not in your tMap before attempting for equal function. Then it should work fine.

      I hope I have answered your original query. Could you please spare a second to mark the topic as resolved? Kudos will be a bonus!

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 🙂

[Anonymous]

Hello Nikhil,

Thank you and I really appreciate your patience. Using Relational.ISNULL() I could get rid of nullpointerexception error.

My job runs successfully with no errors at all but still I don't see any data being populated in the MySQL database table.

I even checked 'die on error'  in my tDBoutput(MySQL) component. I'm attaching screenshots please have a look at it.

Thanks and regards,

Praneeth

job_design_3.PNG
tDB_prop_2.PNG

[Anonymous · Accepted solution]

Hi Praneeth,

     Its because you have not committed the values to database.

     If you want to do auto commit, please select the option in your DB connection component.

    Else you can also use a tDBCommit and connect it through OnSubJobOK from previous subjob.

     Please spare a second to mark the topic as resolved 🙂

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 🙂