Reading larger text file and extracting certail part of data from it

activities
studio

#1

hi,
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.


#2

please share your workflow @sravyarao20


#3

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


#4

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.


#5

what is your intended structure?


#6

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


#7

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


#8

check inbox


#9

Hi @sravyarao20,

One approach that’s works for me:

  1. Use the β€œAssign” activity to apply the text to a variable (string).
  2. Use built in Activities β€œReplace” or β€œMatches” and basic data manipulation techniques to transform your variable:

OR

  1. Supported .NET methods like .Replace, .Split, .Substring, .Trim, etc to transform the variable:

OR

  1. 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:
    Regex.Replace(yourVariableName," *",vbTab).ToString
    or with a comma:
    Regex.Replace(yourVariableName," *",",").ToString
  1. 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:
  1. 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.

  2. 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.

OR

  1. 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:
  1. Once you have a DataTable you could use the β€œExcel Application Scope” activity and associated tools to read and write from/to Excel: