×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Parse delimited text from field into new row

Parse delimited text from field into new row

Parse delimited text from field into new row

(OP)
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?

RE: Parse delimited text from field into new row

So you have this:

Product Location
1234    abd;DEF;ghij;kLmNo 
 
and you want this:

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


---- Andy

There is a great need for a sarcasm font.

RE: Parse delimited text from field into new row

(OP)
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

RE: Parse delimited text from field into new row

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.

RE: Parse delimited text from field into new row

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

CODE

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

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

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

    For i = LBound(ary) To UBound(ary)
        sSQL = "INSERT INTO SomeTable(ID, Whatever) " _
            & " 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.

RE: Parse delimited text from field into new row

(OP)
This looks like exactly what I need, thank you!

RE: Parse delimited text from field into new row

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


---- Andy

There is a great need for a sarcasm font.

RE: Parse delimited text from field into new row

Nice code Andy. bow

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

RE: Parse delimited text from field into new row

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

RE: Parse delimited text from field into new row

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

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.

RE: Parse delimited text from field into new row

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:

CODE --> SQL

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

RE: Parse delimited text from field into new row

(OP)
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!!

RE: Parse delimited text from field into new row

Quote (Trebor423)

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

RE: Parse delimited text from field into new row

(OP)
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?

RE: Parse delimited text from field into new row

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

RE: Parse delimited text from field into new row

Quote (Trebor423 )

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close