none
Delimiting data within a field into multiple records in another table in Access RRS feed

  • Question

  • Hello,

    I have looked at a few articles regarding delimiting data.

    I have this sample of data below.

    Field  1|Field 2

     1            John, Jack, Jill, Mary, Phil

    The steps I want to perform are the following:

    1. Delimit the commas within the field

    2. Transpose the data in another table.

    Field 1| Field 2

    1,        John

    2,        Jack

    3,        Jill

    4,        Mary

    5,        Phil

    Can anyone provide me some solutions on how to code this in VBA using the Split and Replace function prior to

    inputting the transposed data in another table?

    Regards,

    JohnDBCTX


    jp

    Monday, November 27, 2017 6:08 AM

Answers

  • I got it solved again by myself.

    Here is the updated code snippet.    Hope this helps with others who may have been struggling with delimited data prior to transferring to another table.

    Regards,

    JohnDBCTX

    Option Compare Database
    Dim LString As String
    Dim LArray() As String
    Dim recSetX As Recordset
    Dim DBX As Database
    Dim LII As Long
    Dim strX As String
    
    Private Sub cmdDelimiter_Click()
    On Error GoTo OutOfRangeErr:
    
    
    Open "C:\Users\johnd\Documents\Delimited File One.txt" For Output As #1
    
    
    LString = Me.Field4.Value
    
    LArray = Split(LString, ",") 'comma delimiter
    For LII = 0 To 10
        Print #1, LArray(LII)
    Next
    
    Close #1
    'Suppress Out of range errors
    
    OutOfRangeErr:
    If Err.Number = 9 Then
        Resume Next
    End If
    
    End Sub
    
    Private Sub cmdOutputDelimitedData_Click()
    Open "C:\Users\johnd\Documents\Delimited File One.txt" For Input As #2
    
    Set DBX = CurrentDb
    Set recSetX = DBX.OpenRecordset("tblDelimitedData")
    
    
    Do Until EOF(2)
        recSetX.AddNew
        Input #2, strX
        recSetX.Fields("DelimitData").Value = strX
        recSetX.Update
    Loop
    Me.Refresh
    
    Close #2
    
    End Sub
    

    ...And here is the solution screenshot


    jp

    • Marked as answer by JohnDBCTX Monday, November 27, 2017 11:41 AM
    Monday, November 27, 2017 11:29 AM

All replies

  • Maybe the screenshot below would help.


    jp

    Monday, November 27, 2017 6:34 AM
  • ...And here is the code snippet.

    Option Compare Database
    Dim DBX As Database
    Dim RecSet As Recordset
    Dim txtDelimit As Long
    
    
    Private Sub cmdDelimitData_Click()
        Open "C:\Users\johnd\Documents\Delimited File One.txt" For Output As #1
        Set DBX = CurrentDb
        Set RecSet = DBX.OpenRecordset("tblPreDelimitedData", dbOpenDynaset)
        txtDelimit = Split(RecSet.Fields(0).Value, ",") 'This line compiles an error
        
        Me.PreDelimitedListOfNames.SetFocus
        Do Until RecSet.EOF
             RecSet.Edit
             RecSet.Fields(1).Value = txtDelimit
             RecSet.MoveNext
        Loop
        RecSet.Close
        
        Close
        
    End Sub
    

    Regards,

    JohnDBCTX


    jp

    Monday, November 27, 2017 6:46 AM
  • I got it solved again by myself.

    Here is the updated code snippet.    Hope this helps with others who may have been struggling with delimited data prior to transferring to another table.

    Regards,

    JohnDBCTX

    Option Compare Database
    Dim LString As String
    Dim LArray() As String
    Dim recSetX As Recordset
    Dim DBX As Database
    Dim LII As Long
    Dim strX As String
    
    Private Sub cmdDelimiter_Click()
    On Error GoTo OutOfRangeErr:
    
    
    Open "C:\Users\johnd\Documents\Delimited File One.txt" For Output As #1
    
    
    LString = Me.Field4.Value
    
    LArray = Split(LString, ",") 'comma delimiter
    For LII = 0 To 10
        Print #1, LArray(LII)
    Next
    
    Close #1
    'Suppress Out of range errors
    
    OutOfRangeErr:
    If Err.Number = 9 Then
        Resume Next
    End If
    
    End Sub
    
    Private Sub cmdOutputDelimitedData_Click()
    Open "C:\Users\johnd\Documents\Delimited File One.txt" For Input As #2
    
    Set DBX = CurrentDb
    Set recSetX = DBX.OpenRecordset("tblDelimitedData")
    
    
    Do Until EOF(2)
        recSetX.AddNew
        Input #2, strX
        recSetX.Fields("DelimitData").Value = strX
        recSetX.Update
    Loop
    Me.Refresh
    
    Close #2
    
    End Sub
    

    ...And here is the solution screenshot


    jp

    • Marked as answer by JohnDBCTX Monday, November 27, 2017 11:41 AM
    Monday, November 27, 2017 11:29 AM