[AccessD] Extract data from Single field

[Stuart McLachlan]

I'd go with a slight variation to that (I have variants :-) )

Complete code including a test sub:

Option Compare Database
Option Explicit

Dim AllData() As String 

Sub ParseData(strData As String)
Dim recs() As String
Dim ARecord() As String
Dim x As Long
Dim y As Long

'Get records into local Array
recs() = Split(strData, ";")

'Get fields into Global Array
ReDim AllData(UBound(recs()), 4)
For x = 0 To UBound(recs())
  ARecord() = Split(recs(x), ":")
 If UBound(ARecord()) > -1 Then 'Deal with possible trailing ";" in string
    For y = 0 To 4
        AllData(x, y) = ARecord(y)
    Next
  End If
Next
End Sub

Sub Test()
Dim x, y As Long
ParseData "[TransactionDate1]:[Merchant1]:[Amount1]:[ARN1]:[Note1];[TransactionDate2]:[ 
Merchant2]:[Amount2]:[ARN2]:[Note2];"
For x = 0 To UBound(AllData())
    For y = 0 To 4
       Debug.Print AllData(x, y),
    Next
       Debug.Print
Next

End Sub



On 8 Jun 2017 at 13:00, David Emerson wrote:

> Thanks Jack, worth considering. 
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> Of jack drawbridge Sent: Thursday, 8 June 2017 12:14 p.m. To: Access
> Developers discussion and problem solving Subject: Re: [AccessD]
> Extract data from Single field
> 
> David,
> 
> Not sure what you need, but here is an approach (if I understand the
> requirement) that may be useful.
> 
> Sub david()
>     Dim x As String
>     Dim i As Integer, j As Integer
>     Dim rec As Variant  'terminated with ;
>     Dim fld As Variant  'terminated with :
> 
>     'put the data template into a string
>     x =
> "[TransactionDate1]:[Merchant1]:[Amount1]:[ARN1]:[Note1];[TransactionD
> ate2]: [Merchant2]:[Amount2]:[ARN2]:[Note2];"
>     rec = Split(x, ";")
>     ' act on the rec array
>     For i = LBound(rec) To UBound(rec)
>         fld = Split(rec(i), ":")
>         For j = LBound(fld) To UBound(fld)
>             Debug.Print "record (" & i & " , field " & j; ") is " &
>             fld(j)
>         Next j
>     Next i
> End Sub
> 
> result is
> 
> record (0 , field 0) is [TransactionDate1] record (0 , field 1) is
> [Merchant1] record (0 , field 2) is [Amount1] record (0 , field 3) is
> [ARN1] record (0 , field 4) is [Note1] record (1 , field 0) is
> [TransactionDate2] record (1 , field 1) is [Merchant2] record (1 ,
> field 2) is [Amount2] record (1 , field 3) is [ARN2] record (1 , field
> 4) is [Note2]
> 
> Good luck.
> 
> On Wed, Jun 7, 2017 at 7:31 PM, David Emerson <newsgrps at dalyn.co.nz>
> wrote:
> 
> > Hi Bill,
> >
> > I think the data will not actually include the open and close
> > brackets so that shouldn't be a problem.
> >
> > Since the notes are the last field I just need to deal with ;
> >
> > Here is what I came up with so far (since there are so few fields
> > and some are numbers, some are text, I didn't try to loop individual
> > fields)
> >
> >             strTransactions = rstDispute!MergedTransactions
> >             Do While Len(strTransactions) > 1
> >                 strSQL = "INSERT INTO dbo.DisputeTransaction 
> > (TransactionDate, TransactionMerchant, TransactionAmount, 
> > TransactionARN, TransactionNotes ) "
> >                 strSQL = strSQL & "VALUES ("'"
> >                 strSQL = strSQL & 
> > basConvertDate(Left$(strTransactions,
> > InStr(strTransactions, ":") - 1)) & "','" 'TransactionDate
> >                 strTransactions = Right$(strTransactions,
> > Len(strTransactions) - InStr(strTransactions, ":"))
> >                 strSQL = strSQL & Left$(strTransactions, 
> > InStr(strTransactions, ":") - 1) & "'," 'TransactionMerchant
> >                 strTransactions = Right$(strTransactions,
> > Len(strTransactions) - InStr(strTransactions, ":"))
> >                 strSQL = strSQL & Left$(strTransactions, 
> > InStr(strTransactions, ":") - 1) & ",'" 'TransactionAmount
> >                 strTransactions = Right$(strTransactions,
> > Len(strTransactions) - InStr(strTransactions, ":"))
> >                 strSQL = strSQL & Left$(strTransactions, 
> > InStr(strTransactions, ":") - 1) & "','" 'TransactionARN
> >                 strTransactions = Right$(strTransactions,
> > Len(strTransactions) - InStr(strTransactions, ":"))
> >                 strSQL = strSQL & Left$(strTransactions, 
> > InStr(strTransactions, ";") - 1) & "'," 'TransactionNotes
> >                 strTransactions = Right$(strTransactions,
> > Len(strTransactions) - InStr(strTransactions, ";"))
> >                 Set cmd = basGetCommand(strSQL, adCmdText)
> >                 cmd.Execute
> >                 Set cmd = Nothing
> >             Loop
> >
> > Regards
> >
> > David
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On
> > Behalf Of Bill Benson Sent: Thursday, 8 June 2017 10:59 a.m. To:
> > Access Developers discussion and problem solving Subject: Re:
> > [AccessD] Extract data from Single field
> >
> > What will you do if the Notes contain character like [ or ] or ; or
> > :
> >
> > ?
> >
> > What mind of delimiter can be counted on with data like that?
> >
> > Hi Listers,
> >
> > I have some data that is currently stored in a single field in the
> > following format:
> >
> > [TransactionDate1]:[Merchant1]:[Amount1]:[ARN1]:[Note1];[
> > TransactionDate2]:[
> > Merchant2]:[Amount2]:[ARN2]:[Note2];etc
> >
> > The ";" separates records and the ":" separates the data within a
> > record.
> >
> > Before I start reinventing the wheel, does anyone have a code
> > snippet that will extract the fields?
> >
> > Regards
> > David Emerson
> > Dalyn Software Ltd
> > Wellington, New Zealand
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>