Search code examples
sqlms-accessvbadelimiter

Split delimited entries into new rows in Access

So someone gave me a spreadsheet of orders, the unique value of each order is the PO, the person that gave me the spreadsheet is lazy and decided for orders with multiple PO's but the same information they'd just separate them by a "/". So for instance my table looks like this

PO             Vendor   State
123456/234567  Bob      KY
345678         Joe      GA
123432/123456  Sue      CA
234234         Mike     CA

What I hoped to do as separate the PO using the "/" symbol as a delimiter so it looks like this.

    PO         Vendor   State
123456         Bob      KY
234567         Bob      KY
345678         Joe      GA
123432         Sue      CA
123456         Sue      CA
234234         Mike     CA

Now I have been brainstorming a few ways to go about this. Ultimately I want this data in Access. The data in its original format is in Excel. What I wanted to do is write a vba function in Access that I could use in conjunction with a SQL statement to separate the values. I am struggling at the moment though as I am not sure where to start.

Solution

  • If I had to do it I would

    • Import the raw data into a table named [TempTable].
    • Copy [TempTable] to a new table named [ActualTable] using the "Structure Only" option.

    Then, in a VBA routine I would

    • Open two DAO recordsets, rstIn for [TempTable] and rstOut for [ActualTable]
    • Loop through the rstIn recordset.
    • Use the VBA Split() function to split the [PO] values on "/" into an array.
    • For Each array item I would use rstOut.AddNew to write a record into [ActualTable]