Delimited Text File Normalization

mustufa2019

I am trying to normalize a text file into a cleaner excel output file. There are headers and multiple rows here and there I do not need. I am trying to get all the users under the columns fields "ID", "Initials", and "Name". Please see screenshot below:

I usually do clean up in excel but wanted to create a workflow to make this a quicker process. 

This is the output I am looking for 

ID    Initial   Name  

AU   AUI     Choi

TonyA

There are  different techniques that can be used depending on the data.

The general steps to follow are:

1. Open the file as a txt file. This will open a "Resolve File Type" window.

2. Select Read as Delimited file and select OK.

3. If the data is in columns separated by spaces, you can define the columns in the next window by clicking on the character positions at the top of the window. Don't worry about unneeded line of characters before or after the data, just split the file based on the column boundaries of the data you care about. If the data is in columns separated by tabs, then you don't need to worry about column boundaries at this point. Take a look at this link for more info on how to split raw text data into columns: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Text-txt-file-Non-Delimited-Flat-File-Layout/td-p/52601

4. Click OK and the you'll get a preview of the data.

5. Use a Sample tool to skip the rows you don't care about. After this is done, you should have the column headers as the top row of data.

6. If you had split the columns in Step 3, then skip this step. If you hadn't split the columns, and the column data are separated by tabs, then set a row ID, use a Text to Columns tool to split the data to rows, then use a crosstab to put the data into separate columns.

7. You should now have the data in columns with the column names in the first row. Use the Dynamic Rename Tool to promote the first row to be the column headers.

8. At this point you can use Select, Cleansing, and/or Autofield to clean up the columns, select the ones you want, and set the data types.