New post
Question
· Dec 9, 2016

Import Code Table

#Databases #Data Import and Export #Ensemble

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

Discussion (8)0
Log in or sign up to continue
Markus Mechnich · Dec 10, 2016

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>
0 0
Markus Mechnich · Dec 11, 2016

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'=""
0 0