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

Five Stars

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

 

Accepted Solutions
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

All Replies
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.
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

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
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

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
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

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
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

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
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 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch 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