Convert/split delimited field into normalised table (1 Viewer)

Stormin · Sep 12, 2019

Hi all,

Having a bit of a brain fart. I have a file that I import into a table in Access 2016. The data ends up like this:

Code:

MainTableID     PicURLs
1               www.example.com;www.abc.com;www.xyz.com
2               
3               www.access-programmers.co.uk

The field PicURLs are zero or more URLs separated by a semicolon delimiter.

I want to store the PicURLs in a separate table that is more normalised.
e.g.

Code:

PicTableID      MainTableID     PicURL
1               1               www.example.com
2               1               www.abc.com
3               1               www.xyz.com
4               3               www.access-programmers.co.uk

I can't seem to grasp a solution. Is it possible through queries only or is it essential (or simpler) to throw some VBA into the mix?

This will be part of an automated import process.

Thanks for any ideas!

plog · Sep 12, 2019

If there are at most just 2 urls seperated by semi colon in a field, then you can do this with just queries. If more than that its going to take code.

In either case, first import the file/data into a temporary table, then:

Max 2--> make a query to extract the first url, make a query to extract second url.

More than 2--> VBA to read temp table into recordset and then split out field into individual urls then do INSERT statement for each

theDBguy · Sep 12, 2019

Hi. I can't remember exactly but didn't Colin recently posted an article on how to do this using PowerQuery in Excel? Just thinking out loud...

isladogs · Sep 12, 2019

plog said:
If there are at most just 2 urls separated by semi colon in a field, then you can do this with just queries. If more than that its going to take code.

Not sure why you say that.
The Split function can separate these using ; as the separator.
Unfortunately you can't use Split directly in a query (stupid MS decision) so you need to wrap it in another function.
For example, Daniel Pineault has a StringSplit function

Taking the example data provided, I created this query:

Code:

SELECT Table1.ID, Table1.PicURLs, IIf(Len([PicURLs])>0,String_Split([PicURLs],0,";"),"") AS Expr1, IIf(Len([PicURLs])>0,String_Split([PicURLs],1,";"),"") AS Expr2, IIf(Len([PicURLs])>0,String_Split([PicURLs],2,";"),"") AS Expr3
FROM Table1;

Output:

You can then normalise it using union queries or Excel PowerQuery

EDIT
Here's a link to my PowerQuery post as mentioned by DBG: https://www.access-programmers.co.uk/forums/showthread.php?t=306580

arnelgp · Sep 12, 2019

Code:

Public Function fncSplit()
    Const TABLE_SOURCE As String = "pic_URLs1" 'source table where you want to split
    Const TABLE_TARGET As String = "pic_URLs2" 'resulting table
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim v As Variant
    Dim i As Integer
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(TABLE_SOURCE, dbOpenSnapshot, dbReadOnly)
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            If Trim(!picURLS & "") <> "" Then
                v = Split(!picURLS, ";")
                For i = 0 To UBound(v)
                    db.Execute "Insert Into [" & TABLE_TARGET & "] " & _
                        "(MainTableID, picURL) " & _
                        "select " & !MainTableID & "," & _
                        Chr(34) & v(i) & Chr(34)
                Next
            End If
            .MoveNext
        Wend
        .Close
        Set rs = Nothing
    End With
    Set db = Nothing
End Function

Stormin · Sep 16, 2019

Thanks, all!

plog said:
If there are at most just 2 urls seperated by semi colon in a field, then you can do this with just queries. If more than that its going to take code.

Ranges from zero URLs to around 25, I believe. I reckon I'll write some code for this as it'll be easier (and I understand it more than complicated queries)

isladogs said:
The Split function can separate these using ; as the separator.
Unfortunately you can't use Split directly in a query (stupid MS decision) so you need to wrap it in another function. [...] You can then normalise it using union queries or Excel PowerQuery

Hi Colin. This is entirely contained within Access so unfortunately cannot go cheating and unpivot with PowerQuery

I agree that it is a very powerful tool and use it often in Excel projects to get database-esque functionality all contained within one program

I definitely encourage exploring PQ whether you have the time or not

arnelgp said:
(code)

Thanks, Arnel. This is pretty much along the lines of what I was thinking after the comments above. Will use and adapt your code for my program

Convert/split delimited field into normalised table (1 Viewer)

Stormin

Nawly Ragistarad Usar

plog

Banishment Pending

theDBguy

I’m here to help

isladogs

MVP / VIP

Attachments

arnelgp

..forever waiting... waiting for jellybean!

Stormin

Nawly Ragistarad Usar

Similar threads

Users who are viewing this thread