Data Extracation from a Tabular Model to CSV-CodePudding

I have a model file built using a tabular model as below. I would like to extract the PartitionName, description, dataview, SourceType and Expression into a CSV or a Excel file... However I am not getting the right format. File: - ModelTest.bim

    "partitions": [
  {
    "name": "Sales_2020",
    "description": "Partition for sales table for Yr 2020",
    "dataView": "full",
    "source": {
      "type": "m",
      "expression": [
        "let",
        "    Source = #\"SQL/Dev01 xxxxxx database windows net;dbname\",",
        "    fact_sales = Source{[Schema=\"fact\",Item=\"fact_sales\"]}[Data],",
        "\t#\"sales_Yr_2020\" = Table.SelectRows(fact_sales, each [datekey] >=20200701 and [datekey] <= 20201231)",
        "in",
        "    #\"sales_Yr_2020\""
      ]
    }
  },
  {
    "name": "Sales_Yr_2021",
    "description": "Partition for sales table for Yr 2021",
    "dataView": "full",
    "source": {
      "type": "m",
      "expression": [
        "let",
        "    Source = #\"SQL/Dev01 xxxxxx database windows net;dbname\",",
        "    fact_sales = Source{[Schema=\"fact\",Item=\"fact_sales\"]}[Data],",
        "\t#\"sales_Yr_2021\" = Table.SelectRows(fact_sales, each [datekey] >=20210101 and [datekey] <= 20211231)",
        "in",
        "    #\"sales_Yr_2021\""
      ]
    }
  }

Format Required (CSV/Excel)

PartitionName,description,dataview,SourceType,Expression
"Sales_2020","Partition for Table sales_fct for Yr 2020", "full", "m", "let     Source = #\"SQL/Dev01 xxxxxx database windows net;dbname\",",    fact_sales = Source{[Schema=\"fact\",Item=\"fact_sales\"]}[Data],","\t#\"sales_Yr_2020\" = Table.SelectRows(fact_sales, each [datekey] >=20200701 and [datekey] <= 20201231)","in","    #\"sales_Yr_2020\""
"Sales_2021","Partition for Table sales_fct for Yr 2021", "full", "m", "let     Source = #\"SQL/Dev01 xxxxxx database windows net;dbname\",",    fact_sales = Source{[Schema=\"fact\",Item=\"fact_sales\"]}[Data],","\t#\"sales_Yr_2021\" = Table.SelectRows(fact_sales, each [datekey] >=20210101 and [datekey] <= 20211231)","in","    #\"sales_Yr_2021\""

Code I have so Far in PowerShell

cls

$BIM = "..\Modeltest.bim"
$sb = [System.Text.StringBuilder]::new()
$tabDelimiter = "`t"
$newline = "`n"
$out_file = "..\Modeltest\tables.txt"
Remove-Item -Path $out_file

$origmodel = (Get-Content $BIM -Raw) | Out-String | ConvertFrom-Json
$ModelTables = $origmodel.Model.tables
foreach($oTable in $ModelTables)
{
    $tName = $oTable.Name
    foreach($partition in $oTable)
    {
        foreach($oPartitionName in $partition.partitions.name)
        {
            foreach($oPartitionDescription in $partition.partitions.description)
            {
                foreach($oPartitionDataView in $partition.partitions.dataView)
                {
                     foreach($oXpression in $partition.source.expression)
                     {
                        
                     }
                     [void]$sb.Append($tName   "$tabDelimiter"   $oPartitionName   "$tabDelimiter"   $oPartitionDescription   "$tabDelimiter"   $oPartitionDataView   "$tabDelimiter"   $oXpression   "$newline")
                }
            }       
         }
    }
    
}
[System.IO.File]::WriteAllText($out_file,$sb.ToString() , [System.Text.Encoding]::ASCII)

CodePudding user response：

Your sample code shows tab separated value but your desired output shows comma separated. Also, your expression in your desired output is broken up into chunks as well. Your sample JSON isn't valid as listed, and there is no tables which your code references. Regardless of all this, you are missing out on many of the benefits of powershell by trying to build your own string for output. We can simply build your desired objects and then Export-Csv. Here is the sample json I used

$data = @'
    {
    "name": "Sales_Yr_2021",
    "description": "Partition for sales table for Yr 2021",
    "dataView": "full",
    "source": {
      "type": "m",
      "expression": [
        "let",
        "    Source = #\"SQL/Dev01 xxxxxx database windows net;dbname\",",
        "    fact_sales = Source{[Schema=\"fact\",Item=\"fact_sales\"]}[Data],",
        "\t#\"sales_Yr_2021\" = Table.SelectRows(fact_sales, each [datekey] >=20210101 and [datekey] <= 20211231)",
        "in",
        "    #\"sales_Yr_2021\""
      ]
    }
  }
'@ | ConvertFrom-Json

I used a calculated property to extract the desired values.

$output = $data | Select-Object Name,
                                Description,
                                Dataview,
                                @{n='SourceType';e={$_.source.type}},
                                @{n='Expression';e={-join $_.source.Expression}}

At this point we have an object with 5 properties. We can view the contents and/or simply export out to csv

# view output in the console
$output

name        : Sales_Yr_2021
description : Partition for sales table for Yr 2021
dataView    : full
SourceType  : m
Expression  : let    Source = #"SQL/Dev01 xxxxxx database windows net;dbname",    fact_sales = Source{[Schema="fact",Item="fact_sales"]}[Data], #"sales_Yr_2021" = 
              Table.SelectRows(fact_sales, each [datekey] >=20210101 and [datekey] <= 20211231)in    #"sales_Yr_2021"

$output | Format-Table

name          description                           dataView SourceType Expression                                                                                           
----          -----------                           -------- ---------- ----------                                                                                           
Sales_Yr_2021 Partition for sales table for Yr 2021 full     m          let    Source = #"SQL/Dev01 xxxxxx database windows net;dbname",    fact_sales = Source{[Schema="f...

# export
$output | Export-Csv -NoTypeInformation -Delimiter "`t" -Path $out_file

CodePudding user response：

See if this works :

$BIM = "c:\temp\test.txt"
$out_file = "c:\temp\test.csv"
$data = Get-Content -Path $BIM 
#make json
$data = $data.Replace('"partitions":','')
#add missing square bracket at end
$data = $data   "]"
$origmodel = $data | ConvertFrom-Json
foreach($row in $origmodel)
{
   $source = $row.source
   $type = $source.type
   $row.source = $type

$source | Format-Table
   $expression = $source.expression.Trim('{').Trim('}')
   #replace two double quotes with one
   $expression = $expression.Replace('""','"')
   $row | Add-Member -NotePropertyName Expression -NotePropertyValue ([string]$expression)
Write-Host "expression = " $expression

$row | Format-Table
}
$origmodel | Format-Table
$origmodel | Export-Csv -Path $out_file