asked on

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>


<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

ASKER CERTIFIED SOLUTION

Big Monty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

John_Vidmar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MortenWB

ASKER

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

Best regards
MortenWB

MortenWB

ASKER

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