VB Script retrieve data from string

Hello experts,

I have a test.csv file with the following information

Code;Organization
D1;Zil
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:

Code;Organization;Parent
D1;Zil;
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

Ex:
Code;Organization;Parent
D1;Zil;
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.
LVL 1
LD16Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile ("c:\yourTest2.csv")

Set objTextFile2 = objFSO.CreateTextFile("c:\yourTest2.csv", _
    ForWriting, True)
strText = objTextFile.readall
sCSVLine = Split(strText, vbCrLf)
objTextFile2.Write sCSVLine(0) & ";Parent" & vbCrLf
For I = 1 To UBound(sCSVLine)
    If sCSVLine(I) Like "*.*" Then ' has a parent
        sCSVElements = Split(sCSVLine(I), ";")
        LastElement = Split(sCSVElements(UBound(sCSVElements)), ".")
        For Idx = 0 To UBound(LastElement) - 1
            parentElement = parentElement & "." & LastElement(Idx)
        Next
        parentElement = Right(parentElement, Len(parentElement) - 1)
        objTextFile2.Write sCSVLine(I) & ";" & parentElement & vbCrLf
        parentElement = ""
    Else
        objTextFile2.Write sCSVLine(I) & vbCrLf
    End If
Next I
objTextFile2.Close
objTextFile.Close

Open in new window

Regards
LD16Author Commented:
@ Rgonzo1971: I got an error message in line 22, char 6.
Capture.GIF
Rgonzo1971Commented:
pls delete the I at the end of line 22
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Learn More!
LD16Author Commented:
Hello,

I got a new error message in line:10 char:5, Sub o Function not defined.
Additionnally if I relaunch the report I got a new error message line 4, char:1 the file already exist.

Thank you again for your help.
Bill PrewIT / Software Engineering ConsultantCommented:
Give this a try:

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
 
' Set input and output files
strInFile = "test.csv"
strOutFile = "test2.csv"

' Read file into an array
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
arrLines = Split(objFile.ReadAll, VbCrLf)
objFile.Close

' Open output file
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)

' Loop through all lines of the file, create new column
For i = 0 To UBound(arrLines)
   If arrLines(i) <> "" Then
      arrFields = Split(arrLines(i), ";")
      strCode = arrFields(0)
      strOrg = arrFields(1)
      ' Process header line differently
      If i = 0 Then
         objFile.WriteLine strCode & ";" & strOrg & ";Parent"
      Else
         arrOrg = Split(arrFields(1), ".")
         strParent = ""
         For j = 0 To UBound(arrOrg)-1
            If strParent = "" Then
               strParent = arrOrg(j)
            Else
               strParent = strParent & "." & arrOrg(j)
            End If
         Next
         objFile.WriteLine strCode & ";" & strOrg & ";" & strParent
      End If
   End If
Next

' Write output file
objFile.Close

Open in new window

~bp
Rgonzo1971Commented:
Hi,

pls try

Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("C:\Users\Rafael Gonzalez\Documents\ee\Test1.csv")

Set objTextFile2 = objFSO.CreateTextFile("C:\Users\Rafael Gonzalez\Documents\ee\Test2.csv", _
    ForWriting, True)
strText = objTextFile.readall
sCSVLine = Split(strText, vbCrLf)
objTextFile2.Write sCSVLine(0) & ";Parent" & vbCrLf
For I = 1 To UBound(sCSVLine)
    If InStr(1, sCSVLine(I), ".") Then ' has a parent
        sCSVElements = Split(sCSVLine(I), ";")
        LastElement = Split(sCSVElements(UBound(sCSVElements)), ".")
        For Idx = 0 To UBound(LastElement) - 1
            parentElement = parentElement & "." & LastElement(Idx)
        Next
        parentElement = Right(parentElement, Len(parentElement) - 1)
        objTextFile2.Write sCSVLine(I) & ";" & parentElement & vbCrLf
        parentElement = ""
    Else
        objTextFile2.Write sCSVLine(I) & vbCrLf
    End If
Next
objTextFile2.Close
objTextFile.Close

Open in new window

Regards
LD16Author Commented:
Hello Bill,

Thank you! it works great! However I have one remark.

What happens if the direct level +1 it doesn't exist?
With the following input:
Code;Organization
P21;ZID.ZID Projects
P104;ZID.ZID Projects.BEE.France
P105;ZID.ZID Projects.BEE.India
P106;ZID.ZID Projects.BEE.USA

Open in new window

Your script display:the following
Code;Organization;Parent
P21;ZID.ZID Projects;ZID
P104;ZID.ZID Projects.BEE.France;ZID.ZID Projects.BEE
P105;ZID.ZID Projects.BEE.India;ZID.ZID Projects.BEE
P106;ZID.ZID Projects.BEE.USA;ZID.ZID Projects.BEE

Open in new window

However ZID.ZID Projects.BEE is not defined as an Organization and it is considered as Parent.

Can we add an extra check to lookup to the next level 1 to have the final result for those specific cases?

Code;Organization;Parent
P21;ZID.ZID Projects;
P104;ZID.ZID Projects.BEE.France;ZID.ZID Projects
P105;ZID.ZID Projects.BEE.India;ZID.ZID Projects
P106;ZID.ZID Projects.BEE.USA;ZID.ZID Projects

Open in new window


In that case with respect the rule that Parent is always defined as Organization
I am aware of the fact that this is an extra bonus as the inital code works.
Bill PrewIT / Software Engineering ConsultantCommented:
Does the parent that you want to check if exists always come earlier in the file, or could it come after?

For example is this also valid:

Code;Organization
P104;ZID.ZID Projects.BEE.France
P105;ZID.ZID Projects.BEE.India
P106;ZID.ZID Projects.BEE.USA
P21;ZID.ZID Projects

Open in new window

~bp
LD16Author Commented:
Good question.

The parent, should come early. However is there a way to have the two versions when it comes early and when it doesn't?
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, give this a try, it will handle a parent defined anywhere in the file.

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
 
' Set input and output files
strInFile = "test.csv"
strOutFile = "test2.csv"

' Read file into an array
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
arrLines = Split(objFile.ReadAll, VbCrLf)
objFile.Close

' Create dictionary for defined orgs (can be parents)
Set dicOrg = CreateObject("Scripting.Dictionary")
dicOrg.CompareMode = vbTextCompare

' Loop through all lines of the file, load into dictionary
For i = 1 To UBound(arrLines)
   If arrLines(i) <> "" Then
      arrFields = Split(arrLines(i), ";")
      strOrg = arrFields(1)
      dicOrg.Add strOrg, i
   End If
Next

' Open output file
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)

' Loop through all lines of the file, create parent column
For i = 0 To UBound(arrLines)
   If arrLines(i) <> "" Then
      arrFields = Split(arrLines(i), ";")
      strCode = arrFields(0)
      strOrg = arrFields(1)

      ' Process header line differently
      If i = 0 Then
         objFile.WriteLine strCode & ";" & strOrg & ";Parent"
      Else
         objFile.WriteLine strCode & ";" & strOrg & ";" & SetParent(strOrg)
      End If
   End If
Next

' Write output file
objFile.Close

' Function to extract parent from org, and make sure it exists
Function SetParent(strOrg)
   ' Default to no parent if can't determine one
   SetParent = ""

   ' Split input org at "." delims
   arrOrg = Split(strOrg, ".")
    
   ' If no parent in this org, return
   If UBound(arrOrg) = 0 Then Exit Function

   ' Keep removing nodes from the right of the org until we get a defined parent
   For j = UBound(arrOrg)-1 To 0 Step -1
      ReDim Preserve arrOrg(j)
      strTemp = Join(arrOrg, ".")
      If dicOrg.Exists(strTemp) Then
         SetParent = strTemp
         Exit Function
      End If
   Next

   ' No parent found if we get to this point
End Function

Open in new window

~bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rgonzo1971Commented:
LD16Author Commented:
@Rgonzo1971: your solution works however it doesn't meet the last requirement posted on ID: 40642537.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.