Import comma delimited

[JMart]

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, 2020 by JMart

[comment]

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.

 

[JMart]

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

[comment]

How often do you need to do this?

[JMart]

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

thanks

 

[comment]

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.