Created
November 17, 2021 13:42
-
-
Save amano41/85865b888e3c70fcb1fbdd7639d56992 to your computer and use it in GitHub Desktop.
Dump tables, relationships and queries from Access database file
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 実行には ACE14.0 以降が必要 | |
# Microsoft Access Database Engine 2010 Redistributable | |
# http://www.microsoft.com/en-us/download/details.aspx?id=13255 | |
Param ( | |
[Parameter()] | |
[String] $DirPath = ".", | |
[String] $FileName = "*", | |
[ValidateSet("Table", "Query", "SQL", "Relation")][String] $Type = "Table", | |
[Int] $MaxRecords = -1 | |
) | |
function Write-Line($line, $file) { | |
$line | Out-File -FilePath $file -Encoding UTF8 -Append | |
} | |
function Export-Table($accfile, $outfile) { | |
$dbe = New-Object -ComObject DAO.DBEngine.120 | |
$db = $dbe.OpenDatabase($accfile) | |
$tables = $db.TableDefs | |
foreach ($table in $tables) { | |
$name = $table.Name | |
if (!$name.startsWith("MSys")) { | |
$rs = $db.OpenRecordset($name) | |
$nf = $rs.Fields.Count | |
# 最大出力件数 | |
if ($MaxRecords -lt 0) { | |
$max = $rs.RecordCount | |
} | |
else { | |
$max = $MaxRecords | |
} | |
# テーブル名 | |
Write-Line ("[" + $name + "]") $outfile | |
# フィールド名 | |
$line = @() | |
$rs.Fields | foreach-object { | |
$line += $_.Name | |
} | |
Write-Line ($line -join "`t") $outfile | |
# レコード | |
$i = 0 | |
$rs.MoveFirst() | |
while ($rs.EOF -eq $false -and $i -lt $max) { | |
$line = @() | |
$rs.Fields | foreach-object { | |
$line += $_.Value | |
} | |
Write-Line ($line -join "`t") $outfile | |
$rs.MoveNext() | |
$i++ | |
} | |
Write-Line "" $outfile | |
} | |
} | |
$db.Close() | |
} | |
function Export-Relation($accfile, $outfile) { | |
$dbe = New-Object -ComObject DAO.DBEngine.120 | |
$db = $dbe.OpenDatabase($accfile) | |
$relations = $db.Relations | |
foreach ($relation in $relations) { | |
$name = $relation.Name | |
if (!$name.startsWith("MSys")) { | |
# リレーションシップ名 | |
Write-Line ("[" + $name + "]") $outfile | |
# テーブルの対応関係 | |
$line = $relation.Table + "`t" + $relation.ForeignTable | |
Write-Line $line $outfile | |
# フィールドの対応関係 | |
$relation.Fields | foreach-object { | |
$line = $_.Name + "`t" + $_.ForeignName | |
Write-Line $line $outfile | |
} | |
Write-Line "" $outfile | |
} | |
} | |
$db.Close() | |
} | |
function Export-SQL($accfile, $outfile) { | |
$dbe = New-Object -ComObject DAO.DBEngine.120 | |
$db = $dbe.OpenDatabase($accfile) | |
$queries = $db.QueryDefs | |
foreach ($query in $queries) { | |
$name = $query.Name | |
if (!$name.StartsWith("~")) { | |
# クエリ名 | |
Write-Line ("[" + $name + "]") $outfile | |
# SQL | |
Write-Line $query.SQL $outfile | |
Write-Line "" $outfile | |
} | |
} | |
$db.Close() | |
} | |
# Get-ChildItem で Include を使う場合 | |
# Path の最後にアスタリスクをつける必要がある | |
$path = Join-Path $DirPath "*" | |
$name = $FileName + ".accdb" | |
# パターンにマッチする Access ファイルを順番に処理 | |
$files = Get-ChildItem -Path $path -File -Include $name | |
foreach ($file in $files) { | |
$accfile = $file.FullName | |
$outfile = $file.DirectoryName + "\" + $file.BaseName + "_" + $Type + ".txt" | |
Write-Host $accfile | |
if (Test-Path $outfile) { | |
Remove-Item -LiteralPath $outfile | |
} | |
switch ($Type) { | |
"Table" { Export-Table $accfile $outfile } | |
"Query" { Export-SQL $accfile $outfile } | |
"SQL" { Export-SQL $accfile $outfile } | |
"Relation" { Export-Relation $accfile $outfile } | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment