Can this data be converted?

[mpleam]

HI

Can someone help with this problem? I have a data file (it's .txt) that I need to strip out the data into an Excel sheet so the data is in separate rows/columns. Here is a sample of the data:

//===================================================================================<
//
// Process Control - Codes start at 1000
//
    {
        I_PC_INITIALISING,              // Identifier
        1000,                           // Unique ID Code
        IGRP_PROCESS_CTRL,              // Incident group
        SOT_SYSTEM_CONTROLLER,          // Incident source
        ISOS_WHOLEMACHINE,              // Sort Scope
        IFOS_WHOLEMACHINE,              // Feed Scope
        ITOS_WHOLEMACHINE,              // Tracking Scope
        ICOS_WHOLEMACHINE,              // Calibration Scope
        NULL,                           // Pointer to polling function
        NULL,                           // Polling period (ms)
        SOT_NONE,                       // Polled board
        NULL,                           // Polling info
        STR_PC_INITIALISING,            // String ID
        IFLG_FAULT|IFLG_REPORT|IFLG_NOT_READY|IFLG_INITIALISING|IFLG_NOT_RUNNING, // Flags
        "Initialisation in progress"    // English Debug String
    },
    {
        I_PC_EXIT_APP,                  // Identifier
        1001,                           // Unique ID Code
        IGRP_PROCESS_CTRL,              // Incident group
        SOT_SYSTEM_CONTROLLER,          // Incident source
        ISOS_WHOLEMACHINE,              // Sort Scope
        IFOS_WHOLEMACHINE,              // Feed Scope
        ITOS_WHOLEMACHINE,              // Tracking Scope
        ICOS_WHOLEMACHINE,              // Calibration Scope
        NULL,                           // Pointer to polling function
        NULL,                           // Polling period
        SOT_NONE,                       // Polled board
        NULL,                           // Polling info
        STR_PC_EXIT_APP,                // String ID
        IFLG_REPORT|IFLG_NOT_READY|IFLG_NOT_RUNNING,     // Flags
        "Application exit"              // English Debug String
    },
    {
        I_PC_GUI_SORT_DIS,              // Identifier
        1002,                           // Unique ID Code
        IGRP_PROCESS_CTRL,              // Incident group
        SOT_SYSTEM_CONTROLLER,          // Incident source
        ISOS_WHOLEMACHINE,              // Sort Scope
        IFOS_NONE,                      // Feed Scope
        ITOS_NONE,                      // Tracking Scope
        ICOS_NONE,                      // Calibration Scope
        NULL,                           // Pointer to polling function
        NULL,                           // Polling period
        SOT_NONE,                       // Polled board
        NULL,                           // Polling info
        STR_PC_GUI_SORT_DIS,            // String ID
        IFLG_REPORT|IFLG_NOT_RUNNING,   // Flags
        "Sort disabled by GUI"          // English Debug String
    },

Anything that is delimited with '//' is a comment and can be ignored. Is there a way to import or convert this data so that it is nicely delimited for use? Something like in the image (I can't use the addin to upload a minisheet). I'm open to any method that will allow me to automate this process, as there are hundreds of entries in the text file.

Hope someone can help

Mark

 

[igold]

Hi, welcome to the MrExcel Forum.
Does this get you close to what you need. I am not sure if the Header Row is correct, as I could not read the entire column in your picture. The code will first ask you to choose the text file that you want to convert.

Sub TextFlie()
    
    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    Dim rn As Long, cn As Long, i As Long, l As Long, ln As Long
    Dim LineFromFile As Variant, hdr As Variant
    Dim FilePath As String

    'Choose a text file to open
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Show
        FilePath = .SelectedItems(1)
    End With
    
    hdr = Array("Identifier", "ID", "Group", "Source", "Sort scope", "Feed scope", "Tracking scope", "Calib scope", "Pointer", "Poll", "Poll board", "Poll info", "String", "Flags", "Debug")
    Range("A1:O1") = hdr
    
    rn = 2: cn = 1
    Close #1
    Open FilePath For Input As #1
    
    'Loop to count the number of lines in the text file.
    Do Until EOF(1)
        Line Input #1, LineFromFile
        LineFromFile = Trim(LineFromFile)
        i = InStr(LineFromFile, ",")
        If Not i = 0 Then
            Cells(rn, cn) = Left(LineFromFile, i - 1)
            cn = cn + 1
        End If
        l = InStr(2, LineFromFile, Chr(34))
        If Not l = 0 Then
            Cells(rn, cn) = Mid(LineFromFile, 2, l - 2)
            cn = cn + 1
        End If
        If Not InStr(LineFromFile, "}") = 0 Then
            rn = rn + 1
            cn = 1
        End If
    Loop
    Close #1
    Range("P:P").ClearContents
    
End Sub

 

[mpleam]

Wow - perfect! Thank you so much

 

[igold]

You're welcome. I was happy to help. Thanks for the feedback!