Link to home
Start Free TrialLog in
Avatar of Luis Diaz
Luis DiazFlag for Colombia

asked on

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.
Avatar of Rgonzo1971
Rgonzo1971
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
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

@ Rgonzo1971: I got an error message in line 22, char 6.
User generated image
Avatar of Rgonzo1971
Rgonzo1971
pls delete the I at the end of line 22
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

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.
Avatar of Bill Prew
Bill Prew
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
Avatar of Rgonzo1971
Rgonzo1971
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
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

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.
Avatar of Bill Prew
Bill Prew
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
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

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