Macro to extract text from .txt file into Excel

S

striker359

Board Regular
Joined
Jun 19, 2014
Messages
64
Hi,

I'm completely new to macro building but here goes:

I would like to extract info from an external txt file into Excel. For example:

txt file:

/* ----------------- Dummy ------------------- */
JobName: Dummy
send_notifications: y
max_runs: 5


/* ----------------- TAF-O-FF401D ------------------- */
JobName: TAF-O-FF401D
send_notifications: y
max_runs: 5

I would require a way to read the item category (JobName, send_notifcations, max_runs), for lack of a better term, and then to populate the corresponding info into specific columns in Excel.

For example:

JobName (Dummy) in Column A
send_notifications (y) in Column B
max_runs (5) in Column C

With the /* ----------------- JobName ------------------- */ indicating a new job entry, I would require the next job entry to be on the next row and so on till there are no more job entries left.

Thanks in advance!
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Try this with the file path and name changed to suit.
Code: 
Sub Extract_Text()

    Dim r As Long
    Dim textLine As String
    
    Range("A1:C1").Value = Array("JobName", "send_notifications", "max_runs")
    
    r = 1
    Open "C:\path\file.txt" For Input As #1
    While Not EOF(1)
        Line Input #1, textLine
        If InStr(textLine, "JobName") > 0 Then
            r = r + 1
            Cells(r, "A").Value = Trim(Split(textLine, ":")(1))
        ElseIf InStr(textLine, "send_notifications") > 0 Then
            Cells(r, "B").Value = Trim(Split(textLine, ":")(1))
        ElseIf InStr(textLine, "max_runs") > 0 Then
            Cells(r, "C").Value = Trim(Split(textLine, ":")(1))
        End If
    Wend
    Close #1
    
End Sub
 
Upvote 0
John_w said:
Try this with the file path and name changed to suit.
Code: 
Sub Extract_Text()

    Dim r As Long
    Dim textLine As String
    
    Range("A1:C1").Value = Array("JobName", "send_notifications", "max_runs")
    
    r = 1
    Open "C:\path\file.txt" For Input As #1
    While Not EOF(1)
        Line Input #1, textLine
        If InStr(textLine, "JobName") > 0 Then
            r = r + 1
            Cells(r, "A").Value = Trim(Split(textLine, ":")(1))
        ElseIf InStr(textLine, "send_notifications") > 0 Then
            Cells(r, "B").Value = Trim(Split(textLine, ":")(1))
        ElseIf InStr(textLine, "max_runs") > 0 Then
            Cells(r, "C").Value = Trim(Split(textLine, ":")(1))
        End If
    Wend
    Close #1
    
End Sub
Click to expand...

Hi John_w,

The Macro works great when it's simplified... guess more problems will come out when I customize it to fit my actual needs.

For now I have a problem:

As the template for my .txt file has

insert_job: dummy1 job_type: dummy1_type
send_notification: "Release Dummy1"
max_runs: n(dummy1)


How can I make vba recognise that job_type should be extracted separately in its own column?
Right now the column headers and data are all messed up.
٩(͡๏̯͡๏)۶​

P.S I can't change the format of the .txt file as it is generated from another program.

This is the dummy version of my Excel and .txt file:
https://drive.google.com/folderview?id=0B1biaP-f5X6zSFJDY0ZIank5cjg&usp=sharing

Thanks!
 
Upvote 0
Try this then work with other code to manipulate data. I tested it and the data seems pretty clean enough to continue from there.

Code: 
Sub CopyTExtFIle()


    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Tyger\Desktop\test2.txt", Destination:=Range("$A$1"))  'Change Connection to your file location and Desitnation to where you want to paste
        .Name = "test2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub


Just make sure to update location of your file
 
Upvote 0
Tyger i'm not sure what you mean... should I combine your code and my original code under the same module? Or is this supposed to be in another module?

tyger0951 said:
Try this then work with other code to manipulate data. I tested it and the data seems pretty clean enough to continue from there.

Code: 
Sub CopyTExtFIle()


    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Tyger\Desktop\test2.txt", Destination:=Range("$A$1"))  'Change Connection to your file location and Desitnation to where you want to paste
        .Name = "test2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub


Just make sure to update location of your file
Click to expand...
 
Upvote 0
Whole new sub . No need for new module. Paste code in your current module. Change the path to your txt file. Use the code i posted instead of yours to import text file into excel.
 
Last edited:
Upvote 0
tyger0951 said:
Whole new sub . No need for new module. Paste code in your current module. Change the path to your txt file. Use the code i posted instead of yours to import text file into excel.
Click to expand...

Hi tyger.. code works great! I realised it works exactly the same way as using the Data -> From Text function.

Anyway I think perhaps you misunderstood what I'm trying to achieve. As such I have uploaded screenshots to denote what I would require.

Intended Output:
anlRdl.jpg



From txt file:

2cNe4r.jpg


P.S fields that do not exist in the sample or are blank, should be printed as "" on Excel
 
Upvote 0
Correct it is exactly like get info from Text. My suggestion was to use that method then use more code then use all the data just imported then format it to how you want it to look. Later this evening I'll write the code to change the imported data to your perfered format
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
How to export Excel data into multiple txt files
Replies
2
Views
221
Soczuss
S
S
  • Question
VBA: extract certain data from .txt file to new sheet
Replies
6
Views
605
st34lth
S
D
  • Question
Import and format txt file into open workbook.
Replies
4
Views
613
djdbg1
D
S
  • Question
Replace different text/strings in multiple files in one folder
Replies
1
Views
241
Peter_SSs
Peter_SSs
Y
  • Question
Modify Macro to Select 1 .TXT File at a time, do changes on it (tab delimeter+column to text) to convert to .XLS file+save the file then select other
Replies
0
Views
370
yferrer
Y

Forum statistics

Threads
1,214,787
Messages
6,121,561
Members
449,038
Latest member
Guest1337

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top