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
Comments
Save it as CSV and import with Data Import Wizard or from the terminal.
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...
If this is for an Ensemble Lookup Table, then the schema name would be Ens_Util and the table name would be LookupTable. See the documentation here:
http://docs.intersystems.com/ens20161/csp/docbook/DocBook.UI.Page.cls?K…
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,
| function | GER | =VERKETTEN("<entry table=""TestLookupTable"" key=""";B2;""">";C2;"</entry>") |
| function | ENG | =CONCATENATE("<entry table=""TestLookupTable"" key=""";B2;""">";C2;"</entry>") |
| T1 | V1 | <entry table="TestLookupTable" key="T1">V1</entry> |
| T2 | V2 | <entry table="TestLookupTable" key="T2">V2</entry> |
| T3 | V3 | <entry table="TestLookupTable" key="T3">V3</entry> |
| T4 | V4 | <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'=""