split delimited field into different rows

[mazz]

Here is what I have:
ID colors
1 white, black, red
2 red, orange
3 blue

Here is what I want to achieve:
ID colors
1 white
1 black
1 red
2 red
2 orange
3 blue

The separated field would be written to another table in the same DB.

Any help would be greatly appreciated

Thanks in advance
mazz

 

[John Big Booty]

Welcome to the forum.

Perhaps the article here will give you some pointers.

 

[recyan]

Is there going to be not more than 3 colors or is the max number not known ?
Is this a one time operation ?
1) If yes, perhaps, you could export to Excel & then use the Text To Columns feature & then import it back in to a table ?
2) perhaps, use VBA, to loop thro & split each field, then insert in to a table.

Thanks

 

[mazz]

Hi Big John
thanks for you comments. I will look at article

mazz

 

[mazz]

Hi Recyan
the color limit is unknown.
How would I do 2) to loop thro & split each field, then insert in to a table
This will not be a one off operation

thanks
mazz

 

[mazz]

This is the code I have so far but an error comes up:
type mismatch.
The fields are both the same type in both tables. The error occurs at:
For i= lBound(colors) to UBound(colors).

the code:
dim strColors() as string
dim ID as integer
dim rs as dao.recordset
dim i as integer
dim strSql as string
set rs = currentdb.openrecordset("Color_tbl")
do while not rs.eof
ID = rs!ID
strColors = split(rs!colors, ",")
for i = lbound(colors) to ubound(colors)
strSql = "Insert into color2_tbl (ID, Colors) values (" & ID & ", '" & colors(i) & "')"
currentdb.execute strSql
next i
rs.moveNext
loop

 

[Rat1sully]

I'd quwstion the logic of having the numeric filed called ID if it's representative of a quantity

as for the type mismatch i'm going to guess you meant to put strcolours in there?

 

[Rat1sully]

hmm I thought that answer I gave didn't make sense try adapting this that I just wrote, I've tested it ant it does what you're after instead fo the print strplit you'd have your sql

Sub splitem()
Dim strlong, strsplit As String, varsplit, var As Variant, i As Long
 
i = 0
strlong = "red,blue,black,green"
varsplit = Split(strlong, ",")
For Each var In varsplit
strsplit = varsplit(i)
Debug.Print strsplit
i = i + 1
Next
 
Debug.Print "done"
End Sub

 

[mazz]

Hi Rat1sully
I adapted your code and replaced the print screen with my sql and it works fine.
Rat1sully I like your code because it is simple and succinct.
Thanks very much.

As for my code you are right with the strColor replacing colors. It still wasn't working til I captured the nulls and replaced them with "".

strColors = split(rs!colors, ",") and replaced it with
strQualifications = Split(Nz(rs!strColors, ""), ",")

I now have 2 versions that work


I put my code here so that other forum readers can look at:

dim strColors() as string
dim ID as integer
dim rs as dao.recordset
dim i as integer
dim strSql as string
set rs = currentdb.openrecordset("Color_tbl")
do while not rs.eof
ID = rs!ID
strColors = split(rs!colors, ",")
for i = lbound(strColors) to ubound(strColors)
strSql = "Insert into color2_tbl (ID, Colors) values (" & ID & ", '" & strColors(i) & "')"
currentdb.execute strSql
next i
rs.moveNext
loop


mazz

 

[Emil]

Hello, new to this community. I came upon this thread and tried to make the code work. It would not loop and only append the first text prior to the first comma, thoughs? :banghead:

 

[gemma-the-husky]

the code looks ok - if you get a type mismatch check whether colors in your table is defined as type string.

where does the compiler take you to, on the error?

 

[Emil]

Ok i just realized my error, i forgot to remove the Key restriction on my second table not allow dupe ID to be appended. One more thing, what tweek should i do to seek out space between datas instead of commas? Thank you

 

[junknolongerused]

Hello sorry my VBA skills are very poor.
If I want to add to this and add another field, should we say "Make"
How would I add to this code so it splits the colors as per the code below but also includes the 'Make' into each row?

ID colors Make
1 white, black, red Crown
2 red, orange Dulux
3 blue Crown

Here is what I want to achieve:
ID colors
1 white Crown
1 black Crown
1 red Crown
2 red Dulux
2 orange Dulux


dim strColors() as string
dim ID as integer
dim rs as dao.recordset
dim i as integer
dim strSql as string
set rs = currentdb.openrecordset("Color_tbl")
do while not rs.eof
ID = rs!ID
strColors = split(rs!colors, ",")
for i = lbound(strColors) to ubound(strColors)
strSql = "Insert into color2_tbl (ID, Colors) values (" & ID & ", '" & strColors(i) & "')"
currentdb.execute strSql
next i
rs.moveNext
loop