PowerShellでADO

PowerShellで

$Catalog = New-Object -ComObject ADOX.Catalog
$workCon = New-Object -ComObject ADODB.Connection
# $workRS = New-Object -ComObject ADODB.Recordset

### Set Provider
#$JETOLEDB4 = "Microsoft.Jet.OLEDB.4.0"
$ACEOLEDB12 = "Microsoft.ACE.OLEDB.12.0"
#$ACEOLEDB16 = "Microsoft.ACE.OLEDB.16.0"

### set table type
$Excel_120Xml = "Excel 12.0 Xml"
$Excel_80 = "Excel 8.0"
$AccessDB = ""
$IMEX1 = "IMEX=1"
$IMEX0 = "IMEX=0"

$CurrentTime = Get-Date -Format "yyyyMMdd_HHmmss"

########## work databaase setting
### Set work Database table
$myAddress = Split-Path $MyInvocation.MyCommand.Path -Parent
$workDBaddress = Split-Path $myAddress -Parent
# $workDBfile = "WorkDB_$dateTimeString.accdb"
$workDBfile = "WorkDB.accdb"
$workDBpath = "$workDBaddress\$workDBfile"
$workTableType = $AccessDB
$workTable = "work"

### set connection string for work database
$connectionString = "Provider=$ACEOLEDB12;Data Source=$workDBpath"

### create a work database file when it is nothing
if ((Test-Path $workDBpath) -eq $false){        # ワークデータベースが無い場合は作成する
   $workDB = $Catalog.Create($connectionString)
   $workDB.close()
} else {
   $Catalog.ActiveConnection = $connectionString
   foreach($table in $Catalog.Tables) {
      if (($table.type -eq "TABLE") -And ($table.Name -eq $workTable)) {
         $Catalog.Tables.Delete($workTable)
      }
   }
}

###  connection to work database
$workCon.Open($connectionString)
$errorCount = 0

########## source databaase setting
$sourceDBaddress = "$workDBaddress\01_Source\身長・体重"

# SourceData Site:
# https://www.e-stat.go.jp/stat-search/database?page=1&toukei=00400002&tstat=000001011648
# SourceData Excel File:
# https://www.e-stat.go.jp/stat-search/file-download?statInfId=000040120030&fileKind=0
$sourceDBfile = "r4_hoken_tokei_05.xlsx"
$sourceDBpath = "$sourceDBaddress\$sourceDBfile"
# $sourceTableType = "$Excel_120Xml;HDR=NO;$IMEX1"
$sourceTableType = "$Excel_120Xml;HDR=NO;"
$sourceTable = "05-"
$sourceRange = "`$A8:J"

########## destination databaase setting
$destinationDBaddress = $sourceDBaddress
$destinationDBfile = "result_$CurrentTime.xlsx"
$destinationDBpath = "$destinationDBaddress\$destinationDBfile"
$destinationTableType = $Excel_120Xml
$destinationTable = "result"
$destinationRange = "`$A:J"

### set select columns
$columns = @"
'Age歳' AS 年齢, F2 AS [区分], Val(F3) AS [男身長平均値（cm）], Val(F4) AS [男身長標準偏差（cm）], Val(F5) AS [男体重平均値（kg）], Val(F6) AS [男体重標準偏差（kg）], Val(F7) AS [女身長平均値（cm）], Val(F8) AS [女身長標準偏差（cm）], Val(F9) AS [女体重平均値（kg）], Val(F10) AS [女体重標準偏差（kg）]
"@
# $columns ="*"

### set extract condition
$ExtractCondition = "IsNumeric(F3)"
# $ExtractCondition = ""

##===================================================
function SQL_execute {
   param (
   [string] $SQLstr,
   [bool] $errorView=$True,
   [Bool] $exit_exception=$True
   )

   $SQLstr = $SQLstr.replace('\\','/')                            # Directoryの区切り文字を置換 '￥' -> '/'
   $SQLstr = $SQLstr.replace('@YYYYMMDD_HHMMSS@', $CurrentTime)   # SQL分に含まれる'@YYYYMMDD_HHMMSS@'を日時の数値に置換
   $SQLstr = $SQLstr + ';'

   $SQLstr
   try {
      $workCon.Execute($SQLstr)
      # time.sleep(0.1)
   } catch {
      $errorCount += 1
      if ($errorView) {
         'xxxxxxxxxxxxxxxxxxx ★★★ Execute Failure ! xxxxxxxxxxxxxxxxxxxxxxxxx'
         $_.Exception.Message
      }    
      if ($exit_exception) {
         'Error Count = ' + $errorCount
         return
      }
   } finally {
      'Execute Successful !! V(^_^)V'
   }
}

######################################################
### create execute SQL string
for ($i = 1; $i -lt 14; $i++) {
   $SubNo = $i.ToString("000")
   $SQLstring  = "SELECT " + $columns.replace("Age", $i + 4)
   if ($i -eq 1) {
      $SQLstring += " INTO [$destinationTableType;Database=$destinationDBpath].[$destinationTable]"
      $SQLstring += " FROM [$sourceTableType;Database=$sourceDBpath].[$sourceTable$SubNo$sourceRange]"
   } else {
      $SQLstring = "INSERT INTO [$destinationTableType;Database=$destinationDBpath].[$destinationTable] $SQLstring"
      $SQLstring += " FROM [$sourceTableType;Database=$sourceDBpath].[$sourceTable$SubNo$sourceRange]"
   }

   if ($ExtractCondition -ne "") {
      $SQLstring += " WHERE $ExtractCondition" 
   }
   SQL_execute($SQLstring)
}

$workCon.close()

'Error Count = ' + $errorCount
######################################################
### Excel でファイルを開く
$xlApp = New-Object -ComObject Excel.Application
$xlApp.Visible = $true
$xlApp.DisplayAlerts = $true
$Workbook = $xlApp.Workbooks.Open($destinationDBpath)
$wSht = $Workbook.Worksheets($destinationTable)
$wSht.Select()