Joao Palma · Dec 9, 2016

Import Code Table

Good Morning

I have to create a new code table with data from a spreadsheet. This spreadsheet has 170 rows of data.

Is there any easy way of importing the data from Excel into my new Code Table?


Thank You

0 829
Discussion (8)0
Log in or sign up to continue

Thank you Eduard. 

I've tried that but then I don't know what to select on the "Schema Name to import to". 

I'm still a newbie...

I don't think I have...

Then there are several free online courses you can take advantage of here. Especially Creating a Caché Class Definition course.

There are multiple ways as Bredan also pointed out.

If you're Excel specialist ;-) you might want to introduce a third column.

Use the CONCATENATE string function or whatever it is called in your Excel version.

The following will lead to a XML entry which you could copy & paste in an standard XML Export of a lookup table,

So just mockup a Lookiup table, export it. Modify the file with the new entries and Import the resulting file.

Just 2 other cents,

functionGER=VERKETTEN("<entry table=""TestLookupTable"" key=""";B2;""">";C2;"</entry>")
functionENG=CONCATENATE("<entry table=""TestLookupTable"" key=""";B2;""">";C2;"</entry>")
T1V1<entry table="TestLookupTable" key="T1">V1</entry>
T2V2<entry table="TestLookupTable" key="T2">V2</entry>
T3V3<entry table="TestLookupTable" key="T3">V3</entry>
T4V4<entry table="TestLookupTable" key="T4">V4</entry>

The following way might be handy for a one time import.

The ClassMethod %UpdateValue() in Ens.Util.LookupTable is to Update or Insert Entries:
Copying from Excel and pasting it to Terminal (Shift-Insert) will result in Tab delimited Key Value pairs.

The following commands insert one entry into the LookupTable "mytable":

ENSEMBLE>read line
ENSEMBLE>write ##class(Ens.Util.LookupTable).%UpdateValue("mytable",$P(line,$C(9)),$P(line,$C(9),2)

Copying a series of Values directly from Excel might work as follows.

Looping in Terminal using the above idea would be a one line:
ENSEMBLE>do {read line  if line'="" {write ##class(Ens.Util.LookupTable).%UpdateValue("mytable",$P(line,$C(9)),$P(line,$C(9),2),1)}} while line'=""