Solved

Parsing data

Posted on 2014-03-26
4
216 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
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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

11 Experts available now in Live!

Get 1:1 Help Now