Good day all,

I need some clarification on how i can possibly accomplish a task. No matter how much i think of a way cant figure out how to do it.

i need to import data from an excel file where column "A" contains a unique value, but column "B" and "C" can respectively contain anywhere from 1 value to 50 values separated by a comma each. I need to create one record per value on column "B" but keep column "A" as the key field for each record.

    

 

Thanks

JMart

Edited July 3, 20205 yr by JMart

You need to do this in two steps: first, import the Excel into a temp table. Then loop over the imported records along these lines:

Go to Record [ First ]
Loop
   Set Variable [ $a ; Temp::FieldA ]
   Set Variable [ $b ; Substitute ( Temp::FieldB ; ", " ; ¶ ) ]
   Go to Layout [ Target ]
   Loop
      Set Variable [ $i ; $i + 1 ]
      Exit Loop If [ $i > ValueCount ( $b ) ]
      New Record
      Set Field [ Target::FieldA ; $a ]
      Set Field [ Target::FieldB ; GetValue ( $b ; $i ) ]
   End  Loop
   Set Variable [ $i ; "" ]
   Go to Layout [ Temp ]
   Go to Record [ Next; Exit after last ]
End  Loop

Caveat: untested, written from memory.

 

On 7/3/2020 at 10:12 AM, comment said:

You need to do this in two steps: first, import the Excel into a temp table. Then loop over the imported records along these lines:

Go to Record [ First ]
Loop
   Set Variable [ $a ; Temp::FieldA ]
   Set Variable [ $b ; Substitute ( Temp::FieldB ; ", " ; ¶ ) ]
   Go to Layout [ Target ]
   Loop
      Set Variable [ $i ; $i + 1 ]
      Exit Loop If [ $i > ValueCount ( $b ) ]
      New Record
      Set Field [ Target::FieldA ; $a ]
      Set Field [ Target::FieldB ; GetValue ( $b ; $i ) ]
   End  Loop
   Set Variable [ $i ; "" ]
   Go to Layout [ Temp ]
   Go to Record [ Next; Exit after last ]
End  Loop

Caveat: untested, written from memory.

 

Thanks works like a charm, now i have to deal with a speed issue, since i end up having over 3 Million records on this table alone.  

 

Thank

Jay

How often do you need to do this?

Hopefully not often, Whenever canon publishes their Dealer parts list, once or twice a year.

thanks

 

Even straightforward importing of 3M records is not going to be fast. You could possibly speed up the current process by adding:

Set Variable [ $n ; ValueCount ( $b ) ]

before the loop, and then changing the exit condition to:

Exit Loop If [ $i > $n ]

I am not sure how much of a difference that's going to make, but with that many records every bit can help.

You might want to look into preprocessing the input in another application - though I cannot think of a suitable one off the top of my head. Excel is a very inconvenient format for transferring data.