Preparing a CSV File with Alteryx

by Jamie Gough

Today was our introduction to Alteryx and we ended the day by editing two CSV files that were unreadable with Tableau. By using a similar data set, I will run through a couple of functions that can be used to make such data readable by Tableau.

Below is an image of the CSV file I created, gender stats, using OECD data. To the left is how it looks in Excel and on the right, is Tableau’s observation of the data.

As you can see the data in its current format can’t be read by Tableau. It’s unable to distinguish columns, due to the table starting four rows down, and fails to recognise any data types. Beneath, is a screenshot of the Alteryx workflow I used to convert this file to a readable one in TableauHere’s a quick description of the functions that were used at each stage of the workflow:

  1. Input Data – Loaded the original CSV file.
  2. Sample – Removed the first 3 rows of data
  3. Dynamic Rename – Assigned the top row, originally the 4th, to field names
  4. Transpose – Swaps years from rows to column to enable the next stage
  5. Data Cleansing – Removed square brackets from around the years
  6. Filter – Was used to remove rows with ‘..’, which indicated missing data
  7. Select – Renamed the title of rows to match the data and removed unnecessary columns.
  8. Output Data – Created new TDE file with edited dataset.

To complete these 8 functions, including the creation of a new file, Alteryx only took 1.6 seconds and now I have a usable file to analyse in tableau! Here’s a screenshot of the finished product in Tableau.

© 2022 The Information Lab Ltd. All rights reserved.