Parsing comma delimited data in a single column in a table

I am (repeatedly) importing an excel file, and one of the columns in the excel file has a "list" in it. So for instance, the data might be:

Animal | Attributes
Giraffe| African, tall, yellow, spotted
Panda| Asian, black and white, herbivore

etc.

I want to put the animal information into one table (easy), and the list of ALL attributes in another table.

Do I need to code this or is there a way to write a query that will read each individual attribute in the attribute column and put one word at a time in the Attribute table?

Thanks!
epugliseAsked:
Who is Participating?
 
JAMcDoCommented:
I would suggest coding it.

Load the excel file into a temporary table.

From the temporary table, work throught the table adding the Animal to one table and in a loop, parse the Attributes and add a record for each attribute to the second table.  Take into account the need to add a key to each table for linking purposes afterwards.

J.

0
 
epugliseAuthor Commented:
well, i shocked myself by actually being able to implement your directions in code. i must be learning from this website :) One last question before I award points... what is the in code command for suppressing the "you're about to append a row of data" message temporarily while the code is working?

thanks!!!
0
 
JAMcDoCommented:
Befor the executable code, use the

DoCmd.SetWarnings False

then after the excutable code, use

DoCmd.SetWarnings True

J.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
epugliseAuthor Commented:
Cool thanks for the timesaver. :)
0
 
JAMcDoCommented:
Forgot to mention, Congrats on the code success.

J.
0
 
JAMcDoCommented:
Thanks for the points,

J.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.