Create DataTable from specific text file format

I need to create an automation that:

  1. Reads a text file in a specific format (See 1st image for source text file). This text file contains a list of Drive Paths and access information properties (users, admins, security groups)

  2. Writes a 2 column table that contains the Drive Name and a row for each property(See 2nd image for resulting table that I am trying to build)

In general, what is the best way to go about this specific sequence?
Is this specific text format common or does it have a conventional name (Similar to JSON or XML)?
Is this automation going to require a complicated series of activities such as nested ForEach, Regex patterns etc?

Any advice on where to start would be much appreciated. I have some ideas in mind, but I dont want to spend hours going down a rabbit hole of inefficiency in case someone else on here has encountered a similar automation task.

Hi @AustinH …To extract the data from the above text i would suggest to go with Regex(there may be other options too).

But you have only value in Column1 , for that value there n # of rows in columns… i.e 1 to many. After writing first value in Column 1 you have to leave row 2 and 3 blank and Fill corresponding Column 2 values…this goes on…

I am thinking…you have to write each iteration result to separate sheet and After filling the blank values with above values matching column2 rows, then you have merge all the sheets.

1 Like

Hello Austin,
In this video I do a lot of stuff with DataTables including filtering the Data before Generate the DataTable:

33:00​ Filter data for multiple fake spaces before Generate DataTable
38:20​ Filter data with multiple spaces inside of column
cristian Negulescu