formatting RACF Listuser output
i requested a user list and within the output the user record is split across two or three rows and no common delimiter that i could use to pull the data into a spreadsheet/database to analyze.
here are three examples of user records as they appear in the output; based on the user having privileges or not, I'm getting 3 different record formats for users within the same output
USER=ASCHINT NAME=S-ASCHINT STC OWNER=DC CREATED=01.190
INSTALLATION-DATA=IBM 903361
USER=ATTDAEMN NAME=S-BROWN.MATTHEW OWNER=$MVSST CREATED=10.064
INSTALLATION-DATA=IBM 903361 897
UID= 0000000000
USER=BMUC061 NAME=B-RICHARDT.NICK OWNER=GP CREATED=00.003
ATTRIBUTES=OPERATIONS
INSTALLATION-DATA=IBM 8A8348 897
need a script to make the above look like (structured column and row data)
here are three examples of user records as they appear in the output; based on the user having privileges or not, I'm getting 3 different record formats for users within the same output
USER=ASCHINT NAME=S-ASCHINT STC OWNER=DC CREATED=01.190
INSTALLATION-DATA=IBM 903361
USER=ATTDAEMN NAME=S-BROWN.MATTHEW OWNER=$MVSST CREATED=10.064
INSTALLATION-DATA=IBM 903361 897
UID= 0000000000
USER=BMUC061 NAME=B-RICHARDT.NICK OWNER=GP CREATED=00.003
ATTRIBUTES=OPERATIONS
INSTALLATION-DATA=IBM 8A8348 897
need a script to make the above look like (structured column and row data)
user name owner created attributes installation-data UID
ASCHINT S-ASCHINT STC DC 01.190 IBM 903361
ATTDAEMN S-BROWN.MATTHEW $MVSST 10.064 IBM 903361 897 0000000000
BMUC061 B-RICHARDT.NICK GP 00.003 OPERATIONS IBM 8A8348 897
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Place this code in a workbook module or in the workbook itself.
Option Explicit
Public Sub ImportRACF()
Dim strInputFile As String
Dim strRACFdata As String
Dim intFN As Integer
Dim lngNameStart As Long
Dim lngValStart As Long
Dim lngLoop As Long
Dim lngNextCol As Long
Dim lngUserNum As Long
Dim strDataForExcel() As String
Dim strParsedData() As String
Dim strKeys(1 To 7) As String
Dim lngCols(1 To 7) As Long
strInputFile = Application.GetOpenFilename("Text Files (*.txt),(*.txt)", 1, "RACF Data Input File")
If Len(strInputFile) <> 0 Then
If Len(Dir(strInputFile)) = 0 Then
MsgBox "File not found. Please try again", vbCritical, "Selected File not found"
Exit Sub
End If
Else
MsgBox "Input selection cancelled. Please try again", vbCritical, "Selection cancelled"
Exit Sub
End If
'On Error Resume Next
intFN = FreeFile
Open strInputFile For Input As #intFN
strRACFdata = Input(LOF(intFN), #intFN)
Close intFN
strRACFdata = Replace(strRACFdata, vbCrLf, " ")
strParsedData = Split(strRACFdata, "USER=")
ReDim strDataForExcel(1 To UBound(strParsedData), 1 To 7)
strKeys(1) = "USER="
strKeys(2) = "NAME="
strKeys(3) = "OWNER="
strKeys(4) = "CREATED="
strKeys(5) = "ATTRIBUTES="
strKeys(6) = "INSTALLATION-DATA="
strKeys(7) = "UID="
For lngUserNum = 1 To UBound(strParsedData)
Erase lngCols
For lngLoop = 1 To 7
lngNameStart = InStr(strParsedData(lngUserNum), strKeys(lngLoop))
If lngNameStart <> 0 Then
lngCols(lngLoop) = lngNameStart
End If
Next
'store the value part of the name=value pairs
strDataForExcel(lngUserNum, 1) = Trim(Left$(strParsedData(lngUserNum), lngCols(2) - 1)) 'USER
For lngLoop = 2 To 6
If lngCols(lngLoop) <> 0 Then
If lngCols(lngLoop + 1) <> 0 Then
lngValStart = lngCols(lngLoop) + Len(strKeys(lngLoop))
strDataForExcel(lngUserNum, lngLoop) = Trim(Mid$(strParsedData(lngUserNum), lngValStart, lngCols(lngLoop + 1) - lngValStart - 1))
Else
lngNextCol = lngLoop + 2
Do Until (lngNextCol > UBound(lngCols))
If (lngCols(lngNextCol) <> 0) Then Exit Do
lngNextCol = lngNextCol + 1
Loop
If lngNextCol <= UBound(lngCols) Then
lngValStart = lngCols(lngLoop) + Len(strKeys(lngLoop))
strDataForExcel(lngUserNum, lngLoop) = Trim(Mid$(strParsedData(lngUserNum), lngValStart, lngCols(lngNextCol) - lngValStart - 1))
Else
lngValStart = lngCols(lngLoop) + Len(strKeys(lngLoop))
strDataForExcel(lngUserNum, lngLoop) = Trim(Mid$(strParsedData(lngUserNum), lngValStart))
End If
End If
End If
Next
lngLoop = 7 'UID is in the last position
If lngCols(7) <> 0 Then
lngValStart = lngCols(lngLoop) + Len(strKeys(lngLoop))
strDataForExcel(lngUserNum, lngLoop) = Trim(Mid$(strParsedData(lngUserNum), lngValStart))
End If
Next
Application.ScreenUpdating = False 'for best performance
'push parsed data to the active worksheet
ActiveSheet.Range(Cells(2, 1), Cells(UBound(strParsedData) + 1, 7)) = strDataForExcel
'push column headers to first row
'remove equal sign before using as column header
For lngLoop = 1 To 7
strKeys(lngLoop) = Left$(strKeys(lngLoop), Len(strKeys(lngLoop)) - 1)
Next
ActiveSheet.Range(Cells(1, 1), Cells(1, 7)) = strKeys
Application.ScreenUpdating = True
End Sub
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
Alternatively, using a real text Editor e.g. Notepad+, PFE, Word .... you could do the following.
1) Search for and replace all "\n" (line feeds) with nothing.
2) Search for "USER=" and replace with a "\n"
3) Search for "NAME=" and replace with a "\t"
4) Search for "OWNER=" and replace with a "\t"
5) Search for "CREATED=" and replace with a "\t"
...
x) Save the Tab delimited file
y) Import as a tab delimited file into Excel / Access....
If you need to do this regularly, just record the actions as a Macro, and play back next time you have a report in need of re-formatting.
1) Search for and replace all "\n" (line feeds) with nothing.
2) Search for "USER=" and replace with a "\n"
3) Search for "NAME=" and replace with a "\t"
4) Search for "OWNER=" and replace with a "\t"
5) Search for "CREATED=" and replace with a "\t"
...
x) Save the Tab delimited file
y) Import as a tab delimited file into Excel / Access....
If you need to do this regularly, just record the actions as a Macro, and play back next time you have a report in need of re-formatting.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Scripting Languages
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I usually associate RACF with mainframe operating systems. How are you using RACF in a Windows environment?