Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

Parsing data

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
hwassinger
Asked:
hwassinger
2 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
If the data is consistent as far as the delimiter, Split() is far simpler:



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

Jim.
0
 
hwassingerAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Gustav BrockCIOCommented:
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now