[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

formatting RACF Listuser output

Posted on 2010-03-24
6
Medium Priority
?
702 Views
Last Modified: 2013-11-09
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)


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

Open in new window

user-data-sample.txt
0
Comment
Question by:pastaguy
  • 3
4 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 28517896
@pastaguy

I usually associate RACF with mainframe operating systems.  How are you using RACF in a Windows environment?
0
 
LVL 46

Accepted Solution

by:
aikimark earned 1000 total points
ID: 28528435
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

Open in new window

0
 
LVL 26

Assisted Solution

by:arober11
arober11 earned 1000 total points
ID: 28575393
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.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 28578218
@arober11

That's a nice approach, but pastaguy would still need to align the columns.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Progress
Suggested Courses

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question