Wednesday, 18 September 2019

How to convert Tableau CSV/Cross Tab export to Tabular format

From the previous post, one can export the tableau workbook in pdf format or CSV format and send it to the users.  When some users want to analyse the underlying data of tableau workbook on a daily basis, you would need to send them the underlying data in a CSV format but when you export workbook in a cross tab or CSV format from Tableau server, It would be exporting the data with Dim Column Name1, Dim Column Name2, Dim Column Name3, Measure Names, Measure Values. But users like to get the underlying data in a tabular format with each particular measure name as a different column.  In this scenario, use below PowerShell script to convert Tableau CSV export to Tabular format with all dimension column names and measures column names.  Let me know in case if anyone has easier approach than this. Please update the respective values wherever needed.

#Requires -Version 4
# Transpose by "Property", group by "Server" 
# intersection of "Property X Server = Value"
# Phone Number, First Name, Last Name, Measure Names, Measure Values
$Rows = Import-Csv .\WORKBOOK-NAME.csv
$MeasureNames = $Rows |Select-Object -ExpandProperty "Measure Names" |Select -Unique
$ConsolidatedRows = $Rows |Group-Object "Dim_Unique_column_name"|ForEach-Object {
    $NewRowProperties = @{}
    $NewRowProperties = [ordered]@{ "Dim_Unique_column_name" = [String]::Parse($_.Name) }
    foreach($Row in $_.Group)
        $NewRowProperties.Add("First Name",[String]($Row."First Name"))
        $NewRowProperties.Add("Last Name",[String]($Row."Last Name"))
        $NewRowProperties.Add("Phone Number",[String]($Row."Phone Number"))
        $NewRowProperties.Add($Row."Measure Names",[String]($Row."Measure Values"))               
    New-Object psobject -Property $NewRowProperties
$ConsolidatedRows |FT -AutoSize 
#$ConsolidatedRows = $ConsolidatedRows |Export-Csv .\WORKBOOK-NAME_TABULAR.csv  -NoTypeInformation 
$ConsolidatedRows |Select-Object @("Dim_Unique_column_name";"First Name";"Last Name";"Column4";"Phone Number";$MeasureNames) |Export-Csv .\WORKBOOK-NAME_TABULAR.csv -NoTypeInformation

No comments:

Post a comment