Split a String by Comma and Break it up into Rows (1 Viewer)

Reckoner

New member
Local time
Today, 03:20
Joined
Aug 25, 2016
Messages
5
I have a Table (Films) where I have a string of data separated by commas (Genre):

(Ignore the Dashes that indicates break between Columns)

Film - Year - Rating - Genre

Catch Me If You Can - 2002 - 9 - Biographical, Crime, Drama
Batman Begins - 2005 - 9 - Comic Book, Action, Adventure
Heat - 1995 - 8 - Crime, Drama
Ex Machina - 2015 - 8 - Sci-Fi, Psychological, Thriller

How can I put this one column into a new Table (called Genres) putting each part separated by a comma into a new Row like below:

Genre

Biographical
Crime
Drama
Comic Book
Action
Adventure
Crime
Drama
Sci-Fi
Psychological
Thriller

I'm wanting to do this so I can use the resulting table as a source for a Combo Box, this Combo Box will then be used to filter on the master Table while allowing wildcard results so partial matches are retrieved.
 
Last edited:

Ranman256

Well-known member
Local time
Today, 06:20
Joined
Apr 9, 2015
Messages
4,339
Attach the text file as a table (comma delim)
then run a query to create the table using that 1 column. (using unique values)
 

Reckoner

New member
Local time
Today, 03:20
Joined
Aug 25, 2016
Messages
5
Attach the text file as a table (comma delim)
then run a query to create the table using that 1 column. (using unique values)

All the Data is held within the Database, no files have been imported and new records are updated directly in the table. As such there is no text file.

Also as new Records are added new Genre's could appear so I'm looking for a Query / some Code which could be set to run on opening a Form so that those new Genre's can be picked up.
 

Reckoner

New member
Local time
Today, 03:20
Joined
Aug 25, 2016
Messages
5
I know I can run a Append Query to pull the Genre column into the Genre Table I have created but I can't work out how to split say "Action, Adventure, Drama" to 3 rows keeping all the values in 1 column.

I'm pretty sure I need some VBA to do this but haven't an idea where to start, any ideas anyone? Thanks in advance.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 06:20
Joined
Oct 17, 2012
Messages
3,276
I would do the following:

This sub will run through your Films table, split out the genres, and load them into a table called "Genres_Temp". (You need to make that table before you run this.)

Code:
Public Sub GetGenres()
 
Dim rs As DAO.Recordset
Dim rs_out As DAO.Recordset
Dim x As Long
Dim GenreList() As String
 
    Set rs = CurrentDb.OpenRecordset("SELECT Films.Genre FROM Films")
    Set rs_out = CurrentDb.OpenRecordset("Genres_Temp")
    With rs
        Do
            GenreList = [COLOR=red]Split(!Genre, ", ")[/COLOR]
            For x = LBound(GenreList) To UBound(GenreList)
                rs_out.AddNew
                rs_out!Genre = GenreList(x)
                rs_out.Update
            Next x
            .MoveNext
        Loop Until .EOF
    End With
    
    rs_out.Close
    Set rs_out = Nothing
    rs.Close
    Set rs = Nothing
    
    MsgBox "Done"
    
End Sub
Once you've done this, create an append query, turn on 'sorting and grouping' and then append the new values to whatever final Genres table you intend to use. Note that it's the SPLIT function that does what you are looking for.

Also, please note that 'Year' is a terrible field name because it's a reserved word and is going to cause you no end of grief.
 

Tieval

Still Clueless
Local time
Today, 10:20
Joined
Jun 26, 2015
Messages
475
Personally I would create a genre table for films with yes/no fields for all genres, this way you could search for films later by just ticking the genres you are looking for.

Then I would run a series of queries for each film that state if original genre contains 'Crime' the field for that film in the genre table should be 'yes'.
 

Reckoner

New member
Local time
Today, 03:20
Joined
Aug 25, 2016
Messages
5
Thanks Frothingslosh, it's worked a treat.

One more thing what if I wanted to do this for multiple columns into the same Table?
 

moke123

AWF VIP
Local time
Today, 06:20
Joined
Jan 11, 2013
Messages
3,852
you may also use a collection along with the split() and by using a key and error handling you can avoid duplicates without needing a temp table.

something along these lines
Code:
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strSplit As Variant
    Dim i As Integer
    Dim itm
    Dim gCol As Collection


    Set gCol = New Collection

    strSql = "select * from  Table1"   'substitute your table name

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)


    Do Until rs.EOF
        strSplit = Split(rs!genre, ",")   'substitute your field name

        For i = 0 To UBound(strSplit)

            On Error Resume Next
            With gCol
                .Add strSplit(i), CStr(strSplit(i))  ' add the item to your collection with out duplicates
            End With
            On Error GoTo 0
        Next i
        rs.MoveNext
    Loop

    For Each itm In gCol
        Debug.Print itm    ' add code here to write the item to your table
    Next

    rs.Close
    Set rs = Nothing
    Set gCol = Nothing
    Set db = Nothing

HTH
 

moke123

AWF VIP
Local time
Today, 06:20
Joined
Jan 11, 2013
Messages
3,852
One more thing what if I wanted to do this for multiple columns into the same Table?

you could make the procedure more generic and just pass the table and field names as arguments.

Code:
Public Sub MyProc(vTable As String, vRs As String, vtblOut As String, vfldOut As String)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strSplit As Variant
    Dim i As Integer
    Dim itm
    Dim gCol As Collection
    Dim strOut As String

    Set gCol = New Collection

    strSql = "select * from  " & vTable

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

 
 
    Do Until rs.EOF
        strSplit = Split(rs.Fields(vRs).Value, ",")

        For i = 0 To UBound(strSplit)

            On Error Resume Next
            With gCol
                .Add strSplit(i), strSplit(i)   ' add the item to your collection with out duplicates
            End With
            On Error GoTo 0
        Next i
        rs.MoveNext
    Loop

    For Each itm In gCol
            
        strOut = "Insert into " & vtblOut & "(" & vfldOut & ") values('" & itm & "')"
       
        CurrentDb.Execute strOut, dbFailOnError

    Next

    rs.Close
    Set rs = Nothing
    Set gCol = Nothing
    Set db = Nothing
End Sub

you could then call it several times with different arguements
Code:
call MyProc("Table1","genre","tblGenre","GenreTxt")
 

Falcone203

Member
Local time
Today, 05:20
Joined
Dec 5, 2019
Messages
40
I have been working with a couple of suggestions on this matter. I have found that I can get the list of a delimited "," field from a qry that shows "is not null" in my linked table and any other field to link it to other tables from Google Forms.

My linked table is "Worksheet1" that had empty (Responder) fields that would throw an error. Created a "qryWorksheet1" that shown "Is Not Null" in the field to be delimited. Added two suggestions together, one from

forums/showpost.php?p=1501095&postcount=5

And

forums/showpost.php?p=1501129&postcount=8

and came up with this. Will be changing the qry to show only NEW imports from my linked table so I don't DBL up my results.

Background - I am on a Volunteer Fire Dept and used Google Forms to start tracking emergency call since everyone has a cell phone and the paper sheets would not get turned in. I linked the google forms data sheet to an Access DB for dept use, hence, the need to separate each emergency responder for tracking. Thanks to you all across the pond for leading me here. Cheerio

Code:
 Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strSplit As Variant
    Dim i As Integer
    Dim itm
    Dim gCol As Collection

    Set gCol = New Collection

    strSql = "select * from  qryWorksheet1"   'substitute your table name

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

'Dim rs_out As DAO.Recordset
Set rs_out = CurrentDb.OpenRecordset("Responded_Temp")

   
    Do Until rs.EOF = True
             On Error GoTo ErrorHandler
             strSplit = Split(rs![(Responded)], ",")   'substitute your field name

        For i = 0 To UBound(strSplit)

            On Error Resume Next
            With gCol
                .Add strSplit(i), CStr(strSplit(i)), RNum  ' add the item to your collection with out duplicates
            
            End With
            On Error GoTo 0
            
             rs_out.AddNew
                rs_out![(Responded)] = Trim(strSplit(i))
                rs_out!RNum = rs!RNum
                rs_out.Update
           'MsgBox "Responder" & strSplit(i) &" "& rs!RNum
ErrorHandler:
         Next i
        rs.MoveNext
        
    Loop

    For Each itm In gCol
        Debug.Print itm    ' add code here to write the item to your table
         
    Next

    rs.Close
    Set rs = Nothing
    Set gCol = Nothing
    Set db = Nothing
MsgBox "Done"

End Sub
 
Last edited:

Falcone203

Member
Local time
Today, 05:20
Joined
Dec 5, 2019
Messages
40
I have a Table (Films) where I have a string of data separated by commas (Genre):

(Ignore the Dashes that indicates break between Columns)

Film - Year - Rating - Genre

Catch Me If You Can - 2002 - 9 - Biographical, Crime, Drama
Batman Begins - 2005 - 9 - Comic Book, Action, Adventure
Heat - 1995 - 8 - Crime, Drama
Ex Machina - 2015 - 8 - Sci-Fi, Psychological, Thriller

How can I put this one column into a new Table (called Genres) putting each part separated by a comma into a new Row like below:

Genre

Biographical
Crime
Drama
Comic Book
Action
Adventure
Crime
Drama
Sci-Fi
Psychological
Thriller

I'm wanting to do this so I can use the resulting table as a source for a Combo Box, this Combo Box will then be used to filter on the master Table while allowing wildcard results so partial matches are retrieved.

If you had not asked I would not know how to fix my DB, Thanks
 

Users who are viewing this thread

Top Bottom