Solved

Parsing data

Posted on 2014-03-26
4
223 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
[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
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 50

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access Weekly query 11 42
Access Query Top 20 from subquery 11 40
Using Access multi Listbox to pass criteria to a query 9 29
SQL Server Compression Decision 5 42
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…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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 …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

756 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