"There is a product data table ProductCategories.xls: [pic] This table contains the category info .."

mars RaqForum 25 No.
376 View • 2 Years ago

5.10 Split by column - merge duplicate rows after splitting

desktop and excel data processing cases(180)

There is a product data table ProductCategories.xls:

imagepng

This table contains the category information of products, such as CategoryID, CategoryName, Description, etc. Now we need to separate the category information columns to form one Categories table, and take the remaining product columns as the Products table. Since there are many products under one category field, and many of them are duplicate after splitting, deduplication needs to be performed.

The results after splitting are shown as below:

Categories.xlsx：

imagepng

Products.xlsx：

imagepng

Script:

	A
1	=T(“ProductCategories.xlsx”)
2	=A1.groups(CategoryID,CategoryName,Description)
3	=A1.new(CategoryID,ProductID,ProductName,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel)
4	=T(“Categories.xlsx”,A2)
5	=T(“Products.xlsx”,A3)