I recently got hooked on Tableau Public. It’s pretty awesome!
One of the downsides, however, is the need to find yourself some interesting “open” datasets to visualize.
As the number of prospective students at British universities has long been of interest to me, I thought I’d check out if UCAS had any publicly available data to crunch
Luckily for me, they did
Yet, the data format of those files was not what I would call an analyst delight – there were 6 extra rows on top of each dataset, which even the Data Interpreter in Tableau Public couldn’t handle
If I were to make a comprehensive analysis out of this data, I would have to spend at least an hour on data preparation – i.e. getting rid of the 6 extra lines from each of the 103 files
Good that I’ve got some VBA tricks up my sleeve!
After using Power Query to generate a list of all the files saved in that folder, I fired up the following code, which would loop through every workbook on the list, open it, delete the first 6 rows and save it
Sub TweakAllFiles() | |
Dim Cell As Range | |
Dim path As String | |
Dim file As String | |
Dim fullfilepath As String | |
'------------------------------------------------------------ | |
'written by Angelina Teneva, 2017 | |
'----------------------------------------------------------- | |
Application.DisplayAlerts = False | |
For Each Cell In ThisWorkbook.Worksheets("UCAS").Range("A2:A103") | |
file = Cell.Value | |
path = Cell.Offset(0, 6).Value | |
fullfilepath = path & file | |
If file Like "*2017*" Then | |
Workbooks.Open Filename:=fullfilepath, ReadOnly:=False, UpdateLinks:=False | |
With ActiveWorkbook | |
With ActiveSheet | |
Rows("1:5").EntireRow.Delete | |
Columns("B:C").Replace "'", "" | |
End With | |
.Save | |
.Close | |
End With | |
End If | |
Next Cell | |
Application.DisplayAlerts = True | |
End Sub |
The result?
You can find the interatctive workbook here https://public.tableau.com/views/ApplicationstoBritishEducation/Overview
Happy VBA coding!