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:
Sort by date Sort by votes
Anyway Tyger..

Just wanted to ask you... is the sort macro even meant to be run?

And you were mentioning to delete "N/A" values.. where do I put that code?

Inside the CopyTextFile or the Sort?
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Not at the computer I have file saved on. More than likely NO the macro Sort is not meant to be ran it was probably just a test sub i was working on in the workbook.

Put the code in the CopyTextFile Sub


Code: 
lastRowCOlA = Sheets("Results").Range("A65536").End(xlUp).Row ' Next open cell in results sheets 
    If Cell.Value = Range("JobName").Value Then 'Checks if cell matches descrption 
          JobName = Cell.Offset(0, 1).Value  ' Grabs the cell next to the tag name 
  If JobName =  "" then  ' If the cell is blank  
               JobName = "n/a"  ' change it from blank to display n/a 
             End if ' Moves on to next code.



Code: 
For Each Cell In Sheets("PullSheet").Range("A1", Range("A1").Offset(lastRowCOlA, 0))
        
        

 lastRowCOlA = Sheets("Results").Range("A65536").End(xlUp).Row
    If Cell.Value = Range("JobName").Value Then
          JobName = Cell.Offset(0, 1).Value
  If JobName =  "" then  ' If the cell is blank  
               JobName = "n/a"  ' change it from blank to display n/a 
             End if ' Moves on to next code.   
         Sheets("Results").Range("A" & lastRowCOlA + 1).Value = JobName
    End If
        
 LastRowColB = Sheets("Results").Range("B65536").End(xlUp).Row
    If Cell.Value = Range("sendnotifications").Value Then
          Note = Cell.Offset(0, 1).Value
              If Note =  "" then  ' If the cell is blank  
               Note = "n/a"  ' change it from blank to display n/a 
             End if ' Moves on to next code.   
          Sheets("Results").Range("B" & LastRowColB + 1).Value = Note
    End If
    
    
  lastRowCOlC = Sheets("Results").Range("C65536").End(xlUp).Row
    If Cell.Value = Range("max_runs").Value Then
          Note = Cell.Offset(0, 1).Value
  If Note=  "" then  ' If the cell is blank  
               Note = "n/a"  ' change it from blank to display n/a 
             End if ' Moves on to next code.   
          Sheets("Results").Range("C" & lastRowCOlC + 1).Value = Note
    End If
    
    
Next





striker359 said:
Anyway Tyger..

Just wanted to ask you... is the sort macro even meant to be run?

And you were mentioning to delete "N/A" values.. where do I put that code?

Inside the CopyTextFile or the Sort?
Click to expand...
 
Upvote 0
So is it possible to delete the sort macro?

tyger0951 said:
Not at the computer I have file saved on. More than likely NO the macro Sort is not meant to be ran it was probably just a test sub i was working on in the workbook.

Put the code in the CopyTextFile Sub


Code: 
lastRowCOlA = Sheets("Results").Range("A65536").End(xlUp).Row ' Next open cell in results sheets 
    If Cell.Value = Range("JobName").Value Then 'Checks if cell matches descrption 
          JobName = Cell.Offset(0, 1).Value  ' Grabs the cell next to the tag name 
  If JobName =  "" then  ' If the cell is blank  
               JobName = "n/a"  ' change it from blank to display n/a 
             End if ' Moves on to next code.



Code: 
For Each Cell In Sheets("PullSheet").Range("A1", Range("A1").Offset(lastRowCOlA, 0))
        
        

 lastRowCOlA = Sheets("Results").Range("A65536").End(xlUp).Row
    If Cell.Value = Range("JobName").Value Then
          JobName = Cell.Offset(0, 1).Value
  If JobName =  "" then  ' If the cell is blank  
               JobName = "n/a"  ' change it from blank to display n/a 
             End if ' Moves on to next code.   
         Sheets("Results").Range("A" & lastRowCOlA + 1).Value = JobName
    End If
        
 LastRowColB = Sheets("Results").Range("B65536").End(xlUp).Row
    If Cell.Value = Range("sendnotifications").Value Then
          Note = Cell.Offset(0, 1).Value
              If Note =  "" then  ' If the cell is blank  
               Note = "n/a"  ' change it from blank to display n/a 
             End if ' Moves on to next code.   
          Sheets("Results").Range("B" & LastRowColB + 1).Value = Note
    End If
    
    
  lastRowCOlC = Sheets("Results").Range("C65536").End(xlUp).Row
    If Cell.Value = Range("max_runs").Value Then
          Note = Cell.Offset(0, 1).Value
  If Note=  "" then  ' If the cell is blank  
               Note = "n/a"  ' change it from blank to display n/a 
             End if ' Moves on to next code.   
          Sheets("Results").Range("C" & lastRowCOlC + 1).Value = Note
    End If
    
    
Next
Click to expand...
 
Upvote 0
Of course , Either delete from the VBA window just select all from Sub Sort () through End Sub and delete. Or Alt+ F8 to bring up list of marcos and choose sort and then choose to delete it
 
Upvote 0
Ok i've deleted the sort macro. Now i'm about to start adapting it into my actual requirements with even more fields than what we have been doing.

I kinda need some idea on how I should start going about it.

1. For more headers, I'll just type in the column headers and then create named ranges for them.
2. What does this code actually do?

Code: 
LastRowCOlC = Sheets("PullSheet").Range("C65536").End(xlUp).Row

For Each Cell In Sheets("PullSheet").Range("C1", Range("C1").Offset(LastRowCOlC, 0))
        
      
    lastRowCOlD = Sheets("Results").Range("D65536").End(xlUp).Row
      If Cell.Value = Range("job_type").Value Then
          Note = Cell.Offset(0, 1).Value
          Sheets("Results").Range("D" & lastRowCOlD + 1).Value = Note
    End If

If i were to add more columns, would I have to change the LastRowCOLC to something like maybe LastRowColAA if my last column is AA?

tyger0951 said:
Of course , Either delete from the VBA window just select all from Sub Sort () through End Sub and delete. Or Alt+ F8 to bring up list of marcos and choose sort and then choose to delete it
Click to expand...
 
Upvote 0
And what does this code block actually do??

Code: 
LastRowColC = Sheets("PullSheet").Range("C65536").End(xlUp).Row


For Each Cell In Sheets("PullSheet").Range("C1", Range("C1").Offset(LastRowColC, 0))
        
      
    lastRowCOlD = Sheets("Results").Range("D65536").End(xlUp).Row
      If Cell.Value = Range("job_type").Value Then
          Note = Cell.Offset(0, 1).Value
          Sheets("Results").Range("D" & lastRowCOlD + 1).Value = Note
    End If


Next
 
Upvote 0
Do me a favor and reload your current workbook to google drive again for me to re-download. I'll add comments to the code so you can follow along reading or stepping through the code with F8. I MIGHT also add some code so you can watch it all happening and moving on screen if you like .
 
Upvote 0
Ok i'll send you my actual requirements with the actual columns and items needed.

https://drive.google.com/folderview?id=0B1biaP-f5X6zSFJDY0ZIank5cjg&usp=sharing

Really really appreciate your help

tyger0951 said:
Do me a favor and reload your current workbook to google drive again for me to re-download. I'll add comments to the code so you can follow along reading or stepping through the code with F8. I MIGHT also add some code so you can watch it all happening and moving on screen if you like .
Click to expand...
 
Upvote 0
Oh ya.. items that don't appear in the .txt file just means that it wasn't generated by the program, but i still have to prepare for these items in case they are being generated some day.
 
Upvote 0
Code: 
Sub CopyTextFile()


Worksheets.Add().Name = "PullSheet" 'Make the sheet for the data from txt file to go into


    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"  'Name of connection 
        .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


'Pull Sheet was Made  


'Now Begins Looping through data to move it to results sheet 



    
lastRowCOlA = Sheets("PullSheet").Range("A65536").End(xlUp).Row 'Finds he number of the last cell in Column A in Pullsheet


For Each Cell In Sheets("PullSheet").Range("A1", Range("A1").Offset(lastRowCOlA, 0))  'Sets to look in each cell in range of data in column A         

'Start Point 1

         lastRowCOlA = Sheets("Results").Range("A65536").End(xlUp).Row  ' Gets the next open cell in Colmn A of results page


        If Cell.Value = "insert_job:" Or Cell.Value = "update_job:" Then 'Checks if cell equals criteria if it doesn't skips to next cell
          JobName = Cell.Offset(0, 1).Value ' Sets JobName as the value of the cell one to the right 
             If JobName = "" Then   ' If the cell is blank
                    JobName = "NA"  ' change it from blank to NA IT MUST HAVE A VALUE NOT A BLANK
                End If   ' Stops First IF     
                   End If   ' Stops Second IF  
        Sheets("Results").Range("A" & lastRowCOlA + 1).Value = JobName  ' Puts JobName in next open cell in results  
        
' Ends looking in Job Names From Text File To Results Page 


' End Point 1  


' Starts checking if cell is equal to description 


'Start Point 2     
        LastRowColB = Sheets("Results").Range("B65536").End(xlUp).Row ' Find Next empty cell in column b of results 
                   If Cell.Value = "description:" Then 'Checks if cell equals criteria 
              Note = Cell.Offset(0, 1).Value 'Grabs value of cell one to the right
                                If Cell.Value = "" Then ' Checks if cell is blank IT CANNOT BE SO IF IT IS  
                                    Cell.Value = "Na"  ' Change Cell Value to NA to be pasted into results sheet and not mess up code to find next open cell 
                              End If
                                    End IF           
          Sheets("Results").Range("B" & LastRowColB + 1).Value = Note 'Inputs value into results page 
                
' Ends looking for Descrtiption 


'End Point 2


'Need to add more Start and End Points for each Tag in your Txt File 




    
Next ' Loops Back to check Next Cell in Column A    




' Since Jobtype is under a different column than all the other data need new for ecah statement 




LastRowCOlC = Sheets("PullSheet").Range("C65536").End(xlUp).Row ' Finds last cell in column c of pull sheet 


For Each Cell In Sheets("PullSheet").Range("C1", Range("C1").Offset(LastRowCOlC, 0)) 'Sets loop for all items in Colmn C
    lastRowCOlD = Sheets("Results").Range("D65536").End(xlUp).Row ' Sets next Blank row in Results sheet 
      If Cell.Value = "job_type" Then ' Checks if cell is equal to criteria 
          Note = Cell.Offset(0, 1).Value ' if does match copies cell one over to the right
                        If Cell.Value = "" Then  ' Checks if blank 
                            Cell.Value = "Na" ' Change blank to NA caues cannot have a blank breaks code 
                            End If ' stops first if  
                            End IF ' stops second if 
          Sheets("Results").Range("D" & lastRowCOlD + 1).Value = Note ' Sets next blank cell in results  to found value 
Next   ' Loops to next cell in Range of Column C we set 


    
    Application.DisplayAlerts = False 'Turns off pop up windows so it wont ask you if are sure you want to delete page 
   Sheets("PullSheet").Delete ' Deletes page ...not needed but I like temp pages and you wanted connection gone 
    Application.DisplayAlerts = True ' Turns back on  




' Below Will remove all Na and replace with blank 


For Each cell In ActiveSheet 
 If cell.Value = "Na" Then Cell.Clear
 Nex




End Sub





For the note in the code : Need to add more Start and End Points for each Tag in your Txt File

Notice how the only things that differ from

Code: 
'Now Begins Looping through to move over insert job  


'Start Point 1
  
         
         lastRowCOlA = Sheets("Results").Range("A65536").End(xlUp).Row  ' Gets the next open cell in Colmn A of results page
        If Cell.Value = "insert_job:" Or Cell.Value = "update_job:" Then 'Checks if cell equals criteria if it doesn't skips to next cell
          JobName = Cell.Offset(0, 1).Value ' Sets JobName as the value of the cell one to the right 
             If JobName = "" Then   ' If the cell is blank
                    JobName = "NA"  ' change it from blank to NA IT MUST HAVE A VALUE NOT A BLANK
                End If   ' Stops First IF     
                   End If   ' Stops Second IF  
        Sheets("Results").Range("A" & lastRowCOlA + 1).Value = JobName  ' Puts JobName in next open cell in results  
        
' Ends looking in Job Names From Text File To Results Page 


' End Point 1

And


Code: 
' Starts checking if cell is equal to description 


'Start Point 2     
        LastRowColB = Sheets("Results").Range("B65536").End(xlUp).Row ' Find Next empty cell in column b of results 
                   If Cell.Value = "description:" Then 'Checks if cell equals criteria 
              Note = Cell.Offset(0, 1).Value 'Grabs value of cell one to the right
                                If Cell.Value = "" Then ' Checks if cell is blank IT CANNOT BE SO IF IT IS  
                                    Cell.Value = "Na"  ' Change Cell Value to NA to be pasted into results sheet and not mess up code to find next open cell 
                              End If
                                    End IF           
          Sheets("Results").Range("B" & LastRowColB + 1).Value = Note 'Inputs value into results page 
                
' Ends looking for Descrtiption 


'End Point 2


Is that the A and B change in lastRowCol and the Range 's and Your Search Term .



Should be able to just make more of them increasing the letter for each , C D E ect.... and changing your criteria

Easiest is just copy and paste then use cntl + h to replce B with D then change your criteria to appropriate one .
 
Last edited:
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
258
Soczuss
S
S
  • Question
VBA: extract certain data from .txt file to new sheet
Replies
6
Views
615
st34lth
S
D
  • Question
Import and format txt file into open workbook.
Replies
4
Views
638
djdbg1
D
S
  • Question
Replace different text/strings in multiple files in one folder
Replies
1
Views
274
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
380
yferrer
Y

Forum statistics

Threads
1,215,148
Messages
6,123,309
Members
449,095
Latest member
Chestertim

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