Solved

Parsing comma delimited data in a single column in a table

Posted on 2011-03-07
6
457 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:epuglise
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 3

Accepted Solution

by:
JAMcDo earned 500 total points
ID: 35061500
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
 

Author Comment

by:epuglise
ID: 35063024
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
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35063135
Befor the executable code, use the

DoCmd.SetWarnings False

then after the excutable code, use

DoCmd.SetWarnings True

J.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:epuglise
ID: 35063141
Cool thanks for the timesaver. :)
0
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35063150
Forgot to mention, Congrats on the code success.

J.
0
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35066187
Thanks for the points,

J.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question