Split Comma Delimited Cells Across Rows

[nsl100]

Hi All,

I have an access database consisting of one table which is an pointing to an excel spreadsheet.

In the table I have a number of cells (in a column called 'Name') that contain text delimited by commas e.g. "John Smith, Jo Bloggs, Peter Pan".

I wish to split the text on the commas and create new (duplicate) rows with only one vale (e.g. john Smith) in the cells on the name column.

I could pre-prepare the spreadsheet in excel before going into Access however this would be time consuming, therefore is anybody aware of a method that I could employ to do this 'splitting' task in Access.

Many Thanks,

Neal

 

[Remou]

You can use Split to create an array, then append each element of the array to a table. Something like this rough sketch:

'Need reference to Microsoft DAO 3.x Object Library
Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset

Set rsIn=currentDB.OpenRecordset("Name of table with field to split")
Set rsOut=currentDB.OpenRecordset("Name of table to hold new records")

Do While Not rsIn.EOF
  astrNames=Split(rsIn![Name of field to split],",")
  For i = 0 To UBound(astrNames)
     rsOut.AddNew
     rsOut!{Name of Field]=astrNames(i)
     rsOut.Update
  Next
  rsIn.MoveNext
Loop