We help IT Professionals succeed at work.

VB Script retrieve data from string

Luis Diaz
Luis Diaz asked
on
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.
Comment
Watch Question

View Solution Only

Rgonzo1971
Top Expert 2016

Commented:
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
Luis DiazIT consultant

Author

Commented:
@ Rgonzo1971: I got an error message in line 22, char 6.
Capture.GIF
Rgonzo1971
Top Expert 2016

Commented:
pls delete the I at the end of line 22
Luis DiazIT consultant

Author

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 PrewTest your restores, not your backups...
Top Expert 2016

Commented:
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
Rgonzo1971
Top Expert 2016

Commented:
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
Luis DiazIT consultant

Author

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 PrewTest your restores, not your backups...
Top Expert 2016

Commented:
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
Luis DiazIT consultant

Author

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?
Test your restores, not your backups...
Top Expert 2016
Commented:
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
Rgonzo1971
Top Expert 2016

Commented:
Luis DiazIT consultant

Author

Commented:
@Rgonzo1971: your solution works however it doesn't meet the last requirement posted on ID: 40642537.