フォルダ内のSQLを読んで実行結果をTAB区切りファイルに出力する

VBScript

Option Explicit

Private fs
Private conn

'結果出力
Private Sub writeResult(rs, sqlFile)
    '上書きか、追加書き込みか
    Dim mode: mode = "2" '2:ForWriting
    If WScript.Arguments.Count > 2 Then
        If WScript.Arguments(2) = "1" Then
            mode = "8" '8:ForAppend
        End If
    End If

    '出力フォルダ
    Dim outFolder: outFolder = WScript.Arguments(1)
    If Right(outFolder, 1) <> "\" Then
        outFolder = outFolder & "\"
    End If

    '出力ファイル
    Dim tsv: Set tsv = fs.OpenTextFile(outFolder & Replace(UCase(sqlFile.Name), ".SQL", ".xls"), mode, True)

    '項目名 出力
    Dim iCol
    If mode = "2" Then '2:ForWriting
        For iCol = 1 To rs.Fields.Count
            tsv.Write rs.Fields(iCol - 1).Name & vbTab
        Next
    End If
    tsv.WriteLine

    '値 出力
    Do Until rs.EOF
        For iCol = 1 To rs.Fields.Count
            tsv.Write rs.Fields(iCol - 1).Value & vbTab
        Next
        tsv.WriteLine

        rs.MoveNext
    Loop

    tsv.Close
    Set tsv = Nothing
End Sub

'SQL 読み込み
Private Function getSql(sqlFile)
'   WScript.Echo sqlFile.Path
    WScript.Echo sqlFile.Name

    Dim tsSql:   Set tsSql = fs.OpenTextFile(sqlFile.Path)
    Dim sqlText: sqlText   = tsSql.ReadAll
'   WScript.Echo sqlText
    tsSql.Close
    Set tsSql = Nothing

    getSql = sqlText
End Function

'SQL 実行
Private Function openRecordSet(sqlText)
    Dim rs: Set rs = CreateObject("ADODB.Recordset")
    With rs
        .ActiveConnection = conn
        .CursorType       = 0 'adOpenForwardOnly
        .LockType         = 1 'adLockReadOnly
        .Source           = sqlText
        .Open
    End With

    Set openRecordSet = rs
End Function

'ＤＢ接続
Private Sub openDB()
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "aaa","bbb","ccc"
End Sub

'ＤＢ切断
Private Sub closeDB()
    conn.Close
    Set conn = Nothing
End Sub

'主処理
Private Sub Main()
    Set fs = CreateObject("Scripting.FileSystemObject")

    'ＤＢ接続
    openDB

    '指定フォルダ内の全ＳＱＬファイルに対して処理を繰り返す
    Dim sqlFolder: Set sqlFolder = fs.GetFolder(WScript.Arguments(0))
    Dim sqlFile
    For Each sqlFile In sqlFolder.Files
        'SQL 読み込み
        Dim sqlText: sqlText = getSql(sqlFile)
        'SQL 実行
        Dim rs: Set rs = openRecordSet(sqlText)
        '結果出力
        Call writeResult(rs, sqlFile)
        rs.Close
        Set rs = Nothing
    Next
    Set sqlFile   = Nothing
    Set sqlFolder = Nothing

    'ＤＢ切断
    closeDB

    Set fs = Nothing
End Sub

'主処理 呼び出し
Call Main()

'実行形式
'cscript //nologo OracleToTsv.vbs "c:\sql_folder" "c:\result\"
'cscript //nologo OracleToTsv.vbs "c:\sql_folder" "c:\result\" 1