Parse delimited text from field into new row

[Trebor423]

I have 2 columns, one with a product, the other with a ";" delimited string associated to that product in an Access 2016 table. I need to break that string up into multiple rows and into a new table. For instance Product = 1234 and Location = abd;DEF;ghij;kLmNo into
1234 abd
1234 DEF
1234 ghij
1234 kLmNo

How?

 

[Andrzejek]

So you have this:

[pre]
Product Location
1234 abd;DEF;ghij;kLmNo
[/pre]
and you want this:

[pre]
ID Whatever
1234 abd
1234 DEF
1234 ghij
1234 kLmNo
[/pre]
You can split your Location data by... well, Split() function using a ";" as delimiter


---- Andy

There is a great need for a sarcasm font.

 

[Trebor423]

Thanks, but when I use the split function in a query it returns 'undefined' and I don't know enough (if any) VBA to write it

 

[Andrzejek]

Split() function returns an array of elements, and you cannot use it in SQL.
You have to invest a little in a VBA code.

Do you know how to request the data from your table with SQL using VBA?


---- Andy

There is a great need for a sarcasm font.

 

[Andrzejek]

I don't know your tables and fields, so you need to adjust the RED portions, but this should work:

Dim rs As DAO Recordset
Dim sSQL As String
Dim ary() As String
Dim i As integer

sSQL = "SELECT Product, Location FROM [red]MyTable[/red]"
Set rs = CurrentDB.OpenRecordset(sSQL)

Do While Not rs.EOF
    ary = Split(rs!Location, ";")

    For i = LBound(ary) To UBound(ary)
        sSQL = "INSERT INTO [red]SomeTable(ID, Whatever)[/red] " _
            & " Values (" & rs!Product & ", '" & ary(i) & "')"
        DoCmd.RunSQL sSQL
    Next i

    rs.MoveNext
Loop

rs.Close
Set rs = Nothing

---- Andy

There is a great need for a sarcasm font.

 

[Trebor423]

This looks like exactly what I need, thank you!

 

[Andrzejek]

Looks good, but does it work the way you need?


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Nice code Andy.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Trebor423]

Working on it, novice issues to overcome first.. will post success/question soon. Thank you!

 

[Andrzejek]

Thanx Duane, that means a lot to me coming from you.

Trebor, I hope you don't have any ' (single quotes) in your Location field.
If you do, we need to add a little bit of code. Otherwise - crash


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

If there is an existing table of unique locations, this can be done with a simple query.

For instance if you have
Categories (Your Product table)
CategoryID
CategoryName
Products (comma separated list of product names)

Products (your locations table)
ProductID
ProductName
SupplierID

If the productname fields are all unique then you might be able to use a query with SQL of:

SELECT Categories.CategoryID, Products.ProductName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Categories.Products) Like "*" & [ProductName] & "*"))
ORDER BY Categories.CategoryID, Products.ProductName;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Trebor423]

So after I got past the rookie things (set warnings, naming, prebuild, …) I'm running the code. It's still running. This is the second time I'm running it, I killed it the first time after 20 minutes, but it had written 92k rows so it was working as hoped for. I feel like this will work but it's a lot of data for Access (750k products with an average of 200 locations per).
I haven't run the query yet, but that looks great too and something I know I will use numerous times in this role.
Thank you both!!

 

[dhookom]

750k products with an average of 200 locations per

is probably something you should have mentioned in your first post. Is this a one time operation or do you do this regularly?

I believe there are SQL Server possibilities that would be much better.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Trebor423]

Thanks, will be sure to include better definition next time. Yes this will get run on a scheduled interval (every 4 weeks). I will look into having this done on SQL Server. Would the algorithm change?

 

[dhookom]

There are definitely better solutions on SQL Server. When you get SQL Express running, ask your question in the SQL group with a clear specification and possibly sample data and table structures.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

750k products with an average of 200 locations per [product]

I would definitely visit whoever provides you with this data and ask to redo the way this data is being collected. Don't you think it would be better if this data would come to you in the 'proper' format so you don't have to lift the finger?


---- Andy

There is a great need for a sarcasm font.