Notepad Automation

sivagelli

Well-Known Member
There are 3 ways

#1

  • Get File content in to a Data Item using a Calc stage with LoadTextFile() expression passing the file path
  • Use 'Split Lines' action from Strings VBO to get the data in to collection
#2
  • Get File content in to a Data Item using a "Read All Text From File" action in File Management VBO passing the file path
  • Use 'Split Lines' action from Strings VBO to get the data in to collection
#3
  • From the file, if you are looking to get subset of lines, say 4th line 10th line, use "Get lines from file" action from File Management VBO passing the file path, start line and end line.
  • The output is a Collection

Choose a method that suits your requirement.

Post back if it helps!
 

janejeba

Member
I have extracted the data from notepad to collection using the above approach. Now the challenge here is I need to paste the extracted data into an excel file as a
*.

Can you suggest some best approach to deal with this.
 

janejeba

Member
I have attached the screen shot of my collection [Data extracted from Notepad]

Now I need to move the collection data into a excel file as table.

Since, the entire data had been populated under the single column [Write collection -vbo] moves the data into excel as a single cell value. (I need it as table refer screenshot2)
 

Attachments

  • Sivagelli_Collection.PNG
    48.9 KB · Views: 35
  • ScreenShot2.PNG
    66.2 KB · Views: 28

Sukesh Kumaru

Active Member
Hello,

Start using calc stage write an expression to split the text at comma (,)

make use of utility-string manipulations vbo.

It is easy to find the position of comma in that text and split it accordingly,
like first comma is on date and text after first comma & second comma is off date and so on.

Good Luck.
 

sivagelli

Well-Known Member
I have attached the screen shot of my collection [Data extracted from Notepad]

Now I need to move the collection data into a excel file as table.

Since, the entire data had been populated under the single column [Write collection -vbo] moves the data into excel as a single cell value. (I need it as table refer screenshot2)

There could be two ways. Check this post
 

sivagelli

Well-Known Member
I have designed code to implement the second solution that i proposed.

Here it is-

I am going with the <sivagelli_collection-png> that you have attached above. The collection has one column with comma delimited text and you want to break the column in to multi column.

In the object studio, create a new object and add a code stage. The code uses C# and Linq.
So, first things to do is to add the below namespaces and reference dlls to the Initialise page and do not forget to change the language to C#.

External References:
System.Core.dll
System.Data.DataSetExtensions.dll

Namespaces:
System.Linq
System.Collections.Generic

Now, on the Action1 page-
Have Start stage accept the below arguments:
  • CollectionIn of Collection Type and store in Coll_In
    • "CollectionIn" would be the initial collection, you have with single column. Here, you have to pass the "ExtractedCollection"
  • delimiter of Text Type and store in delimiter Data Item
    • This is to hold the delimiter to split the text. Pass ","
  • columnName of type text and store in columnName Data item
    • The name of the column in the collection. Pass "Value"
  • Headers of Collection Type and store in Headers
    • Create a Single Column collection and add field names you want. Pass a collection with On Date, Off Date, so on listed in rows of a column
For the Code stage, have all the above as input arguments. For Outputs, add 'Collection_Out' of type collection and Store In Coll_Out.
Add the below code to the code stage-

C#:
var dtCompleteRecords1 = new DataTable();
foreach(DataRow row in Headers.Rows){
     dtCompleteRecords1.Columns.Add(new DataColumn(row.Field<string>(0),typeof(string)));
 }
dtRecords.AsEnumerable()
    .Select(row => row[columnName].ToString().Split(delimiter.ToCharArray()))
    .ToList()
    .ForEach(array => dtCompleteRecords1.Rows.Add(array));

Collection_Out = dtCompleteRecords1.Copy();

On execution, Coll_Out will have the data split in to multiple columns with column names specified in "Headers" collection.

Post back how it goes!
 

janejeba

Member
Im getting the error message "Metadata file system.data.datatable.dll could not be found". I have attached the screenshot for your reference.
 

Attachments

  • DLL Added.PNG
    12.6 KB · Views: 12
  • Dll error.PNG
    13 KB · Views: 12

sivagelli

Well-Known Member
I did not run in to this trouble, I had these References and Namespaces added-

References:
System.dll
System.Data.dll
System.Drawing.dll
System.xml.dll
System.Data.DataSetExtensions.dll
System.Core.dll

Namespace:
System
System.Data
System.Drawing
System.Collections.Generic
System.Linq

My machine has dotNET v4.7.
BP v5.0
 

janejeba

Member
After adding the above references, Im getting the below error

code:

var dtCompleteRecords1 = new DataTable();
foreach(DataRow row in Headers.Rows){
dtCompleteRecords1.Columns.Add(new DataColumn(row.Field<string>(0),typeof(string)));
}
dtRecords.AsEnumerable()
.Select(row => row[columnName].ToString().Split(delimiter.ToCharArray()))
.ToList()
.ForEach(array => dtCompleteRecords1.Rows.Add(array));
Collection_Out = dtCompleteRecords1.Copy();
 

Attachments

  • error msg.PNG
    10.9 KB · Views: 10

janejeba

Member
Please find the attached SC...
 

Attachments

  • input.PNG
    8.7 KB · Views: 14
  • output.PNG
    6.1 KB · Views: 10
  • Code.PNG
    18.7 KB · Views: 13

janejeba

Member
Data item & Collections used..
 

Attachments

  • Delimiter DataItem.PNG
    17.2 KB · Views: 9
  • Header_DataItem.PNG
    17.5 KB · Views: 9
  • Header Collection.PNG
    21.6 KB · Views: 6
  • Extracted Collection.PNG
    55.7 KB · Views: 4

sivagelli

Well-Known Member
Do you have anything for [columnName] data item? You have "Header Name" data item but the code stage input is mapped with [columnName], pass the [Header Name] instead of [columnName] on inputs page. I am not sure if this is causing the trouble, but based on the screenshots shared, i thought this is the missing link.
 

janejeba

Member
I have tried with [ColumnName] still getting the same error.

code:

var dtCompleteRecords1 = new DataTable();
foreach(DataRow row in Headers.Rows){
dtCompleteRecords1.Columns.Add(new DataColumn(row.Field<string>(0),typeof(string)));
}
dtRecords.AsEnumerable()
.Select(row => row[columnName].ToString().Split(delimiter.ToCharArray()))
.ToList()
.ForEach(array => dtCompleteRecords1.Rows.Add(array));
Collection_Out = dtCompleteRecords1.Copy();

In the 5 th line, We have object called "drRecords" itss not initialised anywhere in the code. Does it create error ??
 

Nici BP

New Member
This solution does not work for me. I try to convert a txt.-file which is separated by commas into a Collection and then in to an Excel file, but for some reason when I use split lines i get way more rows than in the initial work sheet and they all contain strange Symbols like question marks.


Do you have any idea how to solve this Problem?
 
Top