Task description

In the following Excel file book1.xlsx, rows highlighted in red are records of couples, where FIRST NAME is separated by & and the husband goes ahead of the wife. For the rest of the rows, those where DOB and HEALTH are not empty and DOB1 and HEALTH1 are empty are for males; and others are for females who have the opposite values in DOB/HEALTH and DOB/HEALTH.

We want to rearrange the data according to the following format. Split each record of couples into two records, add a new SEX column to store genders, and delete DOB1 column and HEALTH1 column to store their values under DOB and HEALTH.

Directions:

1.  Start esProc

Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time.

2.  Write script in esProc:

List the code separately for an easy viewing:

A1  Import data of book1.xlsx; @t option enables reading the first row as column headers.

A2   Add a new column SEX; the initial value is M.

A3   Loop through each row of A2. During each round of loop, use the ampersand & to split the FIRST NAME (@t option enables removing spaces at both ends of the result values) and extend the row into multiple ones, which is the number of result values after splitting. Each newly-generated row has all columns and copies their values. Each split value represented by the tilde ~ will be the new FIRST NAME value. For the second split value, assign F to SEX column, DOB1 value to DOB column, and HEALTH1 value to HEALTH column.

A4   Loop through each row of A3. If both DOB and HEALTH are null, it is a record of female, and assign F to SEX and DOB1 to DOB and HEALTH1 to HEALTH.

A5   Get columns of A4from the first to the seventh.

A6   Store A5’s result to book2.xlsx.

3.  Press F9 to execute the script. After that you can open book2.xlsx to view the target table.

【Reference】 txl.zip