Issue while splitting delimited text into column

Help
1

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|john.smith@123.com
Sam|“NA”||“102|103| der street”|“airforce area”|Bangalore|600 023|98756 47231|sam@123.com
Mark|“NA”|fernadas|“101|Block A|05 Commando street”|“Military area”|Delhi|110 001|98765 43000|mark.fer@123.com

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

2

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

3

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.

Thanks,
Suresh J

4

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

5

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

Thanks,
Suresh J

6

try this Main.xaml (8.0 KB)

if not working then kindly upload your text file…

7

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 data.zip (352 Bytes)

8

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

1 Like
9

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.

Thanks,
Suresh J

10

@sureshj
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
11

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.

Thanks,
Suresh J

12

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
13

Hi @AkshaySandhu,

Now I understand the Logic.
Thanks, alot akshay.

Regards,
Suresh J:grinning:

14

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