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()