Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Parsing comma delimited data in a single column in a table

Posted on 2011-03-07
6
Medium Priority
?
465 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
  • 4
  • 2
6 Comments
 
LVL 3

Accepted Solution

by:
JAMcDo earned 2000 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
Independent Software Vendors: 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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 …

972 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