Hiccups in exporting data to Excel from Notepad

chv288115

New member
Joined
May 17, 2019
Messages
1
Reaction score
0
Points
0
Excel Version(s)
Office 365
I have text in a notepad like below.
UserGroupId = 61e65be5-d086-4840-85ac-490cad5d6f6e
Name = Access Care Universal
Description = Assignment group for FDR 3972
EventAssignment = true
LdapAutoAssignment = false

UserGroupId = e61e1255-2d2a-43aa-b221-8182a76204dc
Name = Application Owners
Description = [Empty]
EventAssignment = true
LdapAutoAssignment = false

UserGroupId = ece84749-bb13-4214-83fd-7de4d9782b93
Name = ApplicationOwnersLDAP
Description = [Empty]
EventAssignment = true
LdapAutoAssignment = false

UserGroupId = 138b0cd2-0542-4205-9a6a-b0fea81e3ed6
Name = BBMSupport
Description = OTSS application group
EventAssignment = true
LdapAutoAssignment = false

UserGroupId = 43537523-4595-43fa-8eea-7e8bdacb34e3
Name = BELLCANMO
Description = OTSS application group
EventAssignment = true
LdapAutoAssignment = false

UserGroupId = 70865114-8d3d-4432-8ed0-0c4bc75843ba
Name = BlueprintsInfinite
Description = OTSS application group
EventAssignment = true
LdapAutoAssignment = false

I want the data to be converted into an Excel sheet like shown in the below image. [Above data and below image doesn't represent the same values of data]



For understanding, I have posted a data sample here, but I got a Few MB Text file with the same type of values and doing it manually will be a nightmare.

Does anyone have any ideas? If some one needed i have attached the original Notepad here.

FYI... I am using Office 365 Desktop Version.

What i have tried ?

I have tried exporting the data from Notepad and and used power query editor. Since, i am very less experienced with Power Query Editor i do have less luck.
 

Attachments

  • usergroup.txt
    7.3 KB · Views: 13
Last edited:
Using Power Query, I uploaded your text file and then applied the following Mcode

Code:
let
    Source = Csv.Document(File.Contents("C:\Users\alans\Desktop\usergroup.txt"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column2", "Column3", "Column4", "Column5"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Added Index" = Table.AddIndexColumn(#"Removed Blank Rows", "Index", 0, 1),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 5), type number),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Inserted Modulo", "Column1", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column1.1]), "Column1.1", "Column1.2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"FDR #2881", "FDR# 3471", "FDR #3091", "Requested in FDR #3487", "Bell IMC/CC Administrators", "Application Support assignment group"}),
    #"Filled Up" = Table.FillUp(#"Removed Columns1",{"Name               ", "Description        ", "EventAssignment    ", "LdapAutoAssignment "}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([#"UserGroupId        "] <> null)),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Modulo"})
in
    #"Removed Columns2"

Attached is the file it created. Hopefully, this is what you are looking for.
 

Attachments

  • Book8.xlsx
    20.6 KB · Views: 10
…and I did it with a macro. See attached with a button which runs the code below. It adds a new sheet to the workbook.
Code:
Sub blah()
Dim Headers()
ReDim Headers(0 To 0)
HdrCount = -1
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
  .AllowMultiSelect = False
  .Title = "Please select the file."
  .Filters.Clear
  .Filters.Add "Text", "*.txt"
  If .Show = True Then
    Set NewSht = Sheets.Add(After:=Sheets(Sheets.Count))
    Set Destn = NewSht.Range("A2")
    Filename = .SelectedItems(1)
    iFile = FreeFile
    Open Filename For Input As #iFile
    Do Until EOF(1)
      Line Input #1, strTextLine
      x = Split(strTextLine, vbLf)
      For i = LBound(x) To UBound(x)
        If InStr(x(i), "= ") > 0 Then
          y = Split(x(i), "= ")
          vv = Application.Match(Application.Trim(y(0)), Headers, 0)
          If IsError(vv) Then
            HdrCount = HdrCount + 1
            ReDim Preserve Headers(0 To HdrCount)
            Headers(HdrCount) = Application.Trim(y(0))
            vv = Application.Match(Application.Trim(y(0)), Headers, 0)
            NewSht.Cells(1, vv) = Application.Trim(y(0))
          End If
          Destn.Offset(, vv - 1) = y(1)
          LineWritten = True
        End If
      Next i
      If LineWritten Then
        Set Destn = Destn.Offset(1)
        LineWritten = False
      End If
    Loop
    Close #iFile
    NewSht.UsedRange.EntireColumn.AutoFit
  End If
End With
End Sub
It can be tweaked to handle multiple files if needed.
 

Attachments

  • ExcelGuru9990.xlsm
    23 KB · Views: 12
It's nearly 3 weeks now; how did you get on?
 
Back
Top