Tabular Model分区数据提取至CSV：格式问题及代码修正请求

用户持有基于Tabular Model构建的ModelTest.bim模型文件，需要提取其中的PartitionName、description、dataview、SourceType和Expression字段并生成符合要求的CSV格式文件，但当前使用的PowerShell代码无法输出正确格式。

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\""
      ]
    }
  }

目标CSV格式 #

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],    #\"sales_Yr_2020\" = Table.SelectRows(fact_sales, each [datekey] >=20200701 and [datekey] <= 20201231) in    #\"sales_Yr_2020\""
"Sales_Yr_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],    #\"sales_Yr_2021\" = Table.SelectRows(fact_sales, each [datekey] >=20210101 and [datekey] <= 20211231) in    #\"sales_Yr_2021\""

修正后的PowerShell代码 #

cls

$BIM = "..\Modeltest.bim"
$out_file = "..\Modeltest\partitions.csv"
# 删除已存在的输出文件
if (Test-Path $out_file) { Remove-Item -Path $out_file }

# 读取并解析BIM文件
$origmodel = Get-Content $BIM -Raw | ConvertFrom-Json
$ModelTables = $origmodel.Model.tables

# 初始化CSV表头
$csvContent = @("PartitionName,description,dataview,SourceType,Expression")

foreach ($oTable in $ModelTables) {
    # 遍历当前表的所有分区
    foreach ($partition in $oTable.partitions) {
        # 将表达式数组拼接为单行，转义双引号以符合CSV规范
        $expression = $partition.source.expression -join " " -replace '"', '\"'
        # 构造符合格式的CSV行
        $csvRow = '"{0}","{1}","{2}","{3}","{4}"' -f 
            $partition.name,
            $partition.description,
            $partition.dataView,
            $partition.source.type,
            $expression
        $csvContent += $csvRow
    }
}

# 将内容写入CSV文件，使用UTF-8编码避免乱码
$csvContent -join "`n" | Out-File -Path $out_file -Encoding UTF8

代码修正说明 #

1. 循环逻辑优化：直接遍历每个表的partitions集合，每个分区单独处理，去除原代码中无效的多层嵌套循环。
2. 表达式处理：将表达式数组拼接为单行，同时转义字符串中的双引号，确保CSV解析时不会出错。
3. CSV格式规范：所有字段用双引号包裹，适配包含特殊字符或逗号的字段场景。
4. 输出调整：生成标准CSV文件而非制表符分隔文本，使用UTF-8编码避免字符乱码。
5. 冗余内容移除：删除原代码中多余的表名字段，完全匹配目标CSV的字段要求。

内容的提问来源于stack exchange，提问作者NottyHead