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)


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
pastaguyAsked:
Who is Participating?
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.

aikimarkCommented:
@pastaguy

I usually associate RACF with mainframe operating systems.  How are you using RACF in a Windows environment?
0
aikimarkCommented:
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

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
arober11Commented:
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
aikimarkCommented:
@arober11

That's a nice approach, but pastaguy would still need to align the columns.
0
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.