This course teaches how to install and configure Windows Server 2012 R2. It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).
VB Script retrieve data from string
Hello experts,
I have a test.csv file with the following information
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:
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:
Thank you in advance for your help.
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
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
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]
Thank you in advance for your help.
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
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.
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.
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.
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
Hi,
pls try
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
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:
Can we add an extra check to lookup to the next level 1 to have the final result for those specific cases?
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.
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
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
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
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.
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:
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
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?
The parent, should come early. However is there a way to have the two versions when it comes early and when it doesn't?
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
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
Hi LD16,
have you tried my last solution?
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28628090.html#a40642506
have you tried my last solution?
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28628090.html#a40642506
@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.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2010 Inter… 277 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2007 Inter… 218 lessons
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
pls try
Open in new window
Regards