Solved

Parsing comma delimited data in a single column in a table

Posted on 2011-03-07
6
449 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 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

815 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now