Solved

Parsing data

Posted on 2014-03-26
4
221 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 5 54
Can not open the Access Help ? 3 53
Dynamically Reorder List Box 4 38
How to use a single FE file for users with different access versions? 6 16
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

770 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