Issue while splitting delimited text into column

I have a requirement to read a text file and split into a specific column for processing further.
we are facing issue while trying to split a column that has been qualified using double quote.
for example:

sample data TXT.FILE

first Name | middle name | last name | address 1 | address 2 | city | Pincode | phone number|emailid
John|“NA”|Smith|“102|A Naval street”|“Sea side area”|Chennai|600 001|98765 43231|
Sam|“NA”||“102|103| der street”|“airforce area”|Bangalore|600 023|98756 47231|
Mark|“NA”|fernadas|“101|Block A|05 Commando street”|“Military area”|Delhi|110 001|98765 43000|

Here | is the delimiter and text within a quote("") should be considered as a single column, the challenge we facing here the text within the quote has | sign as part of the column. Here split the text into 9 column

Can you try by importing the file into Excel and check if the data is importing as expected. (Excel -> Data -> From Text).

I try in excel is working fine, because it has “text qualifier” we can mention " in the text qualifier so it’s working, but I want to automate it without using excel.

Suresh J

Currently how you are reading the file,splitting the data and loading into UIpath?

I am reading the file using
Assign activity (Arrayvar= “Readallline(Path.txt)”)
using for each
Splitting the string using |, but data in address has | so its allow splitting , need to restrict using “” for String

Suresh J

try this Main.xaml (8.0 KB)

if not working then kindly upload your text file…

Hi Akshay,

Thank for the reply,
For my case it’s not working, Please find the Sample Text file
here | is the delimiter need to replace or split without touching the | within the Quote “”

sample (352 Bytes)

hello @sureshjMain (5).xaml (7.2 KB)
it will replace all vertical bar/pipe with comma…

1 Like

Hi Akshay,

Thanks for the reply
I dont want to replace all pipe/Vertical Bar ,My requirement is to replace the pipe/ vertical bar inside the Double Quotes or replace except the pipe inside the double quotes.

Suresh J

if you have tested the given xaml, you will see it is replacing all vertical pipe with comma in double quotes only…
e.g. “block1 | street1” will get converted to “block1 , street1”

just convert end result (txt variable) into datatable with vertical pipe as delimiter you will get the your desired output.

1 Like

Hi @AkshaySandhu

Thanks alot , It solved my requirement.
But I don’t understand the logic (txt, chr(34) &"(.*?)" & chr(34))),
Could help me to understand the logic.

Suresh J

sure… @sureshj
in given xaml we are using regex (Regular Expression) to search word/s which exists between double quotes. In our case regex pattern is “(.*?)” (example).

1 Like

Hi @AkshaySandhu,

Now I understand the Logic.
Thanks, alot akshay.

Suresh J:grinning:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.