Import specific text from notepad to excel in different columns

[Miteshkg]

Hello Forum

I've specific requirement where in I need to copy only selected rows from text file to excel in different columns.
below is the sample from the text file.

What I need is the highlighted in bold should be fetch by Macro till the end of text file and copy the output to 4 different columns as
Column A Column B Column C
Policy Name Active Client/HW/OS/Pri/CIT

Keeping in mind that some of the Policy name will have only 1 or 2 Client/HW/OS/Pri/CIT details but some of them has more than 25 or 40 as well. So, all Client/HW/OS/Pri/CIT details should be available in one single box only. So desired output considering below example

Column A Column B Column C
Policy Name Active Client/HW/OS/Pri/CIT
AD_GRT_CH2 Yes bymccrsd0161.bentley.emea.vwg
bymccrsd0162.bentley.emea.vwg


Kindly help to construct a good vba code to automate the time consuming task.

====================================================
Policy Name: AD_GRT_CH2
Options: 0x0
template: FALSE
audit_reason: ?
Names: (none)
Policy Type: MS-Windows (13)
Active: yes
Effective date: 22/07/2014 19:28:11
Client Compress: no
Follow NFS Mnts: no
Backup netwrk drvs:no
Collect TIR info: no
Mult. Data Stream: yes
Perform Snapshot Backup: no
Snapshot Method: (none)
Snapshot Method Arguments: (none)
Perform Offhost Backup: no
Backup Copy: 0
Use Data Mover: no
Data Mover Type: 2
Use Alternate Client: no
Alternate Client Name: (none)
Use Virtual Machine: 0
Hyper-V Server Name: (none)
Enable Instant Recovery: no
Policy Priority: 0
Max Jobs/Policy: 4
Disaster Recovery: 0
Collect BMR Info: no
Keyword: (none specified)
Data Classification: -
Residence is Storage Lifecycle Policy: yes
Client Encrypt: no
Checkpoint: no
Residence: CH2_Daily_SLP
Volume Pool: NetBackup
Server Group: *ANY*
Granular Restore Info: yes
Exchange Source attributes: no
Exchange DAG Preferred Server: (none defined)
Application Discovery: no
Discovery Lifetime: 28800 seconds
ASC Application and attributes: (none defined)
Generation: 35
Ignore Client Direct: no
Use Accelerator: no
Optimized Backup: no
Client/HW/OS/Pri/CIT: bymccrsd0161.xxx.xxx.net Windows-x64 Windows2008 0 0 0 ?
Client/HW/OS/Pri/CIT: bymccrsd0162.xxx.xxx.net Windows-x64 Windows2008 0 0 0 ?
Include: ALL_LOCAL_DRIVES
Include: System State:\
Include: Shadow Copy Components:\
Schedule: Monthly_Backup
====================================================

 

[vletm]

Miteshkg
Did You miss to upload sample file and sample output file?
(Keeping in mind that some of the Policy name ... )

 

[Miteshkg]

Miteshkg
Did You miss to upload sample file and sample output file?
(Keeping in mind that some of the Policy name ... )

Please find the attached sample text file and required output file

 

[GraH - Guido]

Hi Miteshkg,

Sounds like an ideal job for Power Query. Do you happen to use Excel for windows? Then it applies to version 2010 and onwards.

I have made it in Excel version 2016 and the result looks like. No VBA coding, just using the UI and some formulas in PQ.


Basically it creates a script and applies all of the steps over and over again.
The steps should work for you, if you change the source patch in the very first applied step of the PQ, which is always Source. Click the gear icon.


If you do not know much about PQ let me know. I'll elaborate a bit more.
Google it to see how you install/activate it for the different versions. Don't let the name POWER scare you. It is a power house, but you do not need to be a power user. You see, I use it... And I'm far from (below) the Ninja level.

kr
G.

 

[vletm]

Miteshkg
... or one possible sample with VBA.
1) Press [Show It] >> Select file
2) Watch result

 

[Miteshkg]

Hi Miteshkg,

Sounds like an ideal job for Power Query. Do you happen to use Excel for windows? Then it applies to version 2010 and onwards.

I have made it in Excel version 2016 and the result looks like. No VBA coding, just using the UI and some formulas in PQ.
View attachment 48607

Basically it creates a script and applies all of the steps over and over again.
The steps should work for you, if you change the source patch in the very first applied step of the PQ, which is always Source. Click the gear icon.
View attachment 48608

If you do not know much about PQ let me know. I'll elaborate a bit more.
Google it to see how you install/activate it for the different versions. Don't let the name POWER scare you. It is a power house, but you do not need to be a power user. You see, I use it... And I'm far from (below) the Ninja level.

kr
G.

Dear Guido

Let me first of all thank you and I really owe you a treat champ...you really made my day....have learnt something interesting apart from routine VBA coding....this one is really useful for my other project task to play with analysis data. I am really thankful to you and really appreciate your quick help on this issue. Never thought that I would be getting such a quick response to my query on this forum. You guys are really Champ and deserve a salute man..... Thanks once again.......

 

[Miteshkg]

Miteshkg
... or one possible sample with VBA.
1) Press [Show It] >> Select file
2) Watch result

Dear vletm

Oh Dear....you have also done great job... have really learnt interesting stuffs in VBA coding. Although I am not champ like you guys but have initially started doing VBA coding to automate daily routine tasks and found excel macro task really interesting and adding learning curve to my knowledge.
THanks once again for helping me and providing such a quick response to my query. You really made my day......

 

[GraH - Guido]

Dear Guido

Let me first of all thank you and I really owe you a treat champ...you really made my day....have learnt something interesting apart from routine VBA coding....this one is really useful for my other project task to play with analysis data. I am really thankful to you and really appreciate your quick help on this issue. Never thought that I would be getting such a quick response to my query on this forum. You guys are really Champ and deserve a salute man..... Thanks once again.......

My pleasure, ... I like you liked it.