Macro to extract text from .txt file into Excel

[striker359]

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 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!

 

[striker359]

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?

 

[tyger0951]

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

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.

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

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?

 

[striker359]

So is it possible to delete the sort macro?

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

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.

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

 

[tyger0951]

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

 

[striker359]

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?

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?

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

 

[striker359]

And what does this code block actually do??

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

 

[tyger0951]

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 .

 

[striker359]

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

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 .

 

[striker359]

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.

 

[tyger0951]

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

'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

' 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 .