Vbscript Parse Comma Delimited String For Number Values Dynamically.
Hello All,
I am trying to parse data within 1 string with values in this example. I have spent time reviewing Instr, Mid,and Split etc. functions in VBScript but, don't have a good idea of a combination that would do the following for me dynamically with the values changing in sizes.
Data from Text file in 1 string value => 9999-ROC013006-3.01,9999-ROC013006-3.02,9999-ROC013006-3.03.
I used the Mid function and concatenated the values to get results I am looking for 3.01,3.02,3.03. This only works when the string data is always in this format and the numbers in between the "-" and "," do not change in size. The example I have done only has 3 fields within the "," commas. The file could have 1 or over 20 fields so my method would not work.
Is there a way to code this so it seaches the string and picks out the values withing the second occurance of "-" and before ","?
Attached is the Bartender file and the Text file I am testing with.
Please let me know. I don't have much string manupulation coding experience with VB or VBScripting.
Thank you for your help.
Josh
I am trying to parse data within 1 string with values in this example. I have spent time reviewing Instr, Mid,and Split etc. functions in VBScript but, don't have a good idea of a combination that would do the following for me dynamically with the values changing in sizes.
Data from Text file in 1 string value => 9999-ROC013006-3.01,9999-ROC013006-3.02,9999-ROC013006-3.03.
I used the Mid function and concatenated the values to get results I am looking for 3.01,3.02,3.03. This only works when the string data is always in this format and the numbers in between the "-" and "," do not change in size. The example I have done only has 3 fields within the "," commas. The file could have 1 or over 20 fields so my method would not work.
Is there a way to code this so it seaches the string and picks out the values withing the second occurance of "-" and before ","?
Attached is the Bartender file and the Text file I am testing with.
Please let me know. I don't have much string manupulation coding experience with VB or VBScripting.
Thank you for your help.
Josh
0
-
[quote name='jkuzenka' timestamp='1334111500' post='2177']
Hello All,
I am trying to parse data within 1 string with values in this example. I have spent time reviewing Instr, Mid,and Split etc. functions in VBScript but, don't have a good idea of a combination that would do the following for me dynamically with the values changing in sizes.
Data from Text file in 1 string value => 9999-ROC013006-3.01,9999-ROC013006-3.02,9999-ROC013006-3.03.
I used the Mid function and concatenated the values to get results I am looking for 3.01,3.02,3.03. This only works when the string data is always in this format and the numbers in between the "-" and "," do not change in size. The example I have done only has 3 fields within the "," commas. The file could have 1 or over 20 fields so my method would not work.
Is there a way to code this so it seaches the string and picks out the values withing the second occurance of "-" and before ","?
Attached is the Bartender file and the Text file I am testing with.
Please let me know. I don't have much string manupulation coding experience with VB or VBScripting.
Thank you for your help.
Josh
[/quote]
I just wanted to add I am using BarTender 9.4 SR3 incase this helps any.
Josh0 -
There is probably a better way to do this -
If your data string (the full one) is named '[i]data[/i]', the following line of VBS code will always return the value between the second - in a string, and the next comma:
[i]mid([b]data[/b],InStr(InStr([b]data[/b], "-")+1, [b]data[/b], "-")+1, (InStr((InStr(InStr([b]data[/b], "-")+1, [b]data[/b], "-")),[b]data[/b],",")-1)-(InStr(InStr([b]data[/b], "-")+1, [b]data[/b], "-")))[/i]0 -
[quote name='nRyder' timestamp='1334151255' post='2180']
There is probably a better way to do this -
If your data string (the full one) is named '[i]data[/i]', the following line of VBS code will always return the value between the second - in a string, and the next comma:
[i]mid([b]data[/b],InStr(InStr([b]data[/b], "-")+1, [b]data[/b], "-")+1, (InStr((InStr(InStr([b]data[/b], "-")+1, [b]data[/b], "-")),[b]data[/b],",")-1)-(InStr(InStr([b]data[/b], "-")+1, [b]data[/b], "-")))[/i]
[/quote]
Thank you for your feedback. I will try this out. Only other question I had is there a way to do this dynamically. Meaning I won't always have 3 values with - - within the comments. I may have only 1 or many more than 3.
Example.
File1 - has data string with 9999-2222-1.01,9999-2222-1.02
File2 - has data string with 9999-6666-4.01,9999-6666-4.02,9999-6666-4.03,9999-6666-4.04,9999-6666-4.05,9999-6666-4.06,9999-6666-4.07
File3 - has data string with 9999-5555-12.01,9999-5555-12.02,9999-6666-12.03,9999-6666-12.04
As you can see there are various data in the string so I wasn't sure if there was a way to dynamically do this to accomidate for variations.
Let me know when you get a chance.
Thank you for your help.
Josh0 -
Oh, sorry. I misunderstood the first question. My line of code will only pull the data between the second[b] '-'[/b] and the next [b]','[/b], but not any of the other values that are between dashes and commas. There may be a way to do what you're looking for though, but I'm confused by what output you'd want.
Would the output line you'd want be:
File1 - "1.01,1.02"
File2 - "4.01,4.04,4.03,4.04,4.05,4.06,4.07"
File3 - "12.01,12.02,12.03,12.04"
Each of those outputs all in one printed field on the label?0 -
nRyder... I appreciate your time helping me with this.
My goal is this. I am using Bartender to print labels from XML files from created from an Oracle EBS system. 1 file will print a label worth of data using a Bartender format specific to user requirements.
So for my example with 3 files it would be three different labels with different values. I in the XML file it has Quantity which counts for duplicate copies.
Back to the example I gave to you.
File1 - has data string with 9999-2222-1.01,9999-2222-1.02
File2 - has data string with 9999-6666-4.01,9999-6666-4.02,9999-6666-4.03,9999-6666-4.04,9999-6666-4.05,9999-6666-4.06,9999-6666-4.07
File3 - has data string with 9999-5555-12.01,9999-5555-12.02,9999-6666-12.03,9999-6666-12.04
I would Expect 3 different labels with output like this:
Label1 = 1.01,1.02
Label2 = 4.01,4.02,4.03,4.04,4.05,4.06,4.07
Label3 = 12.01,12.02,12.03,12.04
Since the rest of the data on the label is mapped to use this one Bartender Template. I am hoping to use the same VBscript to parse the values for each label dynamically to produce the output above.
Also I had some issue running with your sample code. I attached a updated Bartender format COIL_STRING_TEST.btw that shows the error for the field & vbscript I am working with. I am not sure what is incorrect.
Please review and let me know what you think. I greatly appreciate any help with this as I been trying to get something to work as I explained above.
Josh0 -
First of all, I know nothing about Oracle or XML print files, so I can really only help with the VBS side of things. That said:
See my attachment. I have made 2 data fields. One is the raw data with your full data string - the other is on the label and has a multi-line VBS script in it to pull the data you're looking for. The script assumes that there is only a period in places where there is data you want, that the data ends with a comma unless it's the last data in the string, and that there are 2 dashes between the end of one data and the start of the next
[attachment=200:example.btw]
Script:
[code]'Number of data sets: '
arrCount = Split(comments, ".", -1, 1)
numdata = UBound(arrCount)
'Find the position of the first data: '
currentposition = InStr(InStr(comments, "-")+1, comments, "-")
nextcomma = InStr(currentposition, comments, ",")
if (nextcomma = 0) then 'account for only one data set '
result = mid(comments, currentposition+1, Len(comments) - currentposition)
else
result = mid(comments, currentposition+1, nextcomma - currentposition - 1)
end if
'Iterate through data '
i = 1
Do While (i < numdata)
currentposition = InStr(InStr(nextcomma + 1, comments, "-")+1, comments, "-")
nextcomma = InStr(currentposition, comments, ",")
if (nextcomma = 0) then 'account for the last data record '
result = result & "," & mid(comments, currentposition+1, Len(comments) - currentposition)
else
result = result & "," & mid(comments, currentposition+1, nextcomma - currentposition - 1)
end if
i = i + 1
Loop
Value = result[/code]0 -
[quote name='nRyder' timestamp='1334178893' post='2194']
First of all, I know nothing about Oracle or XML print files, so I can really only help with the VBS side of things. That said:
See my attachment. I have made 2 data fields. One is the raw data with your full data string - the other is on the label and has a multi-line VBS script in it to pull the data you're looking for. The script assumes that there is only a period in places where there is data you want, that the data ends with a comma unless it's the last data in the string, and that there are 2 dashes between the end of one data and the start of the next
[attachment=200:example.btw]
Script:
[code]'Number of data sets: '
arrCount = Split(comments, ".", -1, 1)
numdata = UBound(arrCount)
'Find the position of the first data: '
currentposition = InStr(InStr(comments, "-")+1, comments, "-")
nextcomma = InStr(currentposition, comments, ",")
if (nextcomma = 0) then 'account for only one data set '
result = mid(comments, currentposition+1, Len(comments) - currentposition)
else
result = mid(comments, currentposition+1, nextcomma - currentposition - 1)
end if
'Iterate through data '
i = 1
Do While (i < numdata)
currentposition = InStr(InStr(nextcomma + 1, comments, "-")+1, comments, "-")
nextcomma = InStr(currentposition, comments, ",")
if (nextcomma = 0) then 'account for the last data record '
result = result & "," & mid(comments, currentposition+1, Len(comments) - currentposition)
else
result = result & "," & mid(comments, currentposition+1, nextcomma - currentposition - 1)
end if
i = i + 1
Loop
Value = result[/code]
[/quote]
No problem about not knowing Oracle & XML. I have that part working without questions. I will review your sample and let you know how I make out.
Thanks again for your help.
Josh0 -
[quote name='jkuzenka' timestamp='1334194930' post='2197']
No problem about not knowing Oracle & XML. I have that part working without questions. I will review your sample and let you know how I make out.
Thanks again for your help.
Josh
[/quote]
Hi nRyder...
I implemented your code sample you gave to me and it works well. Only thing I had to add to it was when there wasn't any data it would fail showing <script Error> in the field with the Multi-Line VBScript.
I know how to handle that so I should be all set thank you for your help with this item. I believe I am all set to incorporate this into my labeling once I have finished testing the different scenarios.
Josh0
Please sign in to leave a comment.
Comments
8 comments