Access Query To Split String (with unordered variables in it) Into Columns BUT Output Is Ordered (each variable into a column with like)

Question

I know enough to be dangerous in ms access, but a query with this level of functionality escapes me.

The Problem: I have an export of 100K+ products from a website to massage to import into another system. But I need to convert a string into separate columns in a clean way. This column contains product variable fields as a string separated by a delimiter (type, length, color, etc). The problem is the order of these don't line up so I can't just do a text to columns.

Example Input:

Product	Field1
Product 1	Type=Multiple;Side=Optional;Front=No;Hardware=Steel
Product 2	Length=130 ft.;Type=Cable
Product 3	Type=Foam;Ratings=Medium

Example Output: (in no particular column order):

Product	Type	Side	Front	Hardware	Length	Ratings
Product 1	Multiple	Optional	No	Steel
Product 2	Cable				130 ft.
Product 3	Foam					Medium

These stack overflow questions are very close but I'm not seeing how to convert it to this application. Thoughts?

• Split string data into multiple columns access
• Split one field/column into Multiple Columns with varying figure counts SQL Access

Answer 1

Consider a function that returns data part.

Function GetData(strF As String, strS As String)
Dim aryS As Variant, x As Integer
aryS = Split(strS, ";")
For x = 0 To UBound(aryS)
    If Split(aryS(x),"=")(0) = strF Then
        GetData = Split(aryS(x),"=")(1)
        Exit For
    End If
Next
End Function

This version allows for multiples of same category in field:

Function GetData(strF As String, strS As String)
Dim aryS As Variant, x As Integer
aryS = Split(strS, ";")
For x = 0 To UBound(aryS)
    If Split(aryS(x), "=")(0) = strF Then
        GetData = GetData & Split(aryS(x), "=")(1) & ","
    End If
Next
If GetData <> "" Then GetData = Left(GetData, Len(GetData) - 1)
End Function

Example of calling function from SELECT query (could be used for an INSERT INTO SELECT) for one field:
SELECT Product, GetData("Type",[Field1]) AS Type FROM Table1;

Any process dependent on knowing beforehand what categories will be present will fail if new categories are encountered.

Following code writes records to a ProductSpecs table with fields Product, Category, Data.

Sub StringToRecords()
Dim rs As DAO.Recordset, db As DAO.Database, aryS As Variant, x As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1")
Set db = CurrentDb
Do While Not rs.EOF
    aryS = Split(rs!Field1, ";")
    For x = 0 To UBound(aryS)
        db.Execute "INSERT INTO ProductSpecs(Product, Category, Data) " & _
                   "VALUES(" & rs!Product & _ 
                   ",'" & Split(aryS(x), "=")(0) & "','" & Split(aryS(x), "=")(1) & "')"
    Next
    rs.MoveNext
Loop
End Sub

Code could be modified to write data directly to a table designed with category fields. However, this would not be a strictly normalized structure and major pitfall already mentioned.