ForEach Nested Loop in PowerShell

Question

I have this piece of code where I am extracting table names from the adventureworks.bim file using a for each loop. However, I am missing something here because I required a Table name per object and not the final table in the loop. The details are as below

cls
$BIM = "C:\Users\Desktop\adventureworks.bim"
$origmodel = (Get-Content $BIM -Raw) | Out-String | ConvertFrom-Json
   ForEach($table in $origmodel.Model.tables.name)
   {
     $ColumnProperty = $origmodel.Model.tables.columns | ForEach-Object {
                                                            [pscustomobject] @{
                                                              'Table Name' = $table
                                                              'Object Name' = $_.name
                                                              'DataType' = $_.dataType
                                                            }
                                                        }
                 }
 $ColumnProperty | ConvertTo-Csv -NoTypeInformation

Result I Get

"Table Name", "Object Name", "Datatype"
"Date","RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61","int64"
"Date","CurrencyKey","int64"
"Date","Currency Code","string"
"Date","CurrencyName","string"
"Date","RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61","int64"
"Date","CustomerKey","int64"
"Date","GeographyKey","int64"
"Date","Customer Id","string"
"Date","Title","string"
"Date","First Name","string"
"Date","Middle Name","string"
"Date","Last Name","string"
"Date","Name Style","boolean",
"Date","Birth Date","dateTime",
"Date","Marital Status","string"
"Date","Suffix","string"
"Date","Gender","string"
"Date","RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61","int64"
"Date","DateKey","int64"
"Date","Date","dateTime",
"Date","Day Number Of Week","int64"
"Date","Day Name Of Week","string"
"Date","Day Of Year","int64"
"Date","Week Of Year","int64"
"Date","Month Name","string"

Result I Need

"Table Name", "Object Name", "DataType"
"Currency","RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61","int64"
"Currency","CurrencyKey","int64"
"Currency","Currency Code","string"
"Currency","CurrencyName","string"
"Customer","RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61","int64"
"Customer","CustomerKey","int64"
"Customer","GeographyKey","int64"
"Customer","Customer Id","string"
"Customer","Title","string"
"Customer","First Name","string"
"Customer","Middle Name","string"
"Customer","Last Name","string"
"Customer","Name Style","boolean",
"Customer","Birth Date","dateTime",
"Customer","Marital Status","string"
"Customer","Suffix","string"
"Customer","Gender","string"
"Date","RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61","int64"
"Date","DateKey","int64"
"Date","Date","dateTime",
"Date","Day Number Of Week","int64"
"Date","Day Name Of Week","string"
"Date","Day Of Year","int64"
"Date","Week Of Year","int64"
"Date","Month Name","string"

Simon · Accepted Answer · 2023-01-13 03:37:04Z

1

Try this on for size:

$BIM = "C:\Users\Desktop\adventureworks.bim"
$origmodel = Get-Content $BIM -Raw | ConvertFrom-Json

ForEach ($table in $origmodel.Model.tables) {
  $ColumnProperty += $table.columns | ForEach-Object {
    [pscustomobject] @{
      'Table Name'  = $table.name
      'Object Name' = $_.name
      'DataType'    = $_.dataType
    }
  }
}
$ColumnProperty | ConvertTo-Csv -NoTypeInformation

Corrections

No need to use Out-String
ColumnProperty needed a += as = was overwriting every entry
Just a few mix-ups around the foreach loops.

answered Jan 13, 2023 at 3:37

Simon

3762 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Santiago Squarzon · Accepted Answer · 2023-01-13 04:05:13Z

You need to loop over .model.tables first then an inner loop for each columns:

$req = Invoke-RestMethod https://raw.githubusercontent.com/TabularEditor/TabularEditor/master/TabularEditorTest/AdventureWorks.bim
$req.model.tables | ForEach-Object {
    foreach($column in $_.columns) {
        [pscustomobject]@{
            Table      = $_.name
            ObjectName = $column.name
            DataType   = $column.dataType
        }
    }
} | Export-Csv path\to\export.csv -NoTypeInformation

Output to the console would look like this for the first few objects:

Table               ObjectName                                     DataType
-----               ----------                                     --------
Currency            RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61 int64
Currency            CurrencyKey                                    int64
Currency            Currency Code                                  string
Currency            CurrencyName                                   string
Customer            RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61 int64
Customer            CustomerKey                                    int64
Customer            GeographyKey                                   int64
Customer            Customer Id                                    string
Customer            Title                                          string
...
...

Collectives™ on Stack Overflow

ForEach Nested Loop in PowerShell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related