Solved

Extracting values connected with hyphens

Posted on 2014-01-16
4
476 Views
Last Modified: 2014-01-17
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
Comment
Question by:MortenWB
  • 2
4 Comments
 
LVL 33

Accepted Solution

by:
Big Monty earned 400 total points
ID: 39785316
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
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 100 total points
ID: 39785388
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
 

Author Comment

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

Best regards
MortenWB
0
 

Author Closing Comment

by:MortenWB
ID: 39787916
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

776 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