Import print report text file into tabular

Hello, 

I would like to know if someone has a tip for importing the text below into tabular format in knime.

User name                    asy_l
Full Name                    
Comment                      
User's comment               
Country code                 000 (System Default)
Account active               Yes
Account expires              Never

Password last set            1/15/2016 2:50 PM
Password expires             Never
Password changeable          1/17/2016 2:50 PM
Password required            Yes
User may change password     Yes

Workstations allowed         All
Logon script                 
User profile                 
Home directory               
Last logon                   Never

Logon hours allowed          All

Local Group Memberships      *ACSUSRG              *Administrators       
                             *AESADMG              *AESUSRG              
                             *APLOCG               *APZADMG              
                             *CDHFTPUSRG           *CPADMG               
                             *CPSADMG              *CPUSRG               
                             *EXPERTG              *FMSADMG              
                             *FMSUSRG              *FTPUSRG              
                             *MASADMG              *MASUSRG              
                             *MCSADMG              *MCSUSRG              
                             *STSUSRG              *Users                
Global Group memberships     *Domain Users         

 

Hi,

How should the table look like once the data have been imported? What are you going to do with those data once they have been turned into a tabular format?

Knowing that would help to determine the best way to read in the data and parse them. 

Cheers,
Marco.

Hello Marco,

The table should have for each user (user name line), the parameters of his account (last logon, last password set, etc).

But also and most importantly, the Local Group Memberships section includes the groups memberships for the user and it varies per each user.

I attach a sample file.

Thanks & Regards

Georges

Hello Georges,

it is perfectly doable, but it requires a bit of work. I give you few pointers to get started.

1) Read in the whole file using a File Reader node. You will get a table with one row for each line in the sample.txt file.

2) Next you need to bring together all the information belonging to a single record on one row. To do this, you first need to determine where each record starts and ends. You can use a Java Snippet (simple) node with a global int variable (let's call it record) and increment it by one every time you encounter a row starting with "User name". Something like this:

if ($Col0$.contains("User name")){
	record++;
}
return record;

Once all rows are labeled with the record number they belong to, you can use a GroupBy node to group them on each record. Use aggregation method Concatenate and chose a suitable delimiter (say ";").

3) Now you have each record in one single row of your table. It is time to use the RegEx Split node to split each row into columns according to each field. The RegEx string will look something like this (for the first fields, you need to complete it yourself for the remaining ones as indicated by the ...):

User name\s*(.*?);Full Name\s*(.*?);Comment\s*(.*?);User's comment\s*(.*?); ...

The Local Group and Global Group Membership will require a special treatment since they are multi value fields. Simply ignore the delimiter (";") and capture until you reach the next field.

4) Now you can rename the columns as needed. You may also want to cleanup the Local Group and Global Group Membership by removing the unneeded delimiter and even turn them into collections (depending on what you are going to do next with these data).

Hope this helps. You can post your workflow here if you need more help on specific parts of it.

Cheers,
Marco.