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
Ok thank you tyger i'll be waiting for your code... and is there any way to close the connection after the data has been imported?

tyger0951 said:
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
Click to expand...
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
BTW off top of my head should be something like this to start doing what you need. Will write rest late this evening when have more time


Code: 
Sub MoveData ()

Dim Cell As range 
Dim JobName as String 


For Each Cell in Column(1)     

 LastRowColA = Range("A65536").End(xlUp).Row

      If cell.value  = "Insert_Job:" then   

          JobName = cell.offset(1,0).Value 

             Sheets("Results").Range("A1").Value = "Job List" 

                    Sheets("Results").Range("A" & LastRowColA).Value = JobName 

End IF  

Next Cell   


End Sub
 
Upvote 0
tyger0951 said:
What do you mean by Close the connection after the data is imported ? Like close the Txt File ?
Click to expand...

When you use your macro or the native Data -> From Text function, a connection is created between Excel and the txt file.

JCUEDm.jpg


I was wondering if there was a way to close the connection at the end of the VBA code... I believe Excel refers to this as retrieving data from external sources
 
Upvote 0
Try this :

It should delete the connections made


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
For Each cn In ThisWorkbook.Connections
cn.Delete
Next cn
End Sub
 
Upvote 0
Hi Tyger,

Just tried the code.. connections still exist.. however this problem with the connections is a relatively trivial one.. so don't stress yourself too much over it as it can be dealt with by converting the range into a table and back to a range

tyger0951 said:
Try this :

It should delete the connections made


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
For Each cn In ThisWorkbook.Connections
cn.Delete
Next cn
End Sub
Click to expand...
 
Upvote 0
Got it and gets rid of connection as well :)

I defined names of cells.





Code: 
Sub CopyTExtFIle()






Worksheets.Add().Name = "PullSheet"


    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
    
    
    lastRowCOlA = Sheets("PullSheet").Range("A65536").End(xlUp).Row




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
         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
          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
          Sheets("Results").Range("C" & lastRowCOlC + 1).Value = Note
    End If
    
    
Next


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
    
    Application.DisplayAlerts = False
   Sheets("PullSheet").Delete
    Application.DisplayAlerts = True
End Sub


https://drive.google.com/file/d/0B5Pn7PG3Fu2HTTdESXAxTmU4aFk/edit?usp=sharing
 
Last edited:
Upvote 0
Hi tyger, encountered some problems while I was trying out the Macro.

1. For the CopyTextFile Macro, it seems to skip the JobName column. Do note that I'm deliberately leaving out certain items out of each job as in the actual situation, there are cases where not all items are present for the jobs. You might want to use this file for testing:
https://drive.google.com/file/d/0B1biaP-f5X6zM3NSc0N0Q0ZNNXM/edit?usp=sharing

Would it be possible to create If Conditions such that if these items were not present in the txt file, to just create a "" entry into the corresponding fields instead. I think that leaving the predefined columns on the actual sheet before running the macro is a great idea.

ZMieEx.jpg


2. I get a subscript out of range error when I try to run the sort macro

4bHFHv.jpg


tyger0951 said:
Got it and gets rid of connection as well :)

I defined names of cells.





Code: 
Sub CopyTExtFIle()






Worksheets.Add().Name = "PullSheet"


    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
    
    
    lastRowCOlA = Sheets("PullSheet").Range("A65536").End(xlUp).Row




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
         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
          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
          Sheets("Results").Range("C" & lastRowCOlC + 1).Value = Note
    End If
    
    
Next


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
    
    Application.DisplayAlerts = False
   Sheets("PullSheet").Delete
    Application.DisplayAlerts = True
End Sub


https://drive.google.com/file/d/0B5Pn7PG3Fu2HTTdESXAxTmU4aFk/edit?usp=sharing
Click to expand...
 
Upvote 0
The search is going based off what ever the Input is ie



Your example had JobName dummy 1

so its searching for the tag JobName



In your new example you have insert_job:


Change cell A1 to insert_job: from JobName: and run



striker359 said:
Hi tyger, encountered some problems while I was trying out the Macro.

1. For the CopyTextFile Macro, it seems to skip the JobName column. Do note that I'm deliberately leaving out certain items out of each job as in the actual situation, there are cases where not all items are present for the jobs. You might want to use this file for testing:
https://drive.google.com/file/d/0B1biaP-f5X6zM3NSc0N0Q0ZNNXM/edit?usp=sharing

Would it be possible to create If Conditions such that if these items were not present in the txt file, to just create a "" entry into the corresponding fields instead. I think that leaving the predefined columns on the actual sheet before running the macro is a great idea.

ZMieEx.jpg


2. I get a subscript out of range error when I try to run the sort macro

4bHFHv.jpg
Click to expand...
 
Upvote 0
Another option is to change the code :



Code: 
[COLOR=#333333][I]If Cell.Value = Range("JobName").Value Then[/I][/COLOR]

to something like


Code: 
[COLOR=#333333][I]If Cell.Value = "insert_job:"   Then[/I][/COLOR]
 
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
256
Soczuss
S
S
  • Question
VBA: extract certain data from .txt file to new sheet
Replies
6
Views
613
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
272
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
379
yferrer
Y

Forum statistics

Threads
1,215,136
Messages
6,123,249
Members
449,093
Latest member
Vincent Khandagale

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