AnsweredAssumed Answered

CSV to relational table

Question asked by waveyd on Apr 4, 2010
Latest reply on Apr 5, 2010 by fitch

Title

CSV to relational table

Post

I'm trying to create a script that will handle something that I've been doing manually in excel

 

I have two tables, with number fields for id's...one contains a comma separated list, the other is what I want to use as a lookup

 

id1     list of countries

3     USA,China,Japan

5     Japan,France,UK,United States

 

 

id2     country

1     China

2     France

3     Japan

4     UK

5     USA

 

 

what I want is this...

 

id3     id1     id2     order

1     3     5     1

2     3     1     2

3     3     3     3

4     5     3     1

5     5     2     2

6     5     4     3

7     5     0     4

 

where 0 indicates there is no match

 

I'm not sure how to go about doing this...my inclination is to substitute commas for paragraph marks and create a value list...but then what? Or, I could use variables, but I can't wrap my brain around how I could do it...I just need a push in the right direction, not a solution....

 

Thanks, and I know this is difficult request....

 

 

David

Outcomes