topic Re: read file and split data line by line into DB in Talend Studio

read file and split data line by line into DB

_AnonymousUser — Thu, 19 Jul 2012 08:56:29 GMT

I have a file :
FileType00000001~CustomerLoad~83e3ec9b-d824-48a5-8102-773b72ad8cca~20120717101648
#KeyDefinitionField~ImpGuid~RowId~FirstName~LastName~RsaId~PassportNumber~IfaNumber~HomeTelephone~WorkTelephone~MobileTelephone~Fax~EmailAddress
#KeyDefinitionField~ImpGuid~RowId~PolicyNumber~PolicyStatus~PolicyPremium
Customer00000001~~0~Nobongile~Langa~~~~~~~~
Policy0000000001~~0~402060005~I~99.00
Customer00000001~~1~Nobongile~Langa~~~~~~~~
Policy0000000001~~1~610473373~I~135.00
Customer00000001~~2~Nobongile~Mabunda~~~~~~~~
Policy0000000001~~2~403897536~I~99.00
Customer00000001~~3~Nobonile~August~~~~~~~~
I want to be ale to read line by line, check whether it is a Customer00000001 or Policy0000000001 and populate 2 different tables depending on what row i am in .
apologies still new to talend (java) .
regards

Re: read file and split data line by line into DB

Anonymous — Thu, 19 Jul 2012 09:14:40 GMT

HI
You might create a job as the following images.
Code in tExtractRegexFields

"^Policy0000000001"

"^Customer00000001"

Regards,
Pedro

Re: read file and split data line by line into DB

_AnonymousUser — Thu, 19 Jul 2012 10:19:22 GMT

okay ... that is what i was looking for.
a final question ? the entire row is put in one column, must I write to a temp file and extract from that temp file into the DB (separating the tilde delimited values) or is there and easier way.
I aso suppose i need to do this in 2 cycle , one for Customer0000001 and one for Policy00000001.
Regards

Re: read file and split data line by line into DB

Anonymous — Thu, 19 Jul 2012 10:27:02 GMT

Hi
Yes. You need to create two subjobs for this.
tFileInputDelimited--main-->tExtractRegexFields--main-->tExtractPostionalFields--main-->Output
The tExtractPostionalFields can help you extract columns with fixed pattern.
Regards,
Pedro

Re: read file and split data line by line into DB

_AnonymousUser — Thu, 19 Jul 2012 10:30:14 GMT

THANKS PEDRO
GREAT HELP !!!!!

Re: read file and split data line by line into DB

Anonymous — Thu, 19 Jul 2012 10:49:56 GMT

Hi,
You could also split the row by a tMap.
Create 2 output rows from one tMap where you filter the output on the substring Customer and Policy
this way you only need to parse your input file once.
Regards,
Arno