Solved

Extracting values connected with hyphens

Posted on 2014-01-16
4
472 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 32

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 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

17 Experts available now in Live!

Get 1:1 Help Now