...

Merge row-based Excel tables

 

1. With same columns

There are multiple Excel files in a certain directory. The headers and column structures of these files are exactly the same, but the number of rows and contents are different. Now the task is to merge all the data of these files into one Excel file with the same table header. 

Example: There are Excel files of daily orders in the e:/orders directory. The structures of each file are the same with column names in the first row and data starting from the second row. As shown in the figure below, please merge them into one order table orders.xlsx.

..

Write SPL script:

A

1

=directory@p("e:/orders/*.xlsx")

2

=A1.conj(T(~))

3

=T("e:/orders.xlsx",A2)

A1 List all xlsx file names in the e:/orders directory, and the @p option means to list the full path names of the files.

A2 Read out the data of all files in A1 and merge them.

A3 Write the merged data in A2 to the file orders.xlsx.

 

2. With similar columns

If the structures of these files are not exactly the same, for example, the order or number of the columns is different, but each file contains several columns in common, and the task is to merge the data of these common columns into one file. When merging these files, the common column data in each file need to be read in a specified order. 

Example: We still use the example illustrated in last section, where the order files are known to contain five columns: ID, Company, Area, OrderDate, and Amount, but the order of columns in each file is not the same. Some files have other columns. The task is to merge the five columns of data in the files into orders.xlsx.

Write SPL script:

A

1

=directory@p("e:/orders/*.xlsx")

2

=A1.conj(T(~;ID,Company,Area,OrderDate,Amount))

3

=T("e:/orders.xlsx",A2)

A1 List all xlsx file names in the e:/orders directory, and the @p option means to list the full path names of the files.

A2 Read out the data of ID, Company, Area, OrderDate, and Amount columns in all files in A1 and merge them.

A3 Write the merged data in A2 to the file orders.xlsx.

 

3. Convert file name into column data

When merging data, we need to add a column to the merged data and assign values to this column with the names of files before the merge in order to mark the data category or source. 

Example: There are various Excel files of parts order in the e:/orders directory. The file name is the name of the part, such as tyre.xlsx, engine.xlsx, etc. The data structures of each file are the same as those in the previous example. Please merge them to create an order table orders.xlsx, and add a column of PartName to record the names of parts. 

Write SPL script:

A

1

=directory@p("e:/orders/*.xlsx")

2

=A1.conj((fn=filename@n(~),T(~).derive(fn:PartName)))

3

=T("e:/orders.xlsx",A2)

A1 List all xlsx file names in the e:/orders directory, and the @p option means to list the full path names of the files.

A2 Loop through all files in A1, use the filename function to get the name of the current file, and assign it to the variable fn. The @n option means to get the file name without extension. After reading the data of the current file with the T function, add a column of PartName with the column value as fn, and merge all the data.

A3 Write the merged data in A2 to the file orders.xlsx.

 

 


 
 •  347 View  •