Data comes to us in many different forms and formats. Sometimes it is written in a way that just doesn’t seem right or at least makes it awkward to read. In this instance we have a date column that simply looks ugly and is not legible in a way that suits us.
The date is difficult to read and the time stamps are useless to us in this format. We want to clean the date column up and make it easier to read.
First we grab our Data Cleansing Tool and bring it on to the canvas. In the window select None above the second pane and then select date. Go down to the pane below and select Letters. This will remove the ‘T’ that we see in the date format currently and will allow for an easier transformation in our next step. You can Run the workflow now and you’ll see there is no more ‘T’
Next we go to our toolbox and under Parse we bring Date/Time onto the canvas.
Select the string field to convert – date
Specify the new column name – DateTime_Out
And then insert the Custom format to match your latest date format. In our example, yyyymmdd000000
You can then Run the workflow and you’ll see a new column has been added with the desired format.
Now in order to create a new dataset with this format, we can drag our Select Tool on to the canvas and in the pane on the left use the arrows to move the newly created column up towards the top or righ click on the new column, hold and drag it into place.
To remove the original date format just untick the box. You are ready to hit run and now you will have a far more user friendly date format to work with.
As a very final step, if you want to remove the time stamp, you simply need to change the data type from datetime to date, hit run and there you have it.
About The Information Lab
The Information Lab Ireland is at the forefront of creating a data-driven culture in Ireland.
As part of its vision, The Information Lab Ireland regularly hosts free events throughout the country to show how being data-driven can improve decision making and lead to a better understanding of the world around us. For more on these events please like us on Facebook, follow us on Twitter or catch up with us on LinkedIn