Code for difficult data translation

[NXMold]

I have a series of MEMO fields that contain a running history of changes. I need to convert this memo text into proper records in a new table.

EXISTING MEMO EXAMPLE:

\-- 4/21/2009 10:52:01 AM; John D.;
--[CustomerID]; null; 289574;
--[Contact]; null; Jane Doe;
--[OrdEntryDate]; null; 9/17/2008;
--[PONumber]; null; 97006653;
--[QuoteNumber]; null; 123456R1;
--[Customer]; AbC; ABC Sales and Services;
--[ExistingComponents]; null; No;
\-- 5/29/2009 3:40:35 PM; John D.;
--[ProjMgrNotes]; Ship 5-27-09 instead of 5-28-09.; Tool will come back for another E/C.;
\-- ....etc


NEW TABLE: tblUpdates
User
DateofChange
Field
OldValue
NewValue

In the memo "\--" signifies the beginning of a new record, then the date/time and user appear on that line. After that, each field that was modified starts on a new line prefixed " --" followed by field, old, and new value. There can be any number of changed fields per 'record' in the memo. Semicolons are used as a delimiter.

I need to loop while stepping through each line of the huge memo text
when "\--" is encountered, reset DateofChange and User variables.
When " --" is encountered, insert a new record, set all five field values, and save.

I know there are advanced ways of working with strings using split and instr because I have seen those commands here on the forum, but I cant find split in the help files. Can someone give some tips on the best way to break up this memo?

Using access 2000.

 

[joelflorendo]

The split function creates an array of string values based on a delimiter specified.

So

recArr = Split(memoText,"\--")

will give you an array of your records.

You could then move through the resulting array like this:

for i=0 to Ubound(recArr)
debug.print recArr(i) 'prints the text in the first record
next i

looks like you'll have to use the split function a couple more times to parse out the text for each record (Split on "--" then on ";")

 

[Remou]

I reckon you may need several splits, for example:

Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset

Set rsIn=CurrentDB.OpenRecordset("SELECT MemoField FROM MemoTable")

Set rsOut=CurrentDB.OpenRecordset("SELECT * FROM tblUpdates")

Do While Not rsIn.EOF
   strMemoRec=Split(rsIn!MemoField,"\--")
   strRec=vbNullstring

   For i=0 To UBound(strMemoRec)

     If Left(Trim(strMemoRec(i)),2)="--" Then
        'Field entry
        strRec=Split(Mid(Trim(strMemoRec(i)),3),":")

        For j=0 To UBound(strRec)
           rsOut![Field]=strRec(0)
           rsOut!OldValue=strRec(1)
           rsOut!NewValue=strRec(2)
        Next
     Else
        'New record
        rsOut.Update
        rsOut.AddNew
        strRec=Split(strMemoRec(i),":")
        rsOut!DateofChange=strRec(0)
        rsOut!User=strRec(1)
     End If
   Next
   rsIn.MoveNext
Loop

Or there abouts.

http://lessthandot.com

 

[NXMold]

Fantastic, I can make that work! Thanks.