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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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
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
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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
SiddharthRoutCommented:
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

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
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
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
Microsoft Excel

From novice to tech pro — start learning today.