i want to extract data from the text file with is larger in size i only want the last 3 pages of text to be extracted. i tried to copy text into excel but its not pasting the data correctly. i want the text to be neatly extracted in cell wise so i can use that as an input for copying few values into another excel.any ways to acheive this and this should be reliable as this should work for all report files of same type.
i did not develop the workflow yet i tried pasting manually the text data into excel but the text document is completley in unformatted state so i am not getting correct column wise data
Just try to scrap by microsoft ocr activity and change the scale to 2 or 3 . So that you will get the correct data and please make sure that your font size is more than 12.
what is your intended structure?
i have 6 columns in the text file but when extracting it is giving 2 column names as single column name.i want 6 columns sepratley as it is in the text document how to achieve this
i want to extract data from text document and keep it in the datatable the problem here is with the text document pattern its unformatted its having many spaces so i am not able to extract it in proper column wise its giving 2 columns together in single columns
One approach that’s works for me:
- Use the “Assign” activity to apply the text to a variable (string).
- Use built in Activities “Replace” or “Matches” and basic data manipulation techniques to transform your variable:
- Supported .NET methods like .Replace, .Split, .Substring, .Trim, etc to transform the variable:
- For more complex transformations you may need to use Regular Expressions to find recurring patterns that you wish to transform text on. You can use the “Assign” activity with yourVariableName in the “To” field, and something like…
Regex.Replace(yourVariableName,"regex pattern in quotes","replacement text").ToString
If for instance you wanted to replace all spaces with tabs you could try:
or with a comma:
- Line breaks and hidden characters can be especially tricky. Use the “Matches” and “Log” activities to test out your Regex patterns. Here’s something I wrote before on Whitespace and Lookahead/Lookbehinds for regex:
Using the steps above you should be able to transform the data so that your “spaces” or whatever is throwing off the CSV file or Datatable from making the appropriate number of columns is changed to a character (like comma-delimited or tab-delimited) syntax that allows UiPath to recognize the columns correctly.
You can use the activity “Write Text File” to write your variable string directly into an .XLS document. I’ve had trouble writing to .XLSX or .XLSB using this Activity.
- You can use the activity “Generate Data Table” to convert the text string into a DataTable which has formatting options that allow you to specify how the Columns and Line Breaks are separated in your text document. If you have any errors that the Table isn’t initiated: use the “Assign” activity to create a new Data Table on the fly first:
- If experiencing errors with the Generate Data Table activity, I like @ClaytonM solution from this post:
Problem with Generate Data Table Activity
- Once you have a DataTable you could use the “Excel Application Scope” activity and associated tools to read and write from/to Excel: