Using PowerShell to clean-up excel data with multiple entries per cell

8231960108_b07671cb72_m
How many times did you come across a situation where you were supposed to work with Data that looks like this?:
CleanUpData
Some of the cells have multiple entries that are either separated by comma or line-breaks (via Alt+Enter). Furthermore several of those entries contain extraneous spaces. Happy days! What would be actually needed in order to work with the data is one clean entry per cell. In order to do that the columns for those rows that contain multiple entries per cell also need to be cross-joined (or Cartesian product) so that all possible combinations for the entries are accounted for. The end result should look like this:
CleanUpData2
How could we do the same using PowerShell? Let’s first have a look on how to do the cross-join part. This can be done quite easily with nested loops. Taking the second row from the example, the following will lead to the desired result:

$name= @('Nigel')
$products = 'Product 1', 'Product 2'
$preferences = 'Fast Delivery', 'Product Quality'
foreach($n in $name){
    foreach($product in $products){
        foreach($preference in $preferences){
            "$n, $product, $preference"
        }
    }
}

One way to turn this into a more generic solution is using recursion (You need to understand recursion in order to understand recursion 😉 ).
Here is an implementation of the same:

Ok, having covered the difficult part we now only need to read the data from excel clean it up and apply the Cartesian product function to it. Here is the full code to automate the whole process:

The above contains a modified version of the CartesianProduct function in order handle objects (actually ordered hashtables since they preserve the column order). If time permits I would like to implement the same as an Excel macro and share it as part of another post.
Update: I’ve added another post outlining how to do the same (using another approach) via an Excel Macro

shareThoughts


Photo Credit: pni via Compfight cc

Advertisements

I'd love to hear what you think

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s