How to Extract Data From Multiple Text Files & Save Output to Excel with RegEx

Hello RPA Experts,

I am new to UiPath Studio (Enterprise Edition). Can you please help me to Read desired data from multiple text files and save output to Excel.
My Problem is different coz I having daily wise text files which having hourly Price Data with Date given once on top of each file.

Sample Text file is like this :-

Date: 30-04-2021

10:00 Price 42100
MANDI 31700
ALANG32000
11:00 Price 41900
MANDI31500
ALANG31800

where I Extracted Desired Lines of data like “10:00 Price 42100” with help of REGEX.

I want to Extract All Date, Time and Price from each file and Write it to Excel file Like this

I am facing problem, because each file having date only once on top.

Please help me guys. Thank u a lot

Just extract the date once and then use it when you add the rows to data table.

Pseudocode:

strDate = Regex.Match(inputText, "(?<=Date: )\d{2}\-\d{2}-\d{4}").ToString

For Each matchedLine
    Add Data Row: ArrayRow = {strDate, matchedLine_Time, matchedLine_Price}
1 Like

Hello @Ali_Baba ,

Can you try to read the Text file using Read File activity and you will be getting a string variable as output.

The you can try to use Text to column activity. There you can select the delimiter, then it will split to columns.

Thanks a lot,

Hope it work.

Sir, as u told, i build bot. But facing problem.

Please look excel output

Kindly help me to solve the problem.

Or can u please clarify each step.

Thanking you

Could you show us your workflow?

@Ali_Baba , I do think we need to add .Value before .ToString in the Expression.

Modified Expression :

Regex.Match(inputText, "(?<=Date: )\d{2}\-\d{2}-\d{4}").Value.ToString

workflow-2

Please check

Replace the Date Matches with an Assign activity:

RegDate = Regex.Match(inputText, "(?<=Date: )\d{2}\-\d{2}-\d{4}").ToString

where inputText is from the the text file.

Add a For Each loop to go through the Time and Rate matches. In the loop, add the rows.

The Write Range should be outside the loop.

1 Like

Thanks Sir,

Let me try

Sorry sir, Its not working perfectly.

Output Excel file :

I having daily date based monthly text files (i.e. 30-31 files a day)

Each file having Date once (upper)

then Hourly based Item Prices alongwith Time i.e.
10:00 Item 4500
11:00 Item 4500
12:00 Item 4500
01:00 Item 4500
02:00 Item 4500
03:00 Item 4500
04:00 Item 4500
05:00 Item 4500
06:00 Item 4500
07:00 Item 4500

Please give me a solution sir.

My Workflow is here :

It doesn’t look like you are looping through the matches. If you upload your workflow, we could help you to modify it.

1 Like

Please tell me sir, how to export it?

can u please share me your email id, so i mail u full project alognwith Data directory which containt Slource Text Files.

You can send it to me with Private Message.

1 Like

Hows it sir? Kindly tell me Sir… I shall very thankfull to you.

Coz im a new to uiPath Studio and I need to complete this task.

Just click my profile pic and then Message.

1 Like

Sent mail.xaml file to u sir

1 Like