Home > Using PowerShell to organize Data Model Projects

Using PowerShell to organize Data Model Projects

Years after years we are creating new models. Maybe we save all the different projects to the same folder. After a while, we can have many projects in this directory.

This blog post shares a PowerShell Script which copy|move the project files to subfolders created by using the Copyright Year of the projects.
Copyright Year

System requirements

ER/Studio Data Architect must be installed as the script opens the different projects to get their Copyright Year.
PowerShell must be installed too (a version is usually already installed with recent Windows OS).

Script

You can copy the script below and paste it in a Notepad. Save it with an extension .ps1 (not .ps1.txt).
Likewise you can use Windows PowerShell ISE.
Windows PowerShell ISE
Here is the script:

# Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope Process

$DEFAULT_PROJECT_FOLDER = '<YOUR_PATH>'

Clear-Host

Write-Host "Please enter your project folder path:"
Write-Host -ForegroundColor Gray "Default value: $DEFAULT_PROJECT_FOLDER"
$projectsFolder = (Read-Host).Trim()

if($projectsFolder.Equals("")) {

    $projectsFolder = $DEFAULT_PROJECT_FOLDER

}

if(Test-Path -Path ($projectsFolder)) {

    Write-Host ''
    Write-Host -ForegroundColor Yellow "Project folder: $projectsFolder"
    $nbProjectMoved = 0

    try {

        $app = New-Object -ComObject ERStudio.Application

        $version = "" + $app.ERSFullVersion()
        $v = $version.Split('.')
        $version = 'ER/Studio Data Architect ' + $v[0] + '.' + $v[1] + '.' + $v[2] + ' Build (' + $v[3] + ')'

        # Get all DM1 files
        Get-ChildItem $projectsFolder -Include *.dm1 -Name | ForEach{

            Write-Host ''
            Write-Host -ForegroundColor Cyan "Project: $($_)"
            $project = $projectsFolder + '\' + $($_)
            Write-Host -ForegroundColor Yellow "Path: $project"

            $diag = $app.OpenFile($project)

            if ($diag -ne $null) {

                $year = ("" + $diag.CopyrightYear()).Trim()

            }
            else {

                Write-Warning "Project can not be opened. File already opened, database platform not supported or version issue?";"Version used: $version"
                $year =""

            }

            if ("".Equals($year)) {

                $year = "Unknown"

            }

            $app.CloseDiagram($($_))

            Write-Host -ForegroundColor Magenta "Year: $year"

            # Replace invalid filename chars with _
            $year = $year.Split([IO.Path]::GetInvalidFileNameChars()) -join '_'

            # Check target folder
            if(-Not(Test-Path -Path ("$projectsFolder\$year"))) {

                # Folder doesn't exist: create it
                $null = New-Item -Force -ItemType Directory -Path "$projectsFolder\$year"

            }

            # Check target folder
            if(Test-Path -Path ("$projectsFolder\$year")) {

                # Copy the project
                Copy-Item $project "$projectsFolder\$year" -Force

#                # Move the project
#                Move-Item -Path $project -Destination ("$projectsFolder\$year\$($_)") -Force

                $nbProjectMoved++
            }
        }

        Write-Host "";"$nbProjectMoved projects copied|moved"

    }
    catch [System.Runtime.InteropServices.COMException] {

        Write-Warning "Can not create the ComObject ERStudio.Application";"ER/Studio Data Architect installed?"
        Write-Error $_.Exception.Message

    }
}
else {

    Write-Warning "Folder doesn't exist: $projectsFolder"

}

The script currently copies the files to the subfolders. Nonetheless, you can edit the script to move the files instead, like this:

#            # Copy the project
#            Copy-Item $project "$projectsFolder\$year" -Force

            # Move the project
            Move-Item -Path $project -Destination ("$projectsFolder\$year\$($_)") -Force

Evidently do not forget to change the default path value too:

$DEFAULT_PROJECT_FOLDER = '<YOUR_PATH>'

You can obviously edit the script to customize it as you need.

If you can not run the script, there’s probably an execution policy issue. So you can check this PowerShell documentation page.

Output

Moreover, if you need more information about an object, you can use the function Get-Member.
For example with the variable $diag [ERStudio.Diagram]:
Get-Member

Summary

In this blog post, we used:

  • the ER/Studio Data Architect COM automation interface through a PowerShell Script
    • (to get the ERStudio.exe Full Version and Build Number)
    • to open a project
    • to get the Copyright Year
    • to close the project

Bonus

I shared a video (5′) to support this blog post! 👍

ER/Studio Logo
Aligning complex data environments with business goals for over 30 years.
Copyright © 2024 Idera, Inc.