Import CSV to Lookup table within Ensemble.

[R Ali]

Having a quick search it seems possible to manipulate the lookup table
via SQL.

However if I have a CSV with two column's, which I would like to
import into Ensemble, is there an easy way of doing this, to update/
import in with ease?

Many Thanks

[Ricardo Santos]

HI Ruhul,

Have a look at the Data Import Wizard on the SQL page on System Management Portal.

I am sure you can import data that way into the Ens.LookupTable on the namespace of your choice. Alternatively, for a finer degree of control, you can use an Access database to link tables to Caché / Ensemble, and load them up that way.

Regards,
Ricardo

> --
> You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
> To post to this group, send email to Ensemble-in...@googlegroups.com
> To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com
> For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en

[R Ali]

> Many Thanks Buddy,

Will have a look at SMP.

> > For more options, visit this group athttp://groups.google.com/group/Ensemble-in-Healthcare?hl=en- Hide quoted text -
>
> - Show quoted text -

[Ralph7]

The SimpleHL7Search utilities has a freeware app to import a CSV into
a lookup table in the namespace that SimpleHL7Search has been imported
into - just indicate the lookup table name, the file location, and the
delimiter and click on import file. Here is a link to a screen shot:

http://ensemble-in-healthcare.googlegroups.com/web/Screen%20shot%202010-01-22%20at%209.32.06%20PM.jpg?hl=en

Later this year, all the utilities will be namespace aware and be able
to operate across all namespaces.

Thanks,

-Ralph

[Ali Ruhul (RQ8) Mid Essex Hospital]

Hi,

 

I seem to be able to import using the Data Import Wizard, and all looks well, but the 'Finish' button is greyed out.

 

As shown below:

 

 

Any ideas why this mean the case, the preview looks ok?

 

Many Thanks

Mid Essex Hospital Services NHS Trust Caring for you, Caring about you.

Information in this message may contain confidential and privileged information. If you are not the intended recipient please accept our apologies; please do not disclose, copy or distribute information in this e-mail or take any action in reliance on its contents: to do so is strictly prohibited and may be unlawful. Please inform us that this message has gone astray before deleting it.

Content of emails received by this Trust will be subject to disclosure under the Freedom of Information Act 2000. Unless the information is legally exempt from disclosure, confidentiality of this e-mail and your reply cannot be guaranteed. Thank you for your co-operation.

[Ali Ruhul (RQ8) Mid Essex Hospital]

Anyone aware of the issue, or am I doing something wrong before logging a call with Intersystem’s?

 

Many Thanks

[M. Kurz]

Hi,

I manage my lookup table in my old format, which is plain text files
with # lines as comments.
Because I really need the comments and versioning, I will keep
managing the mapping tables as files until Ensemble supports
comments and versioning. Unfortunately, I cannot do the same
for data conversions.

Maybe someone can use some part of the code.

best regards,

Michaela

Example for mapping table file:
-----------------------------------
# mapping table from event to ZBE type
# Copyright (C) 2002 by
# Klinikum der Universitaet Regensburg
# Rechenzentrum DV-med
# Franz-Josef-Strauss-Allee 11
# D-93042 Regensburg
#
#
# mit Bezug
# Aufnahme stationaer
# Aufnahmedaten anlegen Ist
NP11I0|INSERT
# Aufnahmedaten aendern Ist
NP12I0|UPDATE
# Aufnahmedaten stornieren Ist
NP12IS|DELETE
#
# Derzeit wird NICHT JEDE Bewegung an MEDOS geliefert, sondern
# nur DIE Bewegungen, auf die man sich später evtl. beziehen will.

# Fall und Operation anlegen
# (normal)
NP3600|INSERT
# Kurzaufnahme
NP36K0|INSERT
# Notaufnahme
NP36N0|INSERT
-------------------------------
ClassMethod ImportLookupTableAsText(pFileName As %String, pTable As
%String) As %Status
{
/*
** if table is not supplied
** derive name from filename
** use capital letters instead of _
*/
if pTable '= "" {
Set table = pTable
}
else {
Set i = 0
Set table = ""
Set piece = ""
Set filename = pFileName
while $F(filename, "\") '= 0 {
Set filename = $E(filename,2,*)
}
do {
Set i = i + 1
Set piece = $ZCVT($P(filename, "_", i),"W")
Set table = table_piece
} while piece '= ""
Set table = $P(table, ".", 1)
write "table is: "_table,!
}

Set tSC = $$$OK
Set first = 1
Set f = ##class(%FileCharacterStream).%New()
Set f.Filename = pFileName
do {
set line=f.ReadLine()
write line
write !
if '..StartsWith(line, "#") {
write line
write !
Set key = $P(line,"|",1)
if key '= "" {
// write "key is: "_key
// write !
Set value = $P(line,"|",2)
// write "value is: "_value
// write !
if ..%ExistsId(table_"||"_key) {
write "double entry"_key_"="_value_"in table "_table,!
}
if first = 1 {
Kill ^Ens.LookupTable(table)
Set first = 0
}
Set tRecord = ##class(Ens.Util.LookupTable).%New()
Set tRecord.TableName = table
Set tRecord.KeyName = key
Set tRecord.DataValue = value
Set tSC = tRecord.%Save()
if $$$ISERR(tSC) {
write "error saving entry "_key_", zapping table "_table,!
Kill ^Ens.LookupTable(table)
}

Quit:$$$ISERR(tSC)
}
}
} while 'f.AtEnd
do f.Rewind()
// If ..%ExistsId(entry.TableName_"||"_entry.KeyName) {
// Remove duplicate
// Do ..%DeleteId(entry.TableName_"||"_entry.KeyName)
}

ClassMethod ImportLookupTablesAsText(pDirectory As %String, pFileMask
As %String) As %Status
{
Set rs = ##class(%ResultSet).%New("%File:FileSet")
do rs.Prepare()
do rs.Execute(pDirectory, pFileMask)
while rs.Next() '= 0 {
Set filename = rs.GetData(1)
write "Importing "_filename_" ... "
// Set tSC = ..ImportLookupTableAsText(filename, "")
do ..ImportLookupTableAsText(filename, "")
// if $$$ISERR(tSC) {
//write !,"An error occured. Skipping file: "_filename
// }
write "Done.",!
}
quit $$$OK
}

ClassMethod ImportAllFixedSegmentsFromFile(pFileName As %String) As
%Status
{
if pFileName = "" {
set tSC=$$$ERROR(5001,"No file to import segments from")
quit tSC
}
Set f = ##class(%FileCharacterStream).%New()
Set f.Filename = pFileName
// auf existenz abfragen
Set dir = ""
if (pFileName["\") {
set pos = 1
set lastpos = 1
do {
set lastpos = pos
set pos = $F(pFileName, "\", pos)
set lastpos = lastpos - 1
write "pos is"_pos,!
}
while pos '= 0
set dir = $E(pFileName, 1, lastpos)
$$$TRACE("dir is "_dir)
}
do {
set line=f.ReadLine()
if '..StartsWith(line, "#") {
Set seg = $P(line,"|",1)
Set len = $P(line,"|",3)
if len = "" {
$$$TRACE("skipping segment "_seg)
continue
}
Set desc = $P(line,"|",4)
Set file = $P(line,"|",5)
if file = "" {
$$$TRACE(seg_" has no description file, skipping")
continue
}
Set file = $S($F(file, "/", 1)>0:$P(file,"/",2),1:file)
Set file = $S($F(file, "\", 1)>0:$P(file,"\",2),1:file)
Set file = dir_file
write "Reading "_seg_" from file "_file_".",!
Set tSC = ..ImportFixedSegmentFromFile(seg, file)
}
} while 'f.AtEnd
do f.Rewind()
write "Done.",!
quit $$$OK
}