Solved

vbscript required for data transform task in SQL

Posted on 2004-10-20
14
161 Views
Last Modified: 2010-05-02
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
Comment
Question by:Tacobell777
  • 8
  • 5
14 Comments
 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 12368170
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
 
LVL 17

Author Comment

by:Tacobell777
ID: 12375571
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
 
LVL 12

Accepted Solution

by:
BobLamberson earned 500 total points
ID: 12378066
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
 
LVL 17

Author Comment

by:Tacobell777
ID: 12379362
hi, i'll give it a try, but dont you have regex in VBScript?
0
 
LVL 17

Author Comment

by:Tacobell777
ID: 12379364
ps. I will try it on monday..
0
 
LVL 12

Expert Comment

by:BobLamberson
ID: 12381469
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
 
LVL 17

Author Comment

by:Tacobell777
ID: 12385801
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 17

Author Comment

by:Tacobell777
ID: 12478681
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
 
LVL 17

Author Comment

by:Tacobell777
ID: 12531478
I noticed the line
 If numb = "79" Then

there does not always have to be a number 79
0
 
LVL 12

Expert Comment

by:BobLamberson
ID: 12533365
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
 
LVL 17

Author Comment

by:Tacobell777
ID: 12537271
I will never know the number, it is an automated import.
0
 
LVL 12

Expert Comment

by:BobLamberson
ID: 12540708
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
 
LVL 17

Author Comment

by:Tacobell777
ID: 12541571
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
 
LVL 12

Expert Comment

by:BobLamberson
ID: 12551085
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now