Jump to content

Recommended Posts

Posted (edited)

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.

image.png.4c87fc63177f5148699a97fe8b7a039e.png    

 

Thanks

JMart

Edited by JMart
Link to post
Share on other sites

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.

 

Link to post
Share on other sites
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

Link to post
Share on other sites

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

thanks

 

Link to post
Share on other sites

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.

 

  • Like 1
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.