excel関数で抽出

 

//DD1 DD DSN=NAME1,DISP=SHR

=MID(B16,FIND("DSN=",B16,1)+4,IF(1=1,FIND(",",B16,FIND("DSN=",B16,1)+4)-FIND("DSN=",B16,1)-4,FIND("",B16,FIND("DSN=",B16,1)+4)))

//DD1 DD DISP=SHR,DSN=NAME1

=MID(B17,FIND("DSN=",B17,1)+4,IF(1=0,FIND(",",B17,FIND("DSN=",B17,1)+4)-FIND("DSN=",B17,1)-4,FIND("",B17,FIND("DSN=",B17,1)+4)))

 @echo off

rem  入力

rem  [JCL] -- Exx20.txt

rem  [CMP] -- Exx20.txt

rem  [PACK]

rem  ・実行：PACK2.bat

rem  出力

rem     [Exx20_x回目(yymmdd)]

rem        -- Exx20_COPY_JCL.txt 

rem        -- [本番データ取得_エビデンス]

rem              Exx20_COPYSTEP新旧比較エビデンス.txt

rem 遅延環境変数を設定

setlocal ENABLEDELAYEDEXPANSION

 SET EJOB=

 rem ラベル「:loop」

:loop

rem ユーザーからの入力を変数「text」に代入する

set /p text="yymmddの値を入力してください…："

rem 「exit」と入力されたとき、バッチファイルを終了する

if "%text%" == "exit" goto :end

rem ラベル「:loop」に戻る

rem goto :loop

if "%text%" == "" Set text=yymmdd

echo %text%

pause

rem --------------------------------------

rem 一覧取得

rem --------------------------------------

 DIR /b ..\JCL\*.txt>list.txt

rem --------------------------------------

rem 実行

rem --------------------------------------

 set /a n=0

 FOR /F %%a IN (list.txt) DO (

   rem echo %%a

   SET EJOB=%%a

   rem SET EJOB=!EJOB:~,5!

   SET /a n=n+1

   SET EJOB=!EJOB:.txt=!

   echo !n! ###!EJOB!###

   call:Pack2 !EJOB! !text!

 )

 echo :

 echo === !n! 件、処理しました。===

 pause

:end

 exit /b

rem --------------------------------------

rem パッキングする

rem --------------------------------------

:Pack2

   SET TGT_FD=!EJOB!

   ECHO ■!TGT_FD!■

   IF EXIST !TGT_FD! RD !TGT_FD! /S /Q

   MD !TGT_FD!

   IF EXIST ..\JCL\!EJOB!.txt (

      COPY ..\JCL\!EJOB!.txt !TGT_FD!\!EJOB!_COPY_JCL.txt 

   ) ELSE (

     echo ..\JCL\!EJOB!.txtは存在しません。

   )

   IF EXIST ..\CMP\!EJOB!.txt (

      COPY ..\CMP\!EJOB!.txt !TGT_FD!\!EJOB!_COPYSTEP新旧比較エビデンス.txt

   ) ELSE (

     echo ..\CMP\!EJOB!.txtは存在しません。

   )

   EXIT /b

endlocal

 @echo off

rem  入力

rem  [JCL] -- Exx20.txt

rem  [CMP] -- Exx20.txt

rem  [PACK]

rem  ・実行：PACK.bat

rem  出力

rem     [Exx20_x回目(yymmdd)]

rem        -- Exx20_COPY_JCL.txt 

rem        -- [検証_エビデンス]

rem        -- [本番データ取得_エビデンス]

rem              Exx20_COPYSTEP新旧比較エビデンス.txt

rem              ログ_実行LOG.txt

rem 遅延環境変数を設定

setlocal ENABLEDELAYEDEXPANSION

 SET EJOB=

 rem ラベル「:loop」

:loop

rem ユーザーからの入力を変数「text」に代入する

set /p text="yymmddの値を入力してください…："

rem 「exit」と入力されたとき、バッチファイルを終了する

if "%text%" == "exit" goto :end

rem ラベル「:loop」に戻る

rem goto :loop

if "%text%" == "" Set text=yymmdd

echo %text%

pause

rem --------------------------------------

rem 一覧取得

rem --------------------------------------

 DIR /b ..\JCL\*.txt>list.txt

rem --------------------------------------

rem 実行

rem --------------------------------------

 set /a n=0

 FOR /F %%a IN (list.txt) DO (

   rem echo %%a

   SET EJOB=%%a

   rem SET EJOB=!EJOB:~,5!

   

   SET /a n=n+1

   SET EJOB=!EJOB:.txt=!

   echo !n! ###!EJOB!###

   call:Pack1 !EJOB! !text!

 )

 echo :

 echo === !n! 件、処理しました。===

 pause

:end

 exit /b

rem --------------------------------------

rem パッキングする

rem --------------------------------------

:Pack1

   SET TGT_FD=!EJOB!_x回目(!text!)

   SET TGT_HON_FD=!TGT_FD!\本番データ取得_エビデンス

   SET TGT_KEN_FD=!TGT_FD!\検証_エビデンス

   ECHO ■!TGT_FD!■

   IF EXIST !TGT_FD! RD !TGT_FD! /S /Q

   MD !TGT_FD!

   MD !TGT_HON_FD!

   MD !TGT_KEN_FD!

   

   IF EXIST ..\JCL\!EJOB!.txt (

      COPY ..\JCL\!EJOB!.txt !TGT_FD!\!EJOB!_COPY_JCL.txt   

   ) ELSE (

     echo ..\JCL\!EJOB!.txtは存在しません。

   )

   TYPE nul >  !TGT_HON_FD!\ログ_実行LOG.txt

   IF EXIST ..\CMP\!EJOB!.txt (

      COPY ..\CMP\!EJOB!.txt !TGT_HON_FD!\!EJOB!_COPYSTEP新旧比較エビデンス.txt

   ) ELSE (

     echo ..\CMP\!EJOB!.txtは存在しません。

   )

   EXIT /b

endlocal

 DOS COPY

 [JCL]
    XXXX.txt
    YYYY.txt
 
 [CMP]
 
 
 [TRANS]
 
  [XXXX_hhmmdd]
 
   -- XXXX_JCL.TXT
   -- [結果]
          -- XXXX_比較.txt
          -- ログ_実行.txt
   -- [検証]
   
 
  [YYYY_hhmmdd]
 
   -- YYYY_JCL.TXT
   -- [結果]
          -- YYYY_比較.txt
          -- ログ_実行.txt
   -- [検証]
   
 

VＢＳ VＴＯＣ データセット ＶＯＬ＆サイズ出力

 

#########################

入力　0_VTOC_DS一覧.txt

#########################

 
          TEST.AA.J1.S1.TEST1      VOL01
          TEST.AA.J2.S2.TEST2A     VOL02 +
          TEST.AA.J3.S3.TEST3AAA   VOL003
          TEST.AA.J4.S4.TEST4      VOL004+


          TEST.AA.J1.S1.TEST1         1   2   3
          TEST.AA.J2.S2.TEST2A       22 200   0
          TEST.AA.J3.S3.TEST3AAA    333  30  33
          TEST.AA.J4.S4.TEST4      4444  44  44

##########################

バッチ　0_VTOC_DS一覧.bat

##########################

setlocal enabledelayedexpansion

@echo off

cls

echo ---------------------------------------------

echo  VTOC データセット一覧(VOLとサイズ)を出力

echo ---------------------------------------------

echo %1

echo %~0

echo;

SET /P ANSWER="実行します。よろしいですか (y/n)？"

IF /i {%ANSWER%}=={y} (goto :yes)

IF /i {%ANSWER%}=={yes} (goto :yes)

EXIT

:yes

  IF EXIST %1_結果 DEL %1_結果

  cscript /nologo bin\1_VTOC_DS一覧分割.vbs %1

   IF %errorlevel% neq 0 (

      echo 異常終了 %errorlevel%

      exit /b 8

    )

   rem pause

   cscript /nologo bin\2_VTOC_DS一覧併合.vbs %1

   IF %errorlevel% neq 0 (

      echo 異常終了 %errorlevel%

      exit /b 8

    )

    echo 正常終了 %errorlevel%

　pause

    exit /b 0

endlocal

 

########################

結果_VTOC_DS一覧分割SIZ.csv

########################

TEST.AA.J1.S1.TEST1,1,2,3

TEST.AA.J2.S2.TEST2A,22,200,0

TEST.AA.J3.S3.TEST3AAA,333,30,33

TEST.AA.J4.S4.TEST4,4444,44,44

########################

結果_VTOC_DS一覧分割VOL.csv

########################

TEST.AA.J1.S1.TEST1,VOL01
TEST.AA.J2.S2.TEST2A,VOL02,+
TEST.AA.J3.S3.TEST3AAA,VOL003
TEST.AA.J4.S4.TEST4,VOL004,+

########################

0_VTOC_DS一覧.txt_結果.csv

########################

VOL01,,TEST.AA.J1.S1.TEST1,1,2,3
VOL02,+,TEST.AA.J2.S2.TEST2A,22,200,0
VOL003,,TEST.AA.J3.S3.TEST3AAA,333,30,33
VOL004,+,TEST.AA.J4.S4.TEST4,4444,44,44

 

########################

1_VTOC_DS一覧分割.vbs

########################

Option Explicit

Dim fso

Dim iFile1

Dim oFile1

Dim oFile2

'----------------------------------------

Dim Args

Dim sArg1

Dim sLine

Dim sOutLine

Dim OUTFNAME1

Dim OUTFNAME2

Dim LINEKEY

Dim lPosStr

'----------------------------------------

Set Args = WScript.Arguments

If Args.Count < 1 Then

  WScript.Echo "ドラッグ&ドロップしてを実行。"

  WScript.Quit(1)

End If

'WScript.Echo Args.Count

sArg1 =  Wscript.Arguments(0)

'OUTFNAME1 = sArg1 & "_結果VOL" & ".csv"

'OUTFNAME2 = sArg1 & "_結果SIZ" & ".csv"

'----------------------------------------

Set fso = WScript.CreateObject("Scripting.FileSystemObject")

OUTFNAME1 = fso.getParentFolderName(WScript.ScriptFullName) & "\" & "結果_VTOC_DS一覧分割VOL" & ".csv"

OUTFNAME2 = fso.getParentFolderName(WScript.ScriptFullName) & "\" & "結果_VTOC_DS一覧分割SIZ" & ".csv"

Set iFile1 = fso.OpenTextFile(sArg1, 1, False, 0)

Set oFile1 = fso.OpenTextFile(OUTFNAME1, 2, True)

Set oFile2 = fso.OpenTextFile(OUTFNAME2, 2, True)

LINEKEY = "TEST."

'----------------------------------------

Do Until iFile1.AtEndOfStream

  sLine = iFile1.ReadLine

' ---------------------------------------

  lPosStr = InStr(1, sLine, LINEKEY) 

  If lPosStr > 0 Then

    WScript.Echo sLine

    If mid(sLine,40,1) <> " " Then

'     ------------------------------------

'     VOLUME 情報の時

'     ------------------------------------

      sLine = Replace(sLine, "+", " +")

      sOutLine = OmmitSpaceAndCSV(trim(sLine))

      oFile1.WriteLine sOutLine

    Else

'     ------------------------------------

'     サイズ 情報の時

'     ------------------------------------

      sOutLine = OmmitSpaceAndCSV(trim(sLine))

      oFile2.WriteLine sOutLine

    End If

  End If

Loop

'----------------------------------------

iFile1.Close

oFile1.Close

oFile2.Close

WScript.Quit(0)    ' 終了する

'-------------------------------------------------

' 文字列操作：空白を詰めてカンマ区切りに編集する -

'-------------------------------------------------

Function OmmitSpaceAndCSV(p1)

      Dim a

      ' ２文字以上の空白を空白１文字にする

      While InStr(p1, "  ")

         p1 = Replace(p1, "  ", " ")

      Wend

     ' 空白区切りで配列保存

      a=Split(p1," ")

      OmmitSpaceAndCSV = Join(a, ",")

End Function

########################

2_VTOC_DS一覧併合.vbs

########################

Option Explicit

Dim fso

Dim iFile1

Dim iFile2

Dim oFile1

'----------------------------------------

Dim Args

Dim sArg1

Dim line1

Dim line2

Dim sOutLine

Dim INAME1

Dim INAME2

Dim sOName1

Dim i

Dim ilineNum

Dim aline1

Dim aline2

'----------------------------------------

Set Args = WScript.Arguments

If Args.Count < 1 Then

  WScript.Echo "ドラッグ&ドロップしてを実行。"

  WScript.Quit(1)

End If

'WScript.Echo Args.Count

sArg1 =  Wscript.Arguments(0)

sOName1 = sArg1 & "_結果.csv"

'----------------------------------------

Set fso = WScript.CreateObject("Scripting.FileSystemObject")

INAME1 = fso.getParentFolderName(WScript.ScriptFullName) & "\" & "結果_VTOC_DS一覧分割VOL" & ".csv"

INAME2 = fso.getParentFolderName(WScript.ScriptFullName) & "\" & "結果_VTOC_DS一覧分割SIZ" & ".csv"

Set iFile1 = fso.OpenTextFile(INAME1)

Set iFile2 = fso.OpenTextFile(INAME2)

Set oFile1 = fso.OpenTextFile(sOName1, 2, True)

'----------------------------------------

While iFile1.AtEndOfLine = False

    ilineNum = iFile1.Line

    line1 = iFile1.readLine

    line2 = iFile2.readLine

    aline1 = split(line1, ",")

    aline2 = split(line2, ",")

    'WScript.Echo ilineNum & ",[VOL ]," & line1

    'WScript.Echo ilineNum & ",[SIZE]," & line2

    sOutLine = ""

    If aline1(0) = aline2(0) Then

      'ボリューム名

      sOutLine = sOutLine & aline1(1) & ","

      

      'マルチボリューム識別

      If UBound(aline1) >= 2 Then

        sOutLine = sOutLine & aline1(2) & ","

      Else

        sOutLine = sOutLine & "" & ","

      End if

      'データセット名

      sOutLine = sOutLine & aline1(0)

      'サイズ属性の情報

      For i=1 to UBound(aline2)

        sOutLine = sOutLine & "," & aline2(i)

      Next

      WScript.Echo sOutLine

      oFile1.WriteLine sOutLine

    Else

      WScript.Quit(8)

    End If

Wend

'----------------------------------------

iFile1.close

iFile2.close

oFile1.close

WScript.Quit(0)    ' 終了する

VBS IO2 ファイルを読んで、文字列編集して出力する

 

*************************************

 入力データ log_kensu.txt

*****************************

 --------------------------------
1ICE805I 0 JOBNAME: AULOO   , STEPNAME: DUMMYC01
--------------------------------
--------------------------------
 ICE045I 0 RECORDS - IN: 12346, OUT: 56789
--------------------------------
--------------------------------
1ICE805I 0 JOBNAME: AULOO   , STEPNAME: DUMMYC02
--------------------------------
--------------------------------
 ICE045I 0 RECORDS - IN: 6, OUT: 9

 

*******************************

バッチ log_kensu.bat

*******************************

setlocal enabledelayedexpansion

@echo off
cls

echo %1
echo %~0
echo ---------------------------------------------
echo  ログファイルから件数箇所を出力します
echo ---------------------------------------------
echo;

SET /P ANSWER="実行します。よろしいですか (Y/N)？"

if /i {%ANSWER%}=={y} (goto :yes)
if /i {%ANSWER%}=={yes} (goto :yes)
EXIT

:yes
  IF EXIST %1_結果 DEL %1_結果

   cscript /nologo bin\log_kensu.vbs %1

endlocal

 

**************************************
log_kensu.vbs

**************************************

Option Explicit

Dim Args

Dim Cmd
Dim sName
Dim sArg1
Dim lPosStr
Dim iSW
Dim sLine

Dim sEditLine
Dim aryStrings

'--
Dim sJobName
Dim sJobNameT,sJobNameV
Dim sStepName
Dim sStepNameT,sStepNameV
'--
Dim sINName
Dim sINNameT,sINNameV
Dim sOUTName
Dim sOUTNameT,sOUTNameV

Dim fso
Dim iFile
Dim oFile

'----------------------------------------
Set Args = WScript.Arguments
If Args.Count < 1 Then
  WScript.Echo "ドラッグ&ドロップしてを実行。"
  WScript.Quit
End If

WScript.Echo Args.Count
sArg1 =  Wscript.Arguments(0)
WScript.Echo sArg1

Dim OUTFNAME
OUTFNAME = sArg1 & "_結果" & ".csv"

'----------------------------------------
Set fso = WScript.CreateObject("Scripting.FileSystemObject")
Set iFile = fso.OpenTextFile(sArg1, 1, False, 0)
Set oFile = fso.OpenTextFile(OUTFNAME, 2, True)

iSW = 0

Do Until iFile.AtEndOfStream

  sLine = iFile.ReadLine

' ---------------------------------------
  lPosStr = InStr(1, sLine, "1ICE805I 0 JOBNAME:")
  If lPosStr > 0 Then

      sEditLine  =  trim(Mid(sLine, len("1ICE805I 0 ") ))

      aryStrings = Split(sEditLine, ",")
      sJobName   = trim(aryStrings(0))
      sStepName  = trim(aryStrings(1))

      aryStrings = Split(sJobName, ":")
      sJobNameT  = trim(aryStrings(0))
      sJobNameV  = trim(aryStrings(1))

      aryStrings = Split(sStepName, ":")
      sStepNameT = trim(aryStrings(0))
      sStepNameV = trim(aryStrings(1))

     iSW = 0

  End If

' ---------------------------------------
  lPosStr = InStr(1, sLine, "RECORDS - IN:")
  If lPosStr > 0 Then

     sEditLine  =  trim(Mid(sLine, len(" ICE045I 0 RECORDS - ") ))
     aryStrings = Split(sEditLine, ",")

     aryStrings = Split(sEditLine, ",")
     sINName    = trim(aryStrings(0))
     sOUTName   = trim(aryStrings(1))

     aryStrings = Split(sINName, ":")
     sINNameT   = trim(aryStrings(0))
     sINNameV   = trim(aryStrings(1))

     aryStrings = Split(sOUTName, ":")
     sOUTNameT  = trim(aryStrings(0))
     sOUTNameV  = trim(aryStrings(1))

     oFile.WriteLine sJobNameT  & "," & sJobNameV & "," & _
      sStepNameT & "," & sStepNameV & "," & _
      sINNameT  & "," & sINNameV & "," & _
      sOUTNameT & "," & sOUTNameV

     iSW = 1

  End If

Loop

'
iFile.Close
oFile.Close

 

******************************

出力データ log_kensu.txt_結果.csv

******************************

 JOBNAME,AULOO,STEPNAME,DUMMYC01,IN,12346,OUT,56789
JOBNAME,AULOO,STEPNAME,DUMMYC02,IN,6,OUT,9

 

 

 

 

VBA20181112

Sub sss()

Dim rng As Range

Set rng = Range("B1")
'rng.Select


Dim i As Integer
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
i = 20
Set rng = Union(rng, Range("A" & i - 1 & ":C" & i + 1))

rng.BorderAround LineStyle:=xlContinuous
rng.Interior.Color = &HC0C0C0

Set rng = Nothing

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Set rng = Range("B1")
Set rng = Union(rng, Range("D" & i - 1 & ":G" & i + 1))

rng.BorderAround LineStyle:=xlContinuous
rng.Interior.Color = vbWhite

Set rng = Nothing


'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::


'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
i = 23
Set rng = Range("B1")
Set rng = Union(rng, Range("A" & i - 1 & ":C" & i + 1))

rng.BorderAround LineStyle:=xlContinuous
rng.Interior.Color = &HC0C0C0

Set rng = Nothing

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Set rng = Range("B1")
Set rng = Union(rng, Range("D" & i - 1 & ":G" & i + 1))

rng.BorderAround LineStyle:=xlContinuous
rng.Interior.Color = vbWhite

Set rng = Nothing


'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
i = 29
Set rng = Range("A" & i - 1 & ":C" & i + 1)

rng.BorderAround LineStyle:=xlContinuous
rng.Interior.Color = &HC0C0C0

Set rng = Nothing

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Set rng = Range("D" & i - 1 & ":G" & i + 1)

rng.BorderAround LineStyle:=xlContinuous
rng.Interior.Color = vbWhite

Set rng = Nothing


'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
i = 32
Set rng = Range("A" & i - 1 & ":C" & i + 1)

With rng
.BorderAround LineStyle:=xlContinuous
.Borders(xlEdgeTop).LineStyle = xlLineStyleNone
.Interior.Color = &HC0C0C0
End With

Set rng = Nothing

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Set rng = Range("D" & i - 1 & ":G" & i + 1)

With rng
.BorderAround LineStyle:=xlContinuous
.Borders(xlEdgeTop).LineStyle = xlDash
.Interior.Color = vbWhite
End With

Set rng = Nothing


'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::


Set rng = Range("B1")
'rng.Select

Set rng = Union(rng, Range("E2:F3"))
rng.Select
rng.BorderAround LineStyle:=xlContinuous

rng.Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
'rng.Borders.LineStyle = xlLineStyleNone

rng.Borders(xlEdgeBottom).LineStyle = xlDash

'背景色
rng.Interior.Color = vbRed





'Application.Union.Borders.



End Sub

Sub rrr()

Dim rng As Range

Set rng = Cells(1, 1)
'rng.Select



Set rng = Union(rng, Cells(3, 4))
rng.Select

'rng.Borders.Color = vbRed
'rng.Borders.LineStyle
rng.Borders(xlEdgeTop).LineStyle = xlContinuous
rng.Borders(xlEdgeLeft).LineStyle = xlContinuous
rng.Borders(xlEdgeRight).LineStyle = xlContinuous
rng.Borders(xlEdgeBottom).LineStyle = xlContinuous

Set rng = Nothing


Set rng = Cells(7, 7)
'rng.Select

Set rng = Union(rng, Cells(10, 12))
rng.Select
rng.BorderAround LineStyle:=xlContinuous

rng.Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
'rng.Borders.LineStyle = xlLineStyleNone

rng.Borders(xlEdgeBottom).LineStyle = xlDash

'背景色
rng.Interior.Color = vbBlue





'Application.Union.Borders.

End Sub

Sub qqq()

Dim rng As Range

Set rng = Range("B1")
'rng.Select



Set rng = Union(rng, Range("A2:C3"))
rng.Select

'rng.Borders.Color = vbRed
'rng.Borders.LineStyle
rng.Borders(xlEdgeTop).LineStyle = xlContinuous
rng.Borders(xlEdgeLeft).LineStyle = xlContinuous
rng.Borders(xlEdgeRight).LineStyle = xlContinuous
rng.Borders(xlEdgeBottom).LineStyle = xlContinuous

Set rng = Nothing


Set rng = Range("B1")
'rng.Select

Set rng = Union(rng, Range("E2:F3"))
rng.Select
rng.BorderAround LineStyle:=xlContinuous

rng.Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
'rng.Borders.LineStyle = xlLineStyleNone

rng.Borders(xlEdgeBottom).LineStyle = xlDash

'背景色
rng.Interior.Color = vbRed





'Application.Union.Borders.

End Sub