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.
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 firstname.lastname@example.org
Sam|“NA”||“102|103| der street”|“airforce area”|Bangalore|600 023|98756 email@example.com
Mark|“NA”|fernadas|“101|Block A|05 Commando street”|“Military area”|Delhi|110 001|98765 firstname.lastname@example.org
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.
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
try this Main.xaml (8.0 KB)
if not working then kindly upload your text file…
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)
hello @sureshjMain (5).xaml (7.2 KB)
it will replace all vertical bar/pipe with comma…
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.
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.
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.
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).
Now I understand the Logic.
Thanks, alot akshay.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.