Take One Column Of Excel Data And Split Into Multiple Rows Of MS Access Data

[LisaC]

I have data that is coming from Excel, (see attached example and also illustration of output needed in Access). I need to bring this data into my Access database however, once I get it in there, there are several things that I need to accomplish and don't know how to:
For each size in the order field, the size data is separated by commas and the quantities are enclosed in parenthesis ie; Youth Large (1), Adult Small (1), Adult Medium (1),
I need to separate this so that there is only one size in the field and then another field gets populated by the quantity data, copy the same data for that order ID to a new line and then insert the next size with it's corresponding quantity data. When the data has been split and there are no more sizes, it goes to the next record. I hope that I am explaining this correctly, please let me know if you need further info. The needed output is in the Excel file as a sample. Thanks so much for any help! LC

[ItsMe]

I did not look at your attached file but, I use VBA to parse string variables and save the results to a table in cases like this.

[ranman256]

Save the excel book to a generic file say, \\server\myDataFile.xls
Link this excel file to Access, called say, xlData2Import
build a query that uses this table to append to an internal access table.
build a query to give you the results.

now everytime you you get a new file, save a copy to \server\myDataFile.xls
run the import, run the results.

As to the ORDER field, it should not be sent that way. It should be Qty, but if you cant change that, then after import there will need to be another query to 'parse' the English words in col H to actual QTY counts.

[ranman256]

Instead of an import query you will need a macro to call a parsing function. This fcn will append the data after parsing the order/size box

Public Function ParseOrders()
Dim rst   'As Recordset
Dim vOrderNo, vWord, vTxt
Dim vQty, vSize
Dim i As Integer
Set rst = CurrentDb.OpenRecordset("xlData2Import")
With rst
   While Not .EOF()
      vTxt = .Fields("Order").Value & ""
      vTxt = Replace(vTxt, ",", " ")
      vOrderNo = .Fields("Order#").Value & ""
      
      i = InStr(vTxt, ")")
      While i > 0 And Len(vTxt) > 0
            vWord = Left(vTxt, i - 1)
            vTxt = Mid(vTxt, i + 1)
            
            i = InStr(vWord, "(")
            vSize = Trim(Left(vWord, i - 1))
            vQty = Trim(Mid(vWord, i + 1))
      
           'Debug.Print vOrderNo, vSize, vQty
              'add the parsed order to the order table
           sSql = "Insert into tOrders (ORDERNO,SIZE,QTY) VALUES ('" & vOrderNo & "','" & vSize & "'," & vQty & ")"
           DoCmd.RunSQL sSql
           i = InStr(vTxt, ")")
       Wend
       
      .MoveNext
  Wend
End With
Set rst = Nothing
End Function

[LisaC]

thank you, I will try this