Ignore the delimiter in a string


(Jayant Singh) #1

Hello friends,
We are importing a .txt file with comma as separator into a text file reader. The text file reader has commas as the delimiter. But our field data might have commas n the data, so we enclosed it in double quotes so that it doesn’t get treated as two columns.

"14ALLP_Obligation_Num_High_Lev_Adv_Null_Blank","Obligation Number Highest Level Advised","RISKCOML.IN.CLL_YYYYMM.Obl_Num_HLA","if Obligation number is populated and act_sys_id <> 'DSI', then Obligation Number Highest Level Advised shd not be null or  blank",68410,0,68410,100,"95","Completeness","14ALLP-CLL","T13097A",2019-03-14 16:11:48,"FRY14A-69","2019-01-29

As we can see there is a comma after DSI, which is causing the trouble here. Can we get help on how to configure the text file reader so that the entire string;
if Obligation number is populated and act_sys_id <> 'DSI', then Obligation Number Highest Level Advised shd not be null or blank

is treated as one column. We have a huge .txt file with millions of records, therefore we are trying to avoid manual changes here. Can I kindly get some help?

Thanks in advance.


(Victoria Tuktarova) #2

Hello Jayant,

Please, edit File Metadata by changing String qualifier to " symbol.
It solved the issue for me.

Kind regards,
Victoria


(Jayant Singh) #3

Thank you.It solves my problem.


(Victoria Tuktarova) #4

Hello Jayant,

I am glad it helped.

Kind regards,
Victoria


(Jayant Singh) #5

Hello,
Sorry but I have a follow up question. In my case I am using a text file reader to read the file from a remote location. As far as I know we have ‘Edit Metadata’ option, when we import the flat file into my workspace.
But since I am using text file reader to read the file from remote location(outside IDE), I do not see that option.
But I see this option;

May I know how to tweak these options so as to avoid the above issue? Another question is, what is the difference between string qualifier and string qualifier escape? Can you kindly explain?

Your help is appreciated.
Thanks


(Victoria Tuktarova) #6

Hello Jayant,

The functionality of “Edit Metadata” option and “Main” part in Reader step is pretty much the same.
Let me explain some fields that might be confusing:

  • String Qualifier encloses a text string. In other words, to parse correctly a string that includes not only letters you need to enclose a string with a special character. In your case it is quotes.
  • String Qualifier Escape behaves as a standard escape character: escapes the String Qualifier in the original meaning of this character.

You faced the issue above, because the Field separator was set to comma and String Qualifier wasn’t set at all. One of strings includes comma that was parsed as Field Separator as configured. For now, as I can see the configuration is made the way so that a string will be parsed correctly, no matter what characters it includes (except for quotes, surely).

You might find more details in Help section (press F1 when Reader step is opened).
In case you have further questions, do not hesitate to ask. I am always glad to help.

Kind regards,
Victoria


(Jayant Singh) #7

Thank you for the kind explanation. I am getting it now.
So will it be a wise idea to have string qualifier as ", and can we have a text enclosed in single quotes ' in that string. Is that going to work?

For example;
“If ‘acct name’ exists then acct number exists”
Is this acceptable?

Other question about escape character; if we want to have literal double quotes in the string such as:
“If “acct name” exists then acct number exists”

then shall we add string qualifier escape as
\" or "

Kindly let me know.


(Victoria Tuktarova) #8

Hello Jayant,

Sorry for the late response. I would recommend to test all new types of input data to be sure you won’t face any issues.
I have tested the example you provide “If ‘acct name’ exists then acct number exists” and it was parsed as one string.

“If “acct name” exists then acct number exists” will still be parsed as one string in your case even if you will not specify escape character at all. Because the field separator is comma. Hence, any symbols inside the qualified string will be parsed as one string. Please, note that it might not be the case with different metadata configuration.

Kind regards,
Victoria