Last week, I was working with one of our engineers to streamline an alarm reporting process. They receive spreadsheet CSVs of alarm data, and they have an Excel macro process to collate the data and pull out what they need. It’s not a great process, and it’s not sustainable, so we are using Alteryx to improve it. And, the CSVs come in a very unfortunate format. So, for this week’s post, I’m going to show you exactly how I took a wonky spreadsheet and turned it into something that BI applications can easily consume.
Here is the spreadsheet format. As you can see, there are quite a few pain points to this file.
- The unit name/number is listed at the top of each series of alarm events. It would be much better if the unit name were listed as a column, but it’s not.
- Also, there are a different number of alarm events. There won’t always be the same pattern of records.
- Column headers are repeated for each unit.
So, watch my You Tube video below to see how we wrangled this CSV file into something useful.
![](https://www.bigmountainanalytics.com/wp-content/uploads/2020/09/spreadsheet-1024x587.png)
Usually, I do a blog post and a video post because some folks like to read and some folks like to watch. This particular content is really more suited to video because I can talk through my thought process and the configuration of each tool.
Alteryx Version
Content created with Alteryx 2020.3.