Parsing A Delimited Text or Memo Field
Sometimes the developer has little control on the format of data for "migration" applications. Occasionally, files from mainframes or other systems will use a single text string to store multiple occurrances of data, each occurrance delimited by characters such as commas, pipes or spaces. The example below shows how to use the "Split" function to separate out each substring within a delimited string. In the example below, assume that "Field1" represents the social security number, "Field2" a name, and the next field each zip code where the individual has lived. The pipe character is used as the delimiter in this example.
A record set might look something like this:
554778569 John Doe |91440|87276|55569|95124|
613659990 Joey Smart |22189|50091|
The program would create six records in the following format:
554778569 John Doe 91440
554778569 John Doe 87276
554778569 John Doe 55569
554778569 John Doe 95124
613659990 Joey Smart 22189
613659990 Joey Smart 50091
Program Code
Option Compare Database
Option Explicit
' ***********************************************************************
' Explode Piped Data into Individual Records
' ***********************************************************************
Public Function ReformatPipedDataSplit()
Dim db As Database
Dim recIn As Recordset
Dim recOut As Recordset
Dim strParsedData() As String
Dim i As Long
' ***********************************************************************
' Open the Files
' ***********************************************************************
Set db = CurrentDb()
' ***********************************************************************
' Open the Files For The Division Open Orders
' ***********************************************************************
Set recIn = db.OpenRecordset("tblMyFileWithPipes")
Set recOut = db.OpenRecordset("tblMyExplodedFile")
' ***********************************************************************
' Loop Through and Create The Exploded File
' ***********************************************************************
Do
' ***********************************************************************
' Parse The Input File into multiple output records
' ***********************************************************************
If IsNull(recIn!MyMemoField) Then
GoTo Skip_Record
End If
strParsedData = Split(recIn!MyMemoField, "|")
For i = 0 To UBound(strParsedData)
If strParsedData(i) <> "" Then
recOut.AddNew
recOut!Field1 = recIn!Field1
recOut!Field2 = recIn!Field2
recOut!Exploded = strParsedData(i)
recOut.Update
End If
Next i
' ***********************************************************************
' Get the next record
' ***********************************************************************
Skip_Record:
recIn.MoveNext
Loop Until recIn.EOF
recIn.Close
recOut.Close
Set recIn = Nothing
Set recOut = Nothing
Set db = Nothing
End Function