1 Reply Latest reply on Apr 5, 2010 1:14 PM by fitch

    CSV to relational table

    waveyd

      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