We help IT Professionals succeed at work.
Get Started

VB Script retrieve data from string

Luis Diaz
Luis Diaz asked
Last Modified: 2015-03-04
Hello experts,

I have a test.csv file with the following information

D21;Zil.Div HD
D101;Zil.Div HD.BEE
D104;Zil.Div HD.BEE.France
D105;Zil.Div HD.BEE.India
D106;Zil.Div HD.BEE.USA
D93;Zil.Div HD.Breakers
D95;Zil.Div HD.Breakers.China
D97;Zil.Div HD.Breakers.France
D99;Zil.Div HD.Breakers.USA
D107;Zil.Div HD.S & Control
D111;Zil.Div HD.S & Control.France
D113;Zil.Div HD.S & Control.USA
D109;Zil.Div HD.S & Control.China

Open in new window

I would like to generate a test2.csv with and additional column (Parent) In which I look up the N+1 level of Organization as following:

D21;Zil.Div HD;Zil
D101;Zil.Div HD.BEE;Zil.Div HD
D104;Zil.Div HD.BEE.France;Zil.Div HD.BEE
D105;Zil.Div HD.BEE.India;Zil.Div HD.BEE
D106;Zil.Div HD.BEE.USA;Zil.Div HD.BEE
D93;Zil.Div HD.Breakers;Zil.Div HD
D95;Zil.Div HD.Breakers.China;Zil.Div HD.Breakers
D97;Zil.Div HD.Breakers.France;Zil.Div HD.Breakers
D99;Zil.Div HD.Breakers.USA;Zil.Div HD.Breakers
D107;Zil.Div HD.S & Control;Zil.Div HD
D111;Zil.Div HD.S & Control.France;Zil.Div HD.S & Control
D113;Zil.Div HD.S & Control.USA;Zil.Div HD.S & Control
D109;Zil.Div HD.S & Control.China;Zil.Div HD.S & Control

Open in new window

Warning: The line above is not always the Parent so Parent need to be something likes this:
Parent = Organization string in which I remove string from right to left till I find a "." and If the string revised is already listed as an Organization I display as parent otherwise I continue removing the string till I find the second "."

The difficult thing here is to display in Parent column only If it listed in Organization column
So if the direct N+1 doesn't exist it lookups the N+2

D21;Zil.Div HD;Zil
D104;Zil.Div HD.BEE.France;[b]Zil.Div HD[/b]

Open in new window

Thank you in advance for your help.
Watch Question
Test your restores, not your backups...
Expert of the Year 2019
Distinguished Expert 2020
This problem has been solved!
Unlock 1 Answer and 12 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE