How to treat consecutive delimiters as one ( like we have in Excel) in TFiledelimitedInput?

Highlighted
Five Stars

How to treat consecutive delimiters as one ( like we have in Excel) in TFiledelimitedInput?

 

Accepted Solutions
Highlighted
Eleven Stars

Re: How to treat consecutive delimiters as one ( like we have in Excel) in TFiledelimitedInput

Aah I found the error. Thanks for file

 

"^\\s*"+
"(\\w+)"+
"\\s+"+
"(\\w+)"+
"\\s+"+
"(\\w+)"+
"\\s+"+
"(\\w+)"+
"\\s+"+
"(\\w+)"+
"\\s*"

 

 

or

 

"^\\s*"+
"(\\S+)"+
"\\s+"+
"(\\S+)"+
"\\s+"+
"(\\S+)"+
"\\s+"+
"(\\S+)"+
"\\s+"+
"(\\S+)"+
"\\s*"

Regards
Abhishek KUMAR

View solution in original post


All Replies
Highlighted
Moderator

Re: How to treat consecutive delimiters as one ( like we have in Excel) in TFiledelimitedInput?

Hello,

 Is the problem in the way you define the delimiter? Would you mind sharing your tFileInputDelimited Settings + Advanced settings screenshots?

Could you please elaborate your case with an example with input and expected output values?

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Highlighted
Five Stars

Re: How to treat consecutive delimiters as one ( like we have in Excel) in TFiledelimitedInput?

Source Data:[ Not tab delimited]

 

ColA   ColB   ColC  ColD ColE

a1 a2   a3    a4   a5

b1   b2  b3   b4   b5

 

Expected Target Data:

 

ColA|ColB|ColC|ColD|ColE

a1|a2|a3|a4|a5

b1|b2|b3|b4|b5

 

Talend TFileInputdelimited Settings:

Talend Setting.jpg

 

Output:

Talend Output.jpg

I have tried using ‘\t’, ‘\\s+’ in the place of Field Separator.  I didn’t get the expected result

 

In Excel:

    I can achieve the same output using the below option in excel

Excel_screenshot.jpg

Highlighted
Eleven Stars

Re: How to treat consecutive delimiters as one ( like we have in Excel) in TFiledelimitedInput?

try regex tFileInputRegex

 

"^\\s*"+
"(\\w)"+
"\\s+"+
"(\\w)"+
"\\s+"+
"(\\w)"+
"\\s+"+
"(\\w)"+
"\\s+"+
"(\\w)$"

Regards
Abhishek KUMAR
Highlighted
Five Stars

Re: How to treat consecutive delimiters as one ( like we have in Excel) in TFiledelimitedInput?


Thanks, Abhishek for your input.
Still couldn't process the file. Getting the below error:
Delimiter_Issue.jpg

Highlighted
Eleven Stars

Re: How to treat consecutive delimiters as one ( like we have in Excel) in TFiledelimitedInput

May be after last col you have space in input

Try this



"^\\s*"+
"(\\w)"+
"\\s+"+
"(\\w)"+
"\\s+"+
"(\\w)"+
"\\s+"+
"(\\w)"+
"\\s+"+
"(\\w)"+
"\\s*"


Otherwise send me your file.
Regards
Abhishek KUMAR
Highlighted
Five Stars

Re: How to treat consecutive delimiters as one ( like we have in Excel) in TFiledelimitedInput

Attached is the Input file for your reference

Highlighted
Eleven Stars

Re: How to treat consecutive delimiters as one ( like we have in Excel) in TFiledelimitedInput

Aah I found the error. Thanks for file

 

"^\\s*"+
"(\\w+)"+
"\\s+"+
"(\\w+)"+
"\\s+"+
"(\\w+)"+
"\\s+"+
"(\\w+)"+
"\\s+"+
"(\\w+)"+
"\\s*"

 

 

or

 

"^\\s*"+
"(\\S+)"+
"\\s+"+
"(\\S+)"+
"\\s+"+
"(\\S+)"+
"\\s+"+
"(\\S+)"+
"\\s+"+
"(\\S+)"+
"\\s*"

Regards
Abhishek KUMAR

View solution in original post

Highlighted
Five Stars

Re: How to treat consecutive delimiters as one ( like we have in Excel) in TFiledelimitedInput

Thanks, Abhishek for the quick response.
Although the records are being processed through this time without any issue. I'm seeing empty columns as output. Am I missing something here?
Below is the screenshot for your reference.

tFileInputRegex:
Regex.jpg

Output:
Regex Output.jpg

Highlighted
Eleven Stars

Re: How to treat consecutive delimiters as one ( like we have in Excel) in TFiledelimitedInput

You need to define schema
Regards
Abhishek KUMAR
Highlighted
Five Stars

Re: How to treat consecutive delimiters as one ( like we have in Excel) in TFiledelimitedInput

Got it. tLogRow was not defined with the Schema. 
Thank you so much for your help. Appreciate it.
It would be great if you could explain to me the sequence used in Regex as it would help me to customize in the future.
Also, how to handle if there will be a special character like ($,%,@,- etc.,) in the input in such scenarios?

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog