Results 1 to 4 of 4

Thread: Hiccups in exporting data to Excel from Notepad

  1. #1
    Neophyte chv288115's Avatar
    Join Date
    May 2019
    Posts
    1
    Articles
    0
    Excel Version
    Office 365

    Hiccups in exporting data to Excel from Notepad



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files
    Last edited by chv288115; 2019-05-17 at 03:42 PM. Reason: Field Corrections

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    155
    Articles
    0
    Excel Version
    2019
    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.
    Attached Files Attached Files

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,548
    Articles
    0
    Excel Version
    365
    …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.
    Attached Files Attached Files

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,548
    Articles
    0
    Excel Version
    365
    It's nearly 3 weeks now; how did you get on?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •