4.19 Format conversion - merge multiple card-style files to form one row-based table

 

There are multiple card-style files as follows:

Andrew.Fuller.xlsx:

imagepng

Janet.Leverling.xlsx:

imagepng

Margaret.Peacock.xlsx:

imagepng

Nancy.Davolio.xlsx:

imagepng

Now we want to merge them to form one row-based table. The merged format is as follows:

Employee.xlsx:

imagepng

Script:

A B
1 =directory@p(“Cards/..xlsx”)
2 =create(ID,FirstName,LastName,Gender,Title,Birthday,HomePhone,PostalCode,Address)
3 [B1,B2,D2,D1,B3,B4,B5,B7,B6]
4 for A1 =file(A4).xlsopen()
5 =A3.(B4.xlscell(~))
6 >A2.record(B5)
7 =T(“Cards/Employee.xlsx”,A2)

A1: List all files named by Name in the Cards directory

A2: Create a result table sequence

A3: List the cell names in the card-style files to be read

A4: Loop through the files in A1

B4: Open the files

B5: Read the data of the cells listed in A3 in the file

B6: Insert the data in B5 into A2

A7: Write the table sequence in A2 to Employee.xlsx


esProc Desktop and Excel Processing
4.18 Merge by row and column simultaneously - keep data that firstly appear
4.20 Format conversion - merge multiple primary-sub table files to form two row-based tables