topic Re: parsing and nullpointer exception error while loading CSV file into MySQL database table in Talend Studio

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

Anonymous — Sun, 21 Jul 2019 16:59:18 GMT

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

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

Anonymous — Mon, 22 Jul 2019 15:33:57 GMT

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 🙂

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

Anonymous — Mon, 22 Jul 2019 19:15:48 GMT

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

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

Anonymous — Mon, 22 Jul 2019 20:05:11 GMT

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-quot-quot/td-p/92389

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 🙂

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

Anonymous — Mon, 22 Jul 2019 20:50:17 GMT

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

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

Anonymous — Mon, 22 Jul 2019 21:45:28 GMT

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 🙂

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

Anonymous — Tue, 23 Jul 2019 20:42:47 GMT

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

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

Anonymous — Tue, 23 Jul 2019 21:30:12 GMT

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 🙂

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

Anonymous — Wed, 24 Jul 2019 15:01:19 GMT

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

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

Anonymous — Wed, 24 Jul 2019 15:06:56 GMT

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 🙂

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

Anonymous — Wed, 24 Jul 2019 16:09:20 GMT

Hey Nikhil,

Thanks a Lot! It works!....now I'm trying to load multiple .csv files of the same format to the same MySQL DB I've used previously.

I used tFileList for iteration of all the files. But I receive below error for the tFileList component :

Error in the component's properties:The method add(String) in the type List<String> is not applicable for the arguments ().

I'm attaching screenshots again, please have a look at them and let me know what's the issue.

Thanks and regards,

Praneeth

job_design_4.PNG
tFileInput_prop.PNG
tFileList_prop.PNG

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

Anonymous — Wed, 24 Jul 2019 16:13:34 GMT

Hi Praneeth,

I would recommend to create it as a separate post so that original query will get sufficient focus. We have resolved your original query and this is a totally different issue. Could you please spare a second to mark the current topic as resolved? It will help other community members during their reference.

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 🙂

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

Anonymous — Wed, 24 Jul 2019 16:20:28 GMT

Okay. I've posted another topic under subject name 'Loading multiple CSV files into MySQL database '. Kindly, please address my issue over there.

Thanks and regards,

Praneeth