Data import question

[fragglerock]

I have some data like so

ID name Thing1 thing2 thing3 other
1 bob 0 0 1 <null>
2 fish 0 1 0 <null>
3 Steve 1 0 1 <null>
4 Holmes 0 0 0 thing4


I would like to import this into my db to get a look up table of "things" and a list of "names" and a linker to say who had what (it is many to many... people can have more than one "thing"), What is the best way of going about this (there are a fair few "others" and more than 3 "things" so as fully automated as possible! It will be a one off import though (i *hope*)

In a related question... in the fullness of time I will want to be able to re-create the above table (but with no "others" column)...

Links to "work it out yourself" resources are fine as answers

Thanks

 

[SkipVought]

Hi fragglerock,

First you need to NORMALIZE your table. Thing1, Thing2 etc ad infinitum are all THING data. Thing should be your field and each specific Thing is your DATA. I there is a numeric attribute like the COUNT of a Thing or the LENGTH of a Thing or the COST of a Thing, then that Column can be included as well.

So you'ld have columns
[tt]
ID Name Thing
[/tt]
where the Name values could be repeated and a Name could have as many Things as you like.

Skip,
[sub]
[red][/red]
[/sub]

 

[carlen]

Ideally you would want 3 tables
a thing table

ThingID Descriton
1 thing1
2 thing2
3 thing3

a people table

PersonID Descriton
1 Bob
2 Kate
3 Ashley

and an ownership table

DataID PersonID ThingID
1 3 1
2 3 2
3 1 2
4 2 3

to recreate the initial table a simple pivot lookup and some joins would give you the desired output.

 

[fragglerock]

Thank you both for your time.

Carlen has elucidated the final structure I had in mind... I think my difficulty was with the "simple pivot lookup and some joins"!

I am NOT a whizz in excel but I will head off with Google and see what I can come up with!

if you have any further hints I am all ears

Thanks again.