Link to home
Start Free TrialLog in
Avatar of Tacobell777
Tacobell777

asked on

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.

Avatar of jimbobmcgee
jimbobmcgee
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Tacobell777
Tacobell777

ASKER

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..
ASKER CERTIFIED SOLUTION
Avatar of Bob Lamberson
Bob Lamberson
Flag of United States of America image

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
hi, i'll give it a try, but dont you have regex in VBScript?
ps. I will try it on monday..
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
I just think regex will be easier syntax to use.. I'm not complaining, anything that will do the trick is fine, just wondering ;-)
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.
I noticed the line
 If numb = "79" Then

there does not always have to be a number 79
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)
       
I will never know the number, it is an automated import.
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
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.
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