Convert a TXT to Excel File

[CHINH_TRANTAN]

Hello UiPath Community,

I’m working on a project that requires converting multiple TXT files into Excel spreadsheets, and I’m facing some challenges with the data transformation.

Here’s the scenario:

I have a directory containing numerous TXT files. Each TXT file represents a report with data arranged vertically, as shown in this example:

My goal is to convert this data into an Excel file with a specific horizontal format, like this:

Key Requirements:

1. Vertical to Horizontal Conversion: I need to transpose the vertical data from the TXT files into horizontal columns in the Excel file.
2. Data Extraction with Parentheses: Some rows in the TXT file contain two values, one with parentheses. For example: “Temperature 1190 °C (1189 °C)”. I need to extract both values into separate columns in Excel.
3. Multiple TXT Files: The solution should be able to process multiple TXT files within a specified folder.

Specific Questions:

• How can I effectively read the vertical data from the TXT files and transpose it into horizontal columns in Excel using UiPath?
• What’s the best approach to extract the values within and outside the parentheses into separate columns?
• How can I loop through all TXT files in a folder to automate this process for all files?

I’ve attached sample TXT and Excel files for reference to help illustrate the desired output.

Any guidance or examples would be greatly appreciated. Thank you in advance for your help!

18263_250205_1444_ReportData.txt (2.8 KB)
202503_DUC_EXCEL_TEMPLATE.xlsx (10.1 KB)

[Anil_G (Anil Gorthi)]

@CHINH_TRANTAN

1. Use regex to extract each part of the information…you might need to create separate pattern for each …one example is given below
2. For looping use for each file in folder and it would loop on all files and currentFile will give the actual file for current iteration

(?<=Temperature\s*)\d+

use below site to evaluate the expressions regex101.com

cheers

[V_Roboto_V]

Hi @CHINH_TRANTAN

Here is a solution:

The Flow:
Enclose the Activities (in Highlighted Area) inside a For Each File in Folder

The Assign Statement:
You Must change the Regex pattern string in case any of them don’t work.

row Array = New Object() {System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Program   : ).{4}").Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Temperature)\s+(.+)\n\s+(.+)\n(?=Washing)").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Temperature)\s+(.+)\n\s+(.+)\n(?=Washing)").Groups(2).Value.Replace("(", "").Replace(")", "").Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Washing while heating)\s+(.*)(?=\n)").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Insert Flask\/Ingot)\s+(.*)(?=\n)").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Flask temperature)\s+(.*)(?=\n)").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Melting pressure)\s+(.+)\n\s+(.+)(?=\nMelting time)").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Temperature)\s+(.+)\n\s+(.+)\n(?=Washing)").Groups(2).Value.Replace("(", "").Replace(")", "").Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Melting time)\s+(.+)\n\s+(.+)(?=\nCasting)").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Melting time)\s+(.+)\n\s+(.+)(?=\nCasting)").Groups(2).Value.Replace("(", "").Replace(")", "").Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Casting mould)\s+(.+)\n").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Casting mode)\s+(.+)\n").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Pre cast vacuum)\s+(.+)\n").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Casting pressure start)\s+(.+)\n").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Casting pressure)\s+(.+)\n\s+(.+)\n(?=Flask pressure)").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Casting pressure)\s+(.+)\n\s+(.+)\n(?=Flask pressure)").Groups(2).Value.Replace("(", "").Replace(")", "").Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Flask pressure)\s+(.+)\n\s+(.+)\n(?=Vibration time)").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Flask pressure)\s+(.+)\n\s+(.+)\n(?=Vibration time)").Groups(2).Value.Replace("(", "").Replace(")", "").Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Vibration time)\s+(.+)\n").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Vibration sweep)\s+(.+)\n").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Cooling time)\s+(.+)\n\s+(.+)\n(?=Cooling time p)").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Cooling time)\s+(.+)\n\s+(.+)\n(?=Cooling time p)").Groups(2).Value.Replace("(", "").Replace(")", "").Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Cooling time p.-less)\s+(.+)\n\s+(.+)\n(?=Vacuum)").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Cooling time p.-less)\s+(.+)\n\s+(.+)\n(?=Vacuum)").Groups(2).Value.Replace("(", "").Replace(")", "").Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Vacuum chamber open)\s+(.+)\n").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Current values within brackets.)\s*\n-*\s*\n(.{6})\s+(.{8})\s+(.{5})\s+(.{6})").Groups(1).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Current values within brackets.)\s*\n-*\s*\n(.{6})\s+(.{8})\s+(.{5})\s+(.{6})").Groups(2).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Current values within brackets.)\s*\n-*\s*\n(.{6})\s+(.{8})\s+(.{5})\s+(.{6})").Groups(3).Value.Trim, _
System.Text.RegularExpressions.Regex.Match(inputString, "(?<=Current values within brackets.)\s*\n-*\s*\n(.{6})\s+(.{8})\s+(.{5})\s+(.{6})").Groups(4).Value.Trim _
}

The Output:

Note: I have ignored the duplicate column names present in the excel. I have manually fixed them. You can implement a Excel scope - Write Range and build a logic to Insert the Row Array

If this solves your issue, Do mark it as a solution.
Happy Automation