?
Solved

Extracting values connected with hyphens

Posted on 2014-01-16
4
Medium Priority
?
488 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 33

Accepted Solution

by:
Big Monty earned 1600 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 400 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

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…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 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