Extracting unstructured Text file from Notepad onto Datatable

datatable
excel
#1

Salutations everyone, what I have been trying to do is screen scraping my notepad screen.

The problem is that when I clicked on generate table, this is the result of the table generated,

2
2.JPG882x648 51.5 KB
.

As you can see, the results are not what I expected and when I tick “Use first row as column headers”, because Date Time and Testing Time contains space between the words, “Time” becomes a new column and this duplication disallows me to click on that option.

In my ideal table, “Date Time” and “Testing Time” should be on their own column.

What are the methods that I can do so I can get the results that I want? Thank you so much for the help! My apologies for only 1 image, I cannot post more than 1!

#2

Hi @peearpee
You can simply read text file using Read text file activity and then use Generate Datatable activity to convert your text to datatable.
You can use various properties like Column size or Column and row separators.
If your Columns are of fixed size like 10,20,15,15 characters, you can input this as ColumnSize else use separators. In your case, you can try more than one space as column separator.
Thanks

#3

Hi @Bharat , thank you for your input. The reason why I am using screen scraping and not read text file is because this is a prototype of what my actual programme would be. The original code was not designed for notepad, but for other programme that would require me to screen scrape the texts. And as for the column sizes, this varies and is very unpredictable, so fixed column sizes are out of the option. Using more than one space as column separators did not seem to work, am I doing something wrong here?

#4

Hi

If activities doesn’t help much, then you can write your own code to generate a table from the scraped text. But, some pattern must be there in your scraped text else you cannot convert it to table unless there’s no pattern in the text.

You can take help from this workflow attached. What i have done is:

  1. Split the text with newline and split first line of the result with more than one space and add columns in the table.
  2. Split the text on the basis of newline and then space to add datarows in the table.

GenerateDT.zip (2.5 KB)
Thanks

#5

hi @Bharat,

the attached zip I am unable to open,can you please share it again ,I need similar code.