• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 495
  • Last Modified:

Extracting values connected with hyphens

Hi Experts,

I am trying to figure out a way of extracting integer values on both sides of a hyphen (ex: 7-14) from the string in recordset field, as I need to use the values in for - to loops, but so far without success.

To be more specific the aim is to generate a number of record copies corresponding to the two values - ex: like 10 instances of a record where the field contains ex: 6-15 or 4 instances if 17-20 are found. As the field sometimes contains more than one set of values separated by semicolon (ex: 6-15; 17-20) it should also be possible to loop through both (or more) so i need to know how many pairs of number-hyphen-number are within the string.

For clarification: the numbers I try to get at are weeknumbers written as text, and  I need to dig out every one of the weeks just entered as hyphen pairs in the string.

I get at the data by connecting to an MsExcel file.
Heres the code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252" LCID="1030"%>
<%option explicit%>
<%session.lcid=1030%>

<head>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<title>Scrape XLS</title>


</head>
<body>

      <%         
Dim OpenFile,i  
Dim Conn,objRec,strSQL  
Dim locname

OpenFile = "F14_1584.xls"  
Set Conn = Server.CreateObject("ADODB.Connection")  
 
'*** Connect to Excel ***'  
Conn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_  
"Excel 8.0; DBQ=" & Server.MapPath(OpenFile) & "; " 
 
'*** Select Sheet ***'  
strSQL = "SELECT * FROM [Sheet$]"  
Set objRec = Conn.Execute(strSQL)  
%>  
<table border="0">  
<tr align="left">  
          <th scope="col" width="120px">Lokale</th>
          <th scope="col" width="80px">Dag</th>
          <th scope="col" width="50px">fra</th>
          <th scope="col" width="50px">til</th>
          <th scope="col" width="50px">var.</th>
          <th scope="col" width="90px">res. u.</th>
          <th scope="col" width="250px">Name</th>
          <th scope="col" width="550px">Description</th>
</tr>
<%
Do While Not objRec.EOF

locname = objRec("Allocated location name").Value

dim begh
dim endh
dim scw

begh = left(objRec.Fields("Scheduled Start Time").Value, 5)
endh = left(objRec.Fields("Scheduled End Time").Value, 5)
scw = objRec.Fields("Scheduled Weeks").Value
%>

        <tr valign="top">  
        <td><%=locname%></td>
        <td><%=objRec.Fields("Scheduled Days").Value%></td>  
        <td><%=begh%></td>  
        <td><%=endh%></td>  
        <td><%=objRec.Fields("Duration").Value%></td>

        <!-- this is the field in question -->
        <td><%=scw%></td>  

        <td><%=objRec.Fields("Name").Value%></td>  
        <td><%=objRec.Fields("Description").Value%></td>  
        </tr>  

<%  
objRec.MoveNext  
loop  
%>  
</table>  
<%  
objRec.Close  
Conn.Close  
Set objRec=Nothing  
Set Conn=Nothing  
%>  
</body>
</html>



Does someone have a tip or two?

Best regards
MortenWB
0
MortenWB
Asked:
MortenWB
  • 2
2 Solutions
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
i'm not sure what your exact output is supposed to look like but if I understand you correctly, you're looking for a way extract a substring "3-7" out of a longer string and break out those individuals numbers. hopefully I understand that right, it's still early and I haven't had my morning caffeine :)

dim strOfNumRanges: strOfNumRanges = "1-4;2-9;13-22"

'-- put each range into an array element
arrOfNumRanges = Split( strOfNumRanges, ";")

'-- now loop through to isolate each number range
for each numberRange in arrNumRanges
    if numberRange <> "" then
        arrNumbers = Split( numberRange, "-" )

       '-- get each number
       num1 = arrNumbers( 0 )
       num2 = arrNumbers( 1 )

       '-- do whatever, write it out to the screen or you can loop through using those values
       Response.Write "<td>" & num1 & "</td><td>" & num2 & "</td>"

    end if
next

Open in new window

0
 
John_VidmarCommented:
If you are using VBScript then I would use the Split-function against the semi-colon, to get an array of ranges.  Loop through that array, using Split-function against the dash-symbol to get from/to values.

If you are using SQL then I would dynamically build a where-clause using Replace-function:
1.      prefix your string with AND fieldname between
2.      replace semicolon with AND fieldname between
3.      replace dash with AND
declare	@ranges	varchar(100)
,	@where	varchar(100)

set	@ranges	= '6-15; 17-20'

set	@where	= 'fieldname between ' + replace(replace(@ranges,';' and fieldname between'),'-',' and ')

Open in new window

0
 
MortenWBAuthor Commented:
Thank you both for responding - I will test your solutions, but have to leave office now - I'll return later.

Best regards
MortenWB
0
 
MortenWBAuthor Commented:
Hi Big Monty and John,

yes, you both pointed me in the right direction, and I am now in progress again:)

Big Monty - your solution fits into my VBscript experience, so thats what I'm using, and John_Vidmar - I use some aspects of Your idea of a WHERE clause into my SQL - although I'm unfamiliar with the script language You use.

Thank you both for your help
Best regards
Morten B
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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