Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

vbscript required for data transform task in SQL

I need some VBScript for a data transform task in a DTS package.

the data I have looks like the following:

71. Progress status of software installation 72. Print cartridges installation 73. Loading standard size paper 74. Changing print settings (eg. type of paper, size) 75. Inserting memory card (if applicable) 76. Setting up the fax function (if applicable) 77. Using scanner function 78. Clarity on control panel operation 79. Other (... Please Specify Q15C5)

I need to parse it and get the number in a variable, and I need the text behind it in a variable.

0
Tacobell777
Asked:
Tacobell777
  • 8
  • 5
1 Solution
 
jimbobmcgeeCommented:
Is the data posted above written exactly as above (i.e. is that the flat file), or is it in a table, a delimeted file, etc?  For example, it will be far easier for you to parse the file if it actually reads:

    71|Progress status of software installation
    72|Print cartridges installation
    73|Loading standard size paper
    etc...

Regards
J.
0
 
Tacobell777Author Commented:
no thats actually the contents of one column, each row has this info.. I know its bad, but I did not create the files or format..
0
 
Bob LambersonSoftware EngineerCommented:
Hi Tacobell777,

Try this code in a form and see if this is what you are after.

Private Sub Form_Load()
Dim numb As String
Dim desc As String
Dim str As String
Dim parseStr As String
Dim lengthToDot As Integer
Dim clipStr As String
Dim alternateLengthToDot As String
Dim lastPosition As Integer
Dim strLength As Integer


str = "71. Progress status of software installation 72. Print cartridges installation 73. Loading standard size paper 74. Changing print settings (eg. type of paper, size) 75. Inserting memory card (if applicable) 76. Setting up the fax function (if applicable) 77. Using scanner function 78. Clarity on control panel operation 79. Other (... Please Specify Q15C5)"
parseStr = str

lengthToDot = 0
Do
   lengthToDot = InStr(lengthToDot + 1, parseStr, ". ")
   numb = Mid(parseStr, lengthToDot - 2, 2)
    If IsNumeric(numb) Then
      alternateLengthToDot = lengthToDot
   parseStr = Mid(parseStr, 1, alternateLengthToDot - 1) & "|" & Mid(parseStr, lengthToDot + 2)
   End If
'   Debug.Print parseStr
   If numb = "79" Then
      parseStr = parseStr & "|"
      Exit Do
   End If

Loop

lastPosition = 1
Do
   lastPosition = InStr(lastPosition + 1, parseStr, "|")
   numb = Mid(parseStr, lastPosition - 2, 2)
   If numb = "79" Then
      desc = Mid(parseStr, lastPosition + 1, InStr(lastPosition + 1, parseStr, "|") - (lastPosition + 1))
   Else
      desc = Mid(parseStr, lastPosition + 1, InStr(lastPosition + 1, parseStr, "|") - (lastPosition + 4))
   End If
   Debug.Print "Number : " & numb
   Debug.Print "  Description : " & desc
   Debug.Print "_______________________"
      If numb = "79" Then Exit Do
Loop

End Sub
Bob
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Tacobell777Author Commented:
hi, i'll give it a try, but dont you have regex in VBScript?
0
 
Tacobell777Author Commented:
ps. I will try it on monday..
0
 
Bob LambersonSoftware EngineerCommented:
Tacobell777,
VB script can use Regular Expressions, but not sure how that would help here, unless I misunderstand what you are trying to accomplish.
see http://etext.lib.virginia.edu/helpsheets/regex.html

Bob
0
 
Tacobell777Author Commented:
I just think regex will be easier syntax to use.. I'm not complaining, anything that will do the trick is fine, just wondering ;-)
0
 
Tacobell777Author Commented:
I just wanted to let you know I have not forgotten about this question, just have not had time at work to try out your suggestion, please bare with me.
0
 
Tacobell777Author Commented:
I noticed the line
 If numb = "79" Then

there does not always have to be a number 79
0
 
Bob LambersonSoftware EngineerCommented:
will you know the last number in your string and will it be consistant? if so change 79 to whatever the last number is.

If that won't work, change the do  while statement to do while numb > 0 and set numb each loop like;
        numb=len(parse string)
       
0
 
Tacobell777Author Commented:
I will never know the number, it is an automated import.
0
 
Bob LambersonSoftware EngineerCommented:
Will the alternative work? all you need is some way for the do loop to know when it has readed the end.

change the do  while statement to              do while numb > 0 and set the numb variable each loop like;
        numb=len(parse string)
this way the numb variable gets smaller with each record and will eventually = 0
0
 
Tacobell777Author Commented:
i've gone over the script and it looks familiar and I'm sure it will work, even so it will now be a while before Im back on the project where I need to implement this, so I awarded the points to you.

thanks for your help.
0
 
Bob LambersonSoftware EngineerCommented:
You are welcome. If you still have trouble with it when you get a chance to test, just post a question and if I don't respond, someone will be able to help. Thanks for the points.

Bob
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now