Format /etc/password file fields into rows/columns in excel

Hi everyone,

I have an /etc/password file with the following template below. Is there an easy way I can put them into rows and columns? ie:

default | field1 | field 2 | field 3
deafult1 | result1 | result2 | empty

the problem im having is not all field names is included for each separate row so it's difficult to format... any ideas?
default:
	field1 = result1
	field2 = result2
default2:
	field1 = result1
	field2 = result2
	field2 = result2
	field2 = result2
	field2 = result2
default3:
	field1 = result1
	field2 = result2
	field2 = result2
default4:
	field1 = result1

Open in new window

dsrnuAsked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
Ok Try this.

Run the macro Sample from Module 1. Also Place the text file in C:

Sid

Attachments
Excel File
Sample Password File
Snapshot of End Result

Code used

Option Explicit

Sub Sample()
    Const PassPath As String = "C:\Password.Txt"
    
    Dim MyData As String, strData() As String
    Dim ws As Worksheet
    Dim temp As String, temp1 As String, MyArray() As String, Def As String
    Dim i As Long, j As Long, ColNo As Long, LwRow As Long, LastRow As Long
    
    '~~> Open the Text File
    Open PassPath For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    
    strData() = Split(MyData, vbCrLf)
    
    '~~> Set Active Sheet
    Set ws = Sheets("Sheet1")
    
    With ws
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
        i = 0
        Do While i < UBound(strData()) + 1
            If UCase(Left(strData(i), 7)) = "DEFAULT" Then
                Def = Replace(strData(i), ":", "")
                i = i + 1
                Do While UCase(Left(strData(i), 7)) <> "DEFAULT"
                    .Cells(LastRow, 1) = Def
                    If InStr(strData(i), "field") Then
                        MyArray = Split(strData(i), "=")
                        temp = Replace(MyArray(0), "field", "")
                        temp1 = ""
                        For j = 1 To Len(temp)
                            If Asc(Mid(temp, j, 1)) > 47 And Asc(Mid(temp, j, 1)) < 58 Then _
                            temp1 = temp1 & Mid(temp, j, 1)
                        Next j
                        
                        ColNo = Val(Trim(temp1)) + 1
                        LwRow = .Range(Split(.Cells(, ColNo).Address, "$")(1) & Rows.Count).End(xlUp).Row + 1

                        If LwRow > LastRow Then LastRow = LwRow
                        
                        .Cells(LastRow, ColNo) = MyArray(0)
                    End If
                    i = i + 1
                    If i > UBound(strData()) Then Exit Sub
                Loop
                LastRow = LastRow + 1
            Else
                i = i + 1
            End If
        Loop
    End With
End Sub

Open in new window

Password.xls
password.txt
Untitled.jpg
0
 
ittogoCommented:
Just import and use the | as the separator.  As long as the | character is there, it should just leave an empty cell
0
 
SiddharthRoutCommented:
dsrnu: What happens when there are duplicate entries like

        field2 = result2
        field2 = result2

Also is the above text stored in a text file?

Sid
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
dsrnuAuthor Commented:
yes its stored in text file. even if there are duplicate entries, i still want to put it in a row/column
0
 
ittogoCommented:
A straight import as text into excel should still work
0
 
SiddharthRoutCommented:
Is this the result that you are seeking?

Sid
Untitled.jpg
0
 
dsrnuAuthor Commented:
yes siddharthroul! any easy way i can do that?
0
 
SiddharthRoutCommented:
I am on to it :)

Will paste the code shortly :)

Sid
0
 
SiddharthRoutCommented:
Quick question.

What is the maximum number of fields are there for example field1, field2, field3 etc?

Sid
0
 
dsrnuAuthor Commented:
15
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.