Solved

Parsing data

Posted on 2014-03-26
4
222 Views
Last Modified: 2014-03-26
I have a field that will contain a variety of comma separated data. I want to parse it into discrete values.

Searching the web found me the code below, but Im struggling to make it work.

Sample data would look like

01:00,05:00,09:00                  
01:00
01:00,05:00,09:00,13:00 etc

Description 
Converts a delimited string to an array of words 

Returns 
number of words 

Example 
Sub StringToArray () 
' ============================================== 
' Example code for StringToArray() 
' ---------------------------------------------- 
' Creates a delimited string of values, converts 
' it to an array and displays the array 
' contents in the debug window. 
' ============================================== 
Dim strTmp As String 
Dim lngCount As Long 
Dim lngCounter As Long 
Dim aNames() As String 

' Load a string 
strTmp = "apples,oranges,pears,kiwis" 

' Convert the string to an array 
lngCount = StringToArray(strTmp, aNames(), ",") 

' Display the contents of the array 
For lngCounter = 1 To lngCount 
Debug.Print aNames(lngCounter) 
Next lngCounter 

End Sub 



Function StringToArray(strIn As String, arrIn() As String, chrDelimit As String) As Long 
' Comments : Converts a delimited string to an array of words 
' Parameters: strIn - string to convert 
' arrIn - array of strings (1-based) 
' chrDelimit - character used to delimit words in strIn 
' Returns : number of words 
' 
Dim intCounter As Integer 
Dim intWordCount As Integer 

intWordCount = CountDelimitedWords(strIn, chrDelimit) 
ReDim arrIn(1 To intWordCount) 

For intCounter = 1 To intWordCount 
arrIn(intCounter) = GetDelimitedWord(strIn, intCounter, chrDelimit) 
Next intCounter 

StringToArray = intWordCount 

End Function 

Function CountDelimitedWords(strIn As String, chrDelimit As String) As Integer 
' Comments : returns the number of words in a delimited string 
' Parameters: strIn - string to count words in 
' chrDelimit - character that delimits words in strIn 
' Returns : number of occurrences 
' 
Dim intWordCount As Integer 
Dim intPos As Integer 

intWordCount = 1 
intPos = InStr(strIn, chrDelimit) 

Do While intPos > 0 
intWordCount = intWordCount + 1 
intPos = InStr(intPos + 1, strIn, chrDelimit) 
Loop 

CountDelimitedWords = intWordCount 

End Function 


Public Function GetDelimitedWord(strIn As String, intIndex As Integer, chrDelimit As String) As String 
' Comments : returns word intIndex in delimited string strIn 
' Parameters : strIn - string to search 
' intIndex - word position to find (value of 0 or greater than word count is undefined.) 
' chrDelimit - character used as the delimter 
' Returns : nth word 
' 
Dim intCounter As Integer 
Dim intStartPos As Integer 
Dim intEndPos As Integer 
Dim strTmp As String 

intCounter = 1 
intStartPos = 1 

If Left$(strIn, 1) = chrDelimit Then 
' If the first character is the delimiter, then consider the first 
' delimited word to be blank. Recursively call this function to get 
' all other than 1. 
If intIndex = 1 Then 
strTmp = "" 
Else 
strTmp = GetDelimitedWord(Right(strIn, Len(strIn) - 1), intIndex - 1, chrDelimit) 
End If 

GetDelimitedWord = strTmp 

Else 

For intCounter = 2 To intIndex 
intStartPos = InStr(intStartPos, strIn, chrDelimit) + 1 
Next intCounter 

intEndPos = InStr(intStartPos, strIn, chrDelimit) - 1 

If intEndPos <= 0 Then 
intEndPos = Len(strIn) 
End If 

GetDelimitedWord = Mid$(strIn, intStartPos, intEndPos - intStartPos + 1) 

End If 

End Function

Open in new window

0
Comment
Question by:hwassinger
4 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 350 total points
ID: 39956241
If the data is consistent as far as the delimiter, Split() is far simpler:



    Dim arr() As Variant
   
    arr = Split("data here", ",")

Jim.
0
 

Author Comment

by:hwassinger
ID: 39956293
Jim - This gives me a type mismatch


Public Function toa()
Dim arr() As Variant
arr = Split("01:00,05:00,09:00,13:00", ",")
End Function

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39956305
you can use this simpler function
Function fnParseInfo(vString As Variant, idx As Integer, Optional Delimiter As String = ",") As String

   Dim myArray() As String
   myArray = Split(vString, Delimiter)

   If idx < 0 Or idx > UBound(myArray) + 1 Then

      fnParseInfo = ""

  Else

      fnParseInfo = myArray(idx - 1)

   End If

End Function

Open in new window



to use in a query

select   fnParseInfo([FieldName],1,","), fnParseInfo([FieldName],2,",")
from your table
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 150 total points
ID: 39956655
Helping Jim out ...
This is how:

Public Function toa()
  Dim arr As Variant
  arr = Split("01:00,05:00,09:00,13:00", ",")
  ' Demo.
  Debug.Print arr(0), arr(1), arr(2)  ' etc.
End Function

/gustav
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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