LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@madilloar(madi lloar)

PowerQueryを自在に操る：Excelブックのクエリ定義を一括エクスポート＆インポートするPowerShellスクリプト

Last updated at Posted at 2025-10-03

動機

ExcelのPowerQueryは、データ整形や取得の自動化に欠かせない強力な機能なのですが、複数ブックにまたがるクエリの管理(例えば、クエリ定義そのものをGitで管理とか)や再利用をしようとして、今までは手作業でコピペでファイルにしてました。これがとにかくメンドクサイですし、ミスも起こりがち。そこで下記のPowerShellスクリプト「pqman.ps1」により、コマンド一発でエクスポート/インポートできます。

このスクリプトは、Excelブックに含まれるPowerQuery定義をファイルとしてエクスポートしたり、別のブックにインポートしたりする機能を備えています。バックアップ機能やエラー出力、一括処理にも対応してます。
クエリ管理の効率化と信頼性向上に貢献できるかなと思います。

まずはPowerShellスクリプト

pqman.ps1
<#
.SYNOPSIS
    ExcelブックのPowerQuery定義をエクスポート/インポートするスクリプト。
    - インポートする際、既存Excelをバックアップする機能。
    - 複数ブックの一括処理機能。
    - 失敗したクエリ名を標準エラー出力に出す機能。

    -Mode export:エクスポートモード
    例:
    pqman.ps1 -Mode export -ExcelPath "c:\input" -FolderPath "c:\PQ"

    [-ExcelPath]のブック群を読み、[-FolderPath]にPowerQuery定義群を出力します。

    例えば、[-ExcelPath "c:\input"]が次だったとします。

    c:\input
      +-- wb1.xlsx(PowerQueryの定義が「クエリ1」だけ存在している)
      +-- wb2.xlsx(PowerQueryの定義が「query1,query2」の2つ存在している)

    この場合、[-FolderPath "c:\PQ"]にPowerQuery定義を「ブック名\クエリ名.pq」の形式で出力します。

    c:\PQ
      +-- wb1
      |     +-- クエリ1.pq
      |
      +-- wb2
            +-- query1.pq
            +-- query2.pq

    -Mode import:インポートモード
    例:
    pqman.ps1 -Mode import -ExcelPath "c:\output" -FolderPath "c:\PQ"

    [-FolderPath]配下の「ブック名\クエリ名.pq」形式のPowerQuery定義群を読み、
    [-ExcelPath]のブック群に取り込みます。
    [-ExcelPath]配下のブック群はあらかじめ存在している必要があり
    且つ[-FolderPath]配下の「ブック名」フォルダの名称と一致している必要があります。

    次の例は、エクスポートしたときと違うブックにインポートする例です。
    
    例えば、[-FolderPath "c:\PQ"]が次だったとします。

    c:\PQ
      +-- b1
      |     +-- クエリ1.pq
      |
      +-- b2
            +-- query1.pq
            +-- query2.pq
    ※ブック名に該当するフォルダ名の所をわざと「b1、b2」にしています。
    ※こうすることで、インポート先にブック名「b1.xlsx、b2.xlsx」を容易しておけば、
    ※エクスポートしたときと違うブックにもインポートできます。

    c:\output
      +-- b1.xlsx(PowerQueryの定義「クエリ1」がインポートされる)
      +-- b2.xlsx(PowerQueryの定義「query1,query2」がインポートされる)

.PARAMETER Mode
    "export"または"import"を指定します。

.PARAMETER ExcelPath
    Excelファイル(.xlsx, .xlsm, .xlsb)または、フォルダのパス(フォルダの場合は直下の
    Excelファイル(.xlsx, .xlsm, .xlsb)を一括処理)します。

.PARAMETER FolderPath
    エクスポート先、または、インポート元のルートフォルダを指定します。
    - export:<FolderPath>/<ブック名>/*.pq に書き出し
    - import:<FolderPath>/<ブック名>/*.pq から読み込み

.PARAMETER Backup
    このオプションを指定すると、インポート時にバックアップファイルを作成します。
    バックアップファイルは<FolderPath>\Backup配下に保存します。

.EXAMPLE
    pqman.ps1 -Mode export -ExcelPath "c:\excel_files" -FolderPath "c:\pq"

.EXAMPLE
    pqman.ps1 -Mode imprt -ExcelPath "c:\excel_files" -FolderPath "c:\pq" -Backup
#>

[CmdletBinding()]
param(
    [Parameter(Mandatory=$true)]
    [ValidateSet("export","import")]
    [string]$Mode,

    [Parameter(Mandatory=$true)]
    [string]$ExcelPath,

    [Parameter(Mandatory=$true)]
    [string]$FolderPath,

    [switch]$Backup
)

## エラーキャッチ用
$ErrorActionPreference = 'Stop'

## エクスポート用の出力ルートフォルダ作成
if ($Mode -eq "export" -and -not (Test-Path $FolderPath)) {
    New-Item -ItemType Directory -Path $FolderPath | Out-Null
}

## Excelファイル一覧取得(.xlsx, .xlsm, .xlsb)
$excelFiles = @()
if(Test-Path $ExcelPath -PathType Container) {
    $excelFiles = Get-ChildItem -Path $ExcelPath -File | Where-Object {
        $_.Extension -in ".xlsx",".xlsm",".xlsb"
    }
} elseif (Test-Path $ExcelPath -PathType Leaf) {
    $ext = [IO.Path]::GetExtension($ExcelPath).ToLower()
    if($ext -in @(".xlsx",".xlsm",".xlsb")){
        $excelFiles = , (Get-Item $ExcelPath)
    } else {
        Write-Error "ExcelPathは.xlsx,.xlsm,.xlsbのいずれかを指定してください。: $excelPath"
        exit 1
    }
} else {
    Write-Error "指定したパスが存在しません。: $ExcelPath"
    exit 1
}
Write-Host "Excelファイル数: $($excelFiles.Count)"

## Excel起動(COMオブジェクト利用)
$excel = $null
try {
    $excel = New-Object -ComObject Excel.Application
} catch {
    Write-Error "ExcelのCOMオブジェクトを作成できませんでした。Excelがインストールされているか確認してください。"
    exit 1
}
$excel.Visible = $false

foreach ($file in $excelFiles) {
    Write-Host "`n=== 処理中:$($file.FullName) ==="
    $workbook = $null
    try {
        $workbook = $excel.Workbooks.Open($file.FullName)
        if ($Mode -eq "export") {
            ## ブック毎の出力フォルダ
            $exportFolder = Join-Path $FolderPath $file.BaseName
            if (-not (Test-Path $exportFolder)) {
                New-Item -ItemType Directory -Path $exportFolder | Out-Null
            }
            ## クエリ毎にtry/catchして、失敗時はクエリ名のみを標準エラー出力
           foreach ($query in $workbook.Queries) {
                try {
                    $filePath = Join-Path $exportFolder ($query.Name + ".pq")
                    $query.Formula | Out-File -FilePath $filePath -Encoding UTF8 -ErrorAction Stop
                    Write-Host "Exported: $($query.Name) -> $filePath"
                } catch{
                    [Console]::Error.WriteLine($query.Name)
                    Write-Host "Exported failed: $($query.Name) - $($_.Exception.Message)"
                }
            }
        } elseif ($Mode -eq "import") {
            ## バックアップ
            if ($Backup) {
                try {
                    $backupFolder = Join-Path $FolderPath "Backup"
                    if (-not (Test-Path $backupFolder)) {
                        New-Item -ItemType Directory -Path $backupFolder | Out-Null
                    }
                    ## TODO:オリジナルの拡張子を活かす必要がある
                    $backupFile = Join-Path $backupFolder ($file.BaseName + "_" + (Get-Date).ToString("yyyyMMdd_HHmmss") + ".xlsx")
                    Copy-Item -Path $file.FullName -Destination $backupFile -ErrorAction Stop
                    Write-Host "Backup created: $backupFile"
                } catch {
                    Write-Host "Backup failed: $($file.Name) - $($_.Exception.Message)"
                    exit 1
                }
            }
            ## インポート元フォルダ
            $importFolder = Join-Path $FolderPath $file.BaseName
            if (-not (Test-Path $importFolder)) {
                Write-Host "インポート先ブックに対応するインポート元フォルダ・ファイルが見つかりません。: $importFolder"
            } else {
                ## 各pqファイル毎にtry/catchし、失敗時は名前を標準エラー出力
                Get-ChildItem -Path $importFolder -Filter *.pq -File | ForEach-Object {
                    $queryName = $_.BaseName
                    try {
                        $formula = Get-Content $_.FullName -Raw -ErrorAction Stop

                        ## 既存クエリ削除(存在しない場合は無視)
                        try {
                            $existing = $workbook.Queries.Item($queryName)
                            if ($existing) {
                                Write-Host "インポート先ブックに同名のクエリが存在していたので上書きします。: $queryName"
                                $existing.Delete()
                            }
                        } catch {
                            ## 存在しない場合は、そのままインポートします
                        }
                        ## クエリを追加。単純にAddすると、クエリそのものを標準出力するのが邪魔なのでOut-Nullにする
                        ## pqファイルが破損していても、破損したなりにインポートしてしまいます。
                        $workbook.Queries.Add($queryName, $formula) | Out-Null
                        Write-Host "Imported: $queryName"
                    } catch {
                        [Console]::Error.WriteLine($queryName)
                        Write-Host "Import failed: $queryName - $($_.Exception.Message)"
                    }
                }
                ## 少なくとも1件処理した場合はブックを保存(保存失敗はthrow)
                try {
                    $workbook.Save()
                } catch {
                    Write-Host "Save failed: $($file.Name) - $($_.Exception.Message)"
                }

            }
        }
    } catch {
        ## ブックオープン等で失敗
        Write-Host "Workbook-level failed: $($file.Name) - $($_.Exception.Message)"
    } finally {
        if ($workbook -ne $null) {
            try {
                $workbook.Close($false)
            } catch {
                ## ここでの失敗はどうしようもない
            }
        }
    }
}

## 終了処理
try {
    $excel.Quit()
} catch {
    ## ここでの失敗はどうしようもない
}
try {
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
} catch {
    ## ここでの失敗はどうしようもない
}

Write-Host "`n=== 全処理終了 ==="

スクリプトの概要と目的

「pqman.ps1」は、PowerQuery定義をファイルとしてエクスポート・インポートするPowerShellスクリプトです。主な機能は以下の通りです：

  • Excelブックからクエリ定義を抽出し、フォルダ構造に保存（exportモード）
  • クエリ定義ファイルを既存のExcelブックに取り込み（importモード）
  • インポート時のバックアップ機能（-Backupスイッチ）
  • 複数ブックの一括処理
  • クエリ単位でのエラー出力（標準エラー）

エクスポート処理の詳細

exportモードでは、指定されたExcelファイル群からPowerQuery定義を抽出し、以下の形式で保存します。

<FolderPath>\<ブック名>\<クエリ名>.pq

例えば、wb1.xlsxに「クエリ1」、wb2.xlsxに「query1」「query2」が含まれている場合、出力は以下のようになります。

c:\PQ
  +-- wb1
  |     +-- クエリ1.pq
  |
  +-- wb2
        +-- query1.pq
        +-- query2.pq

各クエリは個別にtry/catchして、失敗した場合はクエリ名を標準エラー出力します。

インポート処理の詳細

importモードでは、指定されたフォルダ構造からクエリ定義を読み込み、対応するExcelブックに追加します。インポート先のブック名とフォルダ名が一致している必要があります。

また、-Backupスイッチを指定すると、インポート前にバックアップファイルが作成されます。

<FolderPath>\Backup\<ブック名>_yyyyMMdd_HHmmss.xlsx

既存クエリが存在する場合は削除してから追加されます。処理結果は標準出力に、失敗したクエリ名は標準エラー出力します。

TODO:バックアップファイルの拡張子が".xlsx"固定なのを何とかしたい。。。

実行例とユースケース

一括エクスポート

pqman.ps1 -Mode export -ExcelPath "c:\input" -FolderPath "c:\PQ"

異なるブックへのインポート

pqman.ps1 -Mode import -ExcelPath "c:\output" -FolderPath "c:\PQ" -Backup

このように、クエリ定義をテンプレート化して別ブックに展開することで、業務の標準化や再利用が容易になります。Gitでバージョン管理もやりやすくなります。

参考にしたページ

感謝です。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?