How to Parse a Delimited String Into Individual Records (Split Functio

[Flo79]

I was recently trying to determine a way to parse a delimited string into individual records.

For example, when importing MS Outllok data into Access, contact categories are exported like this (Category 1; Category 2; Cateory 3 etc.) for each contact.

In order for one to more effectively use the above outlook data, it needs to be normalize or parsed into individual records.

So that

Record 1 Column 1: Jack Column 2: Category 1; Category 2


Becomes

Record 1 Column 1: Jack Column 2: Category 1
Record 2 Column 1: Jack Column 2: Category 2



I found my answer here:

http://www.mvps.org/access/strings/str0003.htm

The original code is I think by Dev Ashish, I modified the 2 functions needed slightly to give me the ability to specify the string delimiter more easily.

-----------------------------------------------------------

Public Function GetCSWord(ByVal s, Indx As Integer, Optional strdelimiter)
'Returns the nth word in a specific field.
Dim WC As Integer, Count As Integer
Dim SPos As Integer, EPos As Integer
If IsMissing(strdelimiter) Then strdelimiter = " "
WC = CountCSWords(s, strdelimiter)
If Indx < 1 Or Indx > WC Then
GetCSWord = Null
Exit Function
End If
Count = 1
SPos = 1
For Count = 2 To Indx
SPos = InStr(SPos, s, strdelimiter) + 1
Next Count
EPos = InStr(SPos, s, strdelimiter) - 1
If EPos <= 0 Then EPos = Len(s)
GetCSWord = Trim(Mid(s, SPos, EPos - SPos + 1))
End Function

-----------------------------------------------------------
Function CountCSWords(ByVal s, strdelimiter) As Integer

'Counts the words in a string that are separated by commas.
Dim WC As Integer, Pos As Integer
If VarType(s) <> 8 Or Len(s) = 0 Then
CountCSWords = 0
Exit Function
End If
WC = 1
Pos = InStr(s, strdelimiter)
Do While Pos > 0
WC = WC + 1
Pos = InStr(Pos + 1, s, strdelimiter)
Loop
CountCSWords = WC
End Function
-----------------------------------------------------------
For those less familiar with custom function here is how to make it work:

Paste the above code into a New MS Access Module

Now,

Assuming that you have Table (A), which contains the following:

Record 1 Column 1 = 23 Column 2 = a,b,c,d


I can get the first record in the series by typing the following function in a query:

----------------------------------------
GetCSWord([Name of Column 1],1, ",")
----------------------------------------
The result of the Above Function will be "a"

----------------------------------------
GetCSWord([Name of Column 1],2, ",")
----------------------------------------
The result of the Above Function will be "b"

Now, in order to transform (normalize) my data in a table that looks like this (TableA)

Record 1 Column 1 = 23 Column 2 = a,b,c,d

Into A Query that looks like this

Record 1 Column 1 = 23 Column 2 = a
Record 2 Column 1 = 23 Column 2 = b
Record 3 Column 1 = 23 Column 2 = c
Record 4 Column 1 = 23 Column 2 = d


I write a Union Query:


----------------------------------------------------------
SELECT [Column 1], GetCSWord([Column 2],1,",") AS Data
FROM TableA
WHERE (((GetCSWord([Column 2],1,",")) Is Not Null) AND (([TableA.Column 2]) Is Not Null))

UNION SELECT [Column 1], GetCSWord([Column 2],2,",") AS Data
FROM TableA
WHERE (((GetCSWord([Column 2],2,",")) Is Not Null) AND (([TableA.Column 2]) Is Not Null))

UNION SELECT [Column 1], GetCSWord([Column 2],3,",") AS Data
FROM TableA
WHERE (((GetCSWord([Column 2],3,",")) Is Not Null) AND (([[TableA.Column 2]]) Is Not Null))

UNION SELECT [Column 1], GetCSWord([Column 2],4,",") AS Data
FROM TableA
WHERE (((GetCSWord([Column 2],4,",")) Is Not Null) AND (([TableA.Column 2]) Is Not Null))
------------------------------------------------------------

Hope some of you will find the above helpful


Cheers

FGP

 

[PHV]

A shorter version (ac2k or above):

Public Function GetCSWord(ByVal s, Indx As Integer, Optional strdelimiter = " ")
'Returns the nth word in a specific field
On Error Resume Next
GetCSWord = Split(s, strdelimiter)(Indx - 1)
End Function