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]

Hi Tyger,

Not sure if you are able to solve this, seeing that the delimiter used is a space but unfortunately for the notification_msg tag, the items DO have spaces in them, thus the macro only extracts the first word in the tag.

I have an idea but not too sure how to execute it in VBA.

1. In the macro, enclose the item in " " so that it is treated as one string
2. Remove the " " during the print to cell



P.S I'm not sure how to find what tag the macro is looking at anymore, since there's no mention of what the search criteria.
How does that even work?

Does it mean that if one job has its tags in a different order as the rest, the data input for that job would be screwed up?
eg. Normal Sequence is: insert_job, description, condition


Jumbled Sequence is: condition, insert_job, description

 

[striker359]

Ok just tried jumbling the order and it still copies fine... Now i'm even more curious how the macro works coz I have totally no idea how to debug this baby right now.

(●´ω｀●)ゞ​

Hi Tyger,

Not sure if you are able to solve this, seeing that the delimiter used is a space but unfortunately for the notification_msg tag, the items DO have spaces in them, thus the macro only extracts the first word in the tag.

I have an idea but not too sure how to execute it in VBA.

1. In the macro, enclose the item in " " so that it is treated as one string
2. Remove the " " during the print to cell



P.S I'm not sure how to find what tag the macro is looking at anymore, since there's no mention of what the search criteria.
How does that even work?

Does it mean that if one job has its tags in a different order as the rest, the data input for that job would be screwed up?
eg. Normal Sequence is: insert_job, description, condition


Jumbled Sequence is: condition, insert_job, description

 

[tyger0951]

The sheet is using the List of Options sheet to find the criteria .

I = I + 1  ' Makes I equal 3 since it was first set to equal 2 
LastRowColc = wsResults.Range("C65536").End(xlUp).Row
                   If Cell.Value = wsOptions.Range("A" & I).Value Then  ' Checks to see if cell equals what is in cell A3 of the options sheet 
              Note = Cell.Offset(0, 1).Value 
                   wsResults.Range("C" & LastRowColc + 1).Value = Note   
                End If

 

[tyger0951]

I'm not quite sure what the data looked like for notification_msg. Can you give examples

Ok just tried jumbling the order and it still copies fine... Now i'm even more curious how the macro works coz I have totally no idea how to debug this baby right now.

(●´ω｀●)ゞ​

 

[striker359]

Another problem is that for some tags, eg. start_times,the data is extracted to the row below the supposed job's row:

For eg:

If the job at row 30 has a start_time, the start_time from that job is printed on row 31 instead.

 

[striker359]

I'm not quite sure what the data looked like for notification_msg. Can you give examples

notification_msg: Please contact Dummy


What ends up getting extracted is just Please

 

[tyger0951]

More than likely the please contact dummy is being separated into different cells instead of one. This is cause of how the query was set up.

Change

TextFileSpaceDelimiter = True

To

TextFileSpaceDelimiter = False

notification_msg: Please contact Dummy


What ends up getting extracted is just Please

 

[striker359]

More than likely the please contact dummy is being separated into different cells instead of one. This is cause of how the query was set up.

Change

TextFileSpaceDelimiter = True

To

TextFileSpaceDelimiter = False

That's what I tried but in the end nothing was printed when the space delimiter was switched off

 

[tyger0951]

For this you'll have to change the code to have an offset(-1,0) so it goes up one row

Another problem is that for some tags, eg. start_times,the data is extracted to the row below the supposed job's row:

For eg:

If the job at row 30 has a start_time, the start_time from that job is printed on row 31 instead.

 

[striker359]

Right now there are 2 main problems I've encountered:

1. When there's a space in the data, only the first word is extracted. (I understand that it's caused by the space delimiter, but when the space delimiter is turned off, nothing gets extracted.)

2. The data printed occasionally end up in the wrong row (aka data from one job is printed onto the row of another job instead).


PS. Is this supposed to be created for every column?

For Each Cell In Range("B1:B6000")
If Cell.Value = "" Then
Cell.Value = "na"
End If
Next

If so, I've made it to become like that and as expected, it took a way longer time to finish executing because of the greater range involved.

For Each Cell In Range("A1:AA6000")
If Cell.Value = "" Then
Cell.Value = "na"
End If
Next

Even so, I failed to notice any difference between the 2 codes, so I was wondering what this code actually did, considering the fact that I saw no "na" even when i removed this code:

For Each Cell In wsResults.Range("A1:Z6000")
 If Cell.Value = "na" Then Cell.Clear
 Next