Hi
I have some unstructured data in a txt/csv file (received from a paper scanning).
I want that data in to a an Excel file to create a word document to save that letter to archive system.
what is the best way to do that and how to do it.
a demo is attached.
PaperScanData.txt (2.7 KB)
Hi @Latif
I had a crack at it.
Not sure why you need the conversion from Excel to Word but I included it as well.
I have renamed all activities to show where you need to pass variables/arguments.
I used GenAI Content Generation activity to assist in reading the text file you attached and then converting it into a String that can be passed into a “Generate Datatable” activity which made it easier. I have also attached the prompts etc. You could use Regex for this instead, but I wasn’t sure if all your sample data being ingested in the future was going to be structured the same, so AI will prevent any issues from occurring. You will need to install those GenAI packages and create an Integration Service for it.
Hope this helps 
Main.xaml (28.1 KB)
WordDocument.docx (13.5 KB)
Book1.xlsx (9.8 KB)
AI Prompt.txt (1.4 KB)
Thank you for the help But I can not use AI into that.
I tried with the Regex on Text file and in that I have issue with that I am unable to read the next line from the file.
Use the “Find Matching Patterns” activity like this:
And in the properties menu, make sure “Multiline” is checked:
And then use these regular expressions to get the relevant information:
That should help you.
ReadTextFile - Copy (1).xaml (45.4 KB)
its repeating the same line weather I have different expression for each line
kindly have a look
Hi @Latif
opened your xaml and there’s actually two separate issues stacked together
first, the real cause of “repeating the same line”. Notice your For Each loop that splits the text into individual lines is wrapped inside a Comment Out activity, so it never runs. That means every single “Find Matching Patterns” activity (First Line, Second Line, Third Line, Forth Line) is running its regex against Input=“[varReadTextFile]”, wich is the WHOLE file content, not one line at a time. And since none of them have the Multiline option checked in RegexOptions (yours just says IgnoreCase, Compiled), the ^ anchor in your patterns only matches the very start of the entire text, not the start of each line. So matches(0) in all four sequences ends up pointing near that same spot at the beginning of the file every time, thats why you keep getting the same result back regardless of wich pattern you use
second issue, even once that’s fixed, nothing in the whole workflow ever writes rows into varExcelResultDt, theres no Add Data Row or Build Data Table anywhere, you only assign varResult (a single string) over and over inside each sequence, and that variable gets overwritten each time so only the last one survives anyway. The final Write Range Workbook is writing an empty DataTable no matter what
to fix it id restructure like this, uncomment that For Each loop so it actually splits by line, move your 4 pattern checks inside the loop body so they run per currentText, and add a Build Data Table or Add Data Row step that appends a new row with the matched result each time through the loop, using AddHeaders and proper columns instead of only overwriting varResult
happy to sketch the loop structure if that helps, but the two things above are definitely why nothing works right now.
hope this clears it up !!
You totally misunderstood the problem.
The reason im using varResult is only to check that i get the right data out or not. I have to put all data in to Database but im just builinding and testing right now. so im running project as debug to check the result to build it.
I have that Text file with data and I want to create a word document of that and document should look exact same as Text document at the end but we need to put data into database as for record.
Im not using any loop as that i will put after i have get the whole result out.
Second..
Im using regex as data into excel is not giving me good result so I thought to use Regex to get data and add into Excel after.
I did tried with multiline option as well and it still not going further.
And my regex is not taking each line that is where im having issue right now.
Hi @Latif fair point, sorry for jumping at the loop, now that you explained the varResult debug approach it makes sense
so the actual problem is simpler than it looks, its the patterns, not Multiline.
^(.) and (^.?)\s+(.*) are generic, they match any line, and matches(0) always returns the first match found in the whole text, wich is always line 1 of your file, Multiline on or off doesnt change wich match comes first. And your 3rd and 4th patterns require a colon in the line, without Multiline the ^ can only match the very start of the file, so those two blocks match nothing at all and varResult just keeps the value from the previous block. Both effects together look exactly like “same line repeating whatever expression i use”
the fix is putting the unique label of each field inside the pattern, then matches(0) lands on the right line by itself. I tested these against your PaperScanData.txt, all of them read with varMatches(0).Groups(1).ToString.Trim like you’re already doing
doc ref: (?m)^(AP.+?)\s{2,}\d{4}-\d{2}-\d{2} (Jarryds one, works fine)
recipient: (?m)^Recipient.\r?\n[ \t]+(.+)$ (the Trim matters here, the name line has trailing spaces)
pension incl UFP: Pension incl. UFP supplement\s+([\d.,]+)
pension year: Pension Year\s:\s*([^\r\n]+)
pay term: Pay term\s*:\s*(\d+)
date: Date\s*:\s*([\d.]+)
marital pension: Evt. opsat marital pension\s+([\d.,]+)
child pension: Evt. 10% opsat child pension\s+([\d.,]+)
easiest way to test without debug runs, paste your txt into regex101.com, set the flavor to .NET on the left, and try each pattern there, you see instantly what the group captures before touching studio
one heads up for later, your file has 2 letters separated by that long dashed line, and regex over the whole text always returns the first letters values. When you get to the multi record part, split the text on the dashed line first and run these patterns per chunk, one chunk = one letter = one db row. For testing on letter 1 right now you’re fine as is
and for the word document, since it must look identical to the txt you dont even need the extracted fields for that part, just write the raw file text into the doc using a monospaced font like Courier New and the column layout survives exactly. The regex fields then only feed your database
try them and post back if any field comes out wrong, i have your sample here so its quick to adjust. and if im still off on what you’re building, just correct me or share a bit more detail on the end goal, rather get corrected twice than answer the wrong problem again
hope this unblocks you
Appericate your effort.
Can you explain the document par.
Act the regex you have given is just the data but I need everything on the file means It has to put exact same text from txt file to the document to be archived.
So I can not just move the values to doc and create a sample document. Because text can we different as well but the format of text will remain same.
And then i have to find the next dcoument by seperating the line which is in TXT file.
Hope you got what im trying to explain.
Hi ,
If the TXT/CSV data is unstructured, I would first read the file and identify the required fields before writing them to Excel.
A simple approach in UiPath would be:
Use Read Text File to read the TXT file.
If the data has a fixed pattern, use Regex to extract the required information.
Store the extracted values in variables or a DataTable.
Use Use Excel File + Write DataTable to Excel to write the data into Excel.
Then you can use the Excel data to create the Word document.
If the scanned data is more complex or the layout changes from document to document, Document Understanding may be a better option than relying only on Regex.
The best approach depends on how the data is structured in the attached TXT file.
Hi @Latif
Perhaps you could give an example of how you would expect the final Excel/Word/PDF file to look, to help guide us?
And what would be your indicators for when to move to a new cell?
You could in theory just make one large cell, center the contents and copy all text to that cell.. but I’m not sure thats what you would want?
Regards
Soren
I’m expecting data in that format after structured.
PaperScanData.txt (3.8 KB)
I got the structed data by doing that atleast it has removed all the empty lines.
String.Join(System.Environment.Newline, yourstring.Split(System.Environment.Newline.ToCharArray,System.StringSplitOptions.RemoveEmptyEntries)
As you can see document has different type of data and sometime that data can be changed within file.
an example of how i want data in excel
PaperScan.xlsx (9.5 KB)
The issue is, document pattern can be changed.
I can not figur out which way it would be best to do and I can not use document understanding and company policy is not allow us for that project.
This seems challenging for standard regex given that your structure is going to constantly change. Hence why I originally used AI to do it.
The only thing you can really do is use regex to get each value that you want for your provided Excel file and then build out a Datatable in that same format. It will work for this text file that you provided but I can’t guarantee it will work for the next one you ingest.
If company policy is not allowing you to use DU or AI, I would suggest having a conversation with them and explaining the use case.
OK
Is there a way that I can split the data means split the each reference after (-----------------) lines and save the data in text format again?
Then I can save each person data into a differnent file.
I tried split Text activity but it’s now allow me give that (-----------------)
Yes, you can use the split function with a regular expression inside:
System.Text.RegularExpressions.Regex.Split(TextFile_Input, "-{5,}")
This way what you’re doing is look for an occurrence of hyphens where there are a minimum of 5 or more (no matter how many), and then it will split the data into a string array. If you need each string, just loop through the array to get each one.
Just pop that into an assign:
Thank you.. It helped alot with seperating the files and I used Regex to find the reference in of each file and saved as file name.
Jerry I user this Regex to find the each seprate file first line..
doc ref: (?m)^(AP.+?)\s{2,}\d{4}-\d{2}-\d{2} (Given by @Michael.Vornes )
But when start of text is not starting from AP then its not working.
Is there a way that you can tell how to find the doc ref: where text start from any letter?
