powershell 从表格模型到CSV的数据提取

deikduxw 于 2023-01-26 发布在 Shell

关注(0)|答案(2)|浏览(130)

我有一个使用表格模型构建的模型文件，如下所示。我想将PartitionName、描述、数据视图、源类型和表达式提取到CSV或Excel文件中...但是，我没有获得正确的格式。文件：- 模型测试.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\""
      ]
    }
  }

格式要求（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\""

到目前为止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)

powershell

来源：https://stackoverflow.com/questions/75045738/data-extracation-from-a-tabular-model-to-csv

2条答案

按热度按时间

wfveoks01#

示例代码显示的是制表符分隔的值，但所需的输出显示的是逗号分隔的值。此外，所需输出中的表达式也被分解为多个块。示例JSON如所列无效，并且代码中没有引用表。尽管如此，如果您试图构建自己的字符串用于输出，则会错过powershell的许多好处。我们可以简单地构建所需的对象，然后构建Export-Csv。

$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

我使用了计算属性来提取所需的值。

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

现在我们有了一个具有5个属性的对象，可以查看内容和/或导出为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

赞(0）回复(0）举报 2023-01-26

8ljdwjyq2#

看看这是否有效：

$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

赞(0）回复(0）举报 2023-01-26

我来回答

powershell 从表格模型到CSV的数据提取

2条答案

相关问题

热门标签

最新问答