...

How to Choose Other Columns According to a certain Column Calculation

 

Task description

In the following Excel file book1.xlsx, the rows in red font are the records of a couple, in which the FIRST NAME is separated by “&” with male in front and female behind. In other rows, DOB and HEALTH with values are the records of male, and the DOB1 and HEALTH1 of male are empty, while those of female are opposite to male.

..

The data need to be converted into the following format: split the couple into two records, add a SEX column to store the gender, delete the DOB1 and HEALTH1 columns, and store their values in DOB and HEALTH respectively.

..

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:

..

The code is described separately for a better viewing:

A

1

=file("E:/txl/book1.xlsx").xlsimport@t()

2

=A1.derive("M":SEX)

3

=A2.news(#2.split@t("&");'LAST NAME',~:'FIRST NAME', CITY,PHONE,if(#==2,"F",SEX):SEX,if(#==2,DOB1,DOB):DOB,if(#==2,HEALTH1,HEALTH):HEALTH,DOB1,HEALTH1)

4

=A3.run(if(DOB==null &&   HEALTH==null,(SEX="F",DOB=DOB1,HEALTH=HEALTH1)))

5

=A4.new(#1,#2,#3,#4,#5,#6,#7)

6

=file("E:/txl/book2.xlsx").xlsexport@t(A5)

A1 Import the data of book1.xlsx, and the @t option means that the first row is column header

A2 Add a column of SEX; the initial value is M

A3 Loop through each row of A2, split the second column FIRST NAME with & (@t option means to trim the blanks at both ends after splitting), and expand the current row into multiple rows according to the number of split values. Each row keeps the original columns and copies the column values, then the split values (indicated by ~) are used as the new FIRST NAME. If it is the second value obtained by splitting, set SEX as F, DOB takes the value of DOB1, and HEALTH takes the value of HEALTH1.

A4 Loop through each row of A3. If the values of DOB and HEALTH are both null, indicating that the current row is female, set SEX as F, DOB takes the value of DOB1, and HEALTH takes the value of HEALTH1.

A5 Retrieve the first to seventh columns of A4

A6 Store A5 in book2.xlsx

 3. Press F9 to execute the program, then open book2.xlsx to view the target table after the execution is over.

【Attachment】txl.zip


 
 •  242 View  •