How to parse a string name field (lastname, firstname middlename?

[profuse]

Hi,

From a CSV file, could someone tell me how to parse a string field that contains the persons lastname, firstname middlename? Where the lastname always ends in a comma, then first name and then the middle name is last.

Here is an example:

SMITH, JOHN LEO

Then I would like to move:

The lastname SMITH to a new column called lastname

The firstname JOHN to a new column called firstname

The middlename LEO to a new column called middlename

How do you parse it into 3 new columns?

Thank you in advance!

[Anonymous]

Hi,

   I believe you are looking for below solution.

The tMap is as shown below.

Please refer the functions used below.

last_part =       row1.data.substring(row1.data.indexOf(",")+1).trim() 
fname     =       row1.data.substring(0,row1.data.indexOf(",")) 
lname     =       Var.last_part.substring(0,Var.last_part.indexOf(" "))
mname   =       Var.last_part.substring(Var.last_part.indexOf(" ")+1)

Please note that I have created the solution for happy path. If some of the data is not there, it will result in NULL and you will have to add the additional logic for NULL handling. Well, I would let you handle it as there are lot of community posts already related to that topic.

Please spare a second to mark the topic as resolved. Kudos will be a bonus from your 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

[profuse]

I added your code. But when it runs it error's.

See attached document for screen pages (showing the tMap and run error messages)

Here's the details of my code:

1. My input field name is: row1.LicenseeName

2. The Var Expression is: row1.LicenseeName.substring(row1.LicenseeName.indexOf(",")+1).trim()

3. Variable name is:  last_part

4. The output field names contain the following:

LicenseeName =row1.LicenseeName 

fname = row1.LicenseeName.substring(0,row1.LicenseeName.indexOf(",")) 

lname = Var.last_part.substring(0,Var.last_part.indexOf(" ")) 

mname = Var.last_part.substring(Var.last_part.indexOf(" ")+1) 

It errors on the first row where the row1.LicenseeName = KNOWLES, JONATHAN

Please advise of what to do next?

Thanks

TalendError12132019.docx

[Anonymous · Accepted solution]

Hi,

    Like I mentioned in my previous post, I know this error will come and my intention was to let you try to reach the solution yourself. I believe you are stuck now and here is the solution. You need to make minor changes in the code by adding java contains function in your existing code. The output will look like below.

You need to use below java code in tMap.

lname =

Var.last_part.contains(" ")? Var.last_part.substring(0,Var.last_part.indexOf(" ")) : Var.last_part 

mname =

Var.last_part.contains(" ")? Var.last_part.substring(Var.last_part.indexOf(" ")+1):"" 

I would really appreciate if you could spare a second to mark the topic as resolved as it will help other Community members during their reference and its a way of thanking the author for volunteering their time. Kudos will be a bonus from your 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

[profuse]

Nikhil Thampi,

Wow that's great coding, the perfect solution!

Your absolutely the best.

Thank You!!!