Importing Table

[Bill Tipton]

Is there a way to import data to a table. For example, from an excel file?

Topic

[Bob Richardson]

Greetings Bill,

I have attached a Word document that describes a method that we use for importing an Excel table (column one=key; column two=argument) into the engine.  It is a manual procedure but once you do it a couple of times, it really is quick.  Obviously not the only method but a method nonetheless.  If the table is new you will need the prologue section from another table file:  just cat it on the top of your imported data.

Let me know if the attachment is unreadable and I can email the document to you.

Enjoy.

[Chris Williams]

If you want to load a table from Excel and are comfortable with using tcl, you can use the attached routine.

1. First create and save an empty table via the gui if the table does not already exist. (I was too lazy to do this step in the proc.)

2. With your data in the first two columns of the spreadsheet, save it as a CSV file.

3. Use the CSV file to load the data into the table using the attached tcl proc.

[Jim Kosloskey]

Bill,

Interesting. I just completed a Tcl proc to build Cloverleaf(R) Lookup Tables from csv or other delimited files as would be created from an export of something like Excel.

If you want the doc to review, send me an email.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jim Beall]

After the table’s been imported I’m assuming that the process (or is it just the thread?) needs to be recycled.  Is that correct (I’m doing something similar but I want it to be totally automated – not sure I can purge cache through a script or if purge cache works to refresh table data)?

[Tom Rioux]

I do something similar that converts/updates Physician ID’s.  I have a script that daily imports a .csv file and converts the data to a table.  I then have a second script that goes through and performs a purge cache on all the interfaces that utilize those tables.   This is something that has been in place for about 4 months and was written as a temporary interface until a more permanent solution is in place.  The purge cache script works fine and I have had no problems.

Thanks….

Tom Rioux

[Jim Beall]

Thomas –

Can you post your purge cache script?

TIA…

[Tom Rioux]

Here is the script:

#!/usr/bin/sh

#reload using exec but make it a comment for tcl

exec hcitcl “$0” “$@”

#!/qvdx/qdx5.6/integrator/bin/hcitcl

#!/usr/bin/expect -f

set file /sites/qdx5.6/phys_files/purge_sites

set fd [open $file r]

while 1 {

lgets $fd line

if [eof $fd] { break }

set site [lindex $line 0]

set process [lindex $line 1]

set thread [lindex $line 2]

set HciSite $site

set env(HCISITE) $HciSite

echo $env(HCISITE)

set cmd “setroot ; setsite $env(HCISITE) ; “

append cmd “hcicmd -p $process -c “$thread purgex””

echo cmd is $cmd

if [catch { echo [exec /usr/bin/sh -c $cmd] } err] {

    echo $err

}

}

It uses a file called “purge_sites”.  This file contains the sites, processes, and inbound thread names of the interfaces that use the tables.  The syntax is “site process thread”.  Here is an example of one of the lines from the file:

to_ecl_p lab FR_PL_ECLL_OR

[Sergey Sevastyanov]

Thomas,

I do similar task (collect doctor info in CL) using gdbm database that receives dynamic updates from MFN messages. One of the vendors requires us to expand provider fields (PV1-7 and many others) with NPI (National Provider Id). I created extract from our HIS and loaded into gdbm database (one time job).

I have a real time interface from HIS sending MFN messages whenever provider information added/updated. That thread add/update records in gdbm table, that way my table is always up to date. That ensures that I always provide up to date provider information in expanded fields.

[Tom Rioux]

We will be utilizing an Initiate solution to maintain a Provider Master Index.  The Cloverleaf engine will perform an ODBC call to the PMI database to provide the correct Provider ID and then plug it into the message.

Like I always say, more than one way to skin a cat.

Tom Rioux

[Jim Kosloskey]

Tom,

Wher in the process are you placing the ODBC activity?

Is it pre-route; inside the Xlate, or???

Do you have sleeps to handle connectivity?

What do you do if you receive an error during the processing of the OBDC Query?

I am asking because we are beginning to investigate the use of ODBC as an option for dynamic information retrieval such as you are intending.

I have already confirmed I can do this from a basic functional point of view in an Xlate (and of course elsewhere) but some of the above questions arose and the answers I see are not very palatble.

I am hoping you have different answers.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Tom Rioux]

We are in the very beginning stages.  We haven’t even built the ODBC interface yet.  The PMI side has just got their side built.  We are thinking about putting the ODBC call within the inbound TPS.  I can’t comment on any errors or how we may handle them.  I do know that currently if the doctor is not in the table, then the provider ID is defaulted to a certain number based on facility.

I may be hitting you up for info when we get closer to building our side.

Thanks….

Tom Rioux

[Sergey Sevastyanov]

Thomas,

I’d like to hear it as well. I use SQL queries in one of the interfaces in pre-xlate proc. I pass parameter to that proc whether the thread should ignore SQL errors (like no connection) or fail.

But I have it only in test interface so far, so I really didn’t have good chance to test errors (other than bringing SQL server database offline).

So it will be really interesting to know how people do it with real life interfaces

Thanks

[Jim Kosloskey]

Tom,

No problem I will be happy to exchange.

One thing though that is very obvious – if you put a sleep in the proc in the Inbound Tps (which is common to make sure appropriate attempts at connectivity are achieved) then the entire process sleeps.

That will impact all of the integrations driven from that inbound.

There is a connect option for at least a couple of supported database types that specifies connect delays and retries inside the Connect String.

This would remove the sleep from the Tcl proc but it is unclear to me if that removes the impact to the process.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Tom Rioux]

Thanks Jim.  Do you have any sample scripts you would be willing to share to get me started?  As I said, we are only in the beginning phases here and no one here has built an ODBC interface from scratch.  I’ve worked with ones in the past that were already in place however, so I am a bit familiar with them.  

I certainly understand about the sleep issue.  I believe we are going to attempt to put this in a process all to itself so as to not bog down throughput too much.

Thanks…

Tom Rioux

[Jim Kosloskey]

Tom,

I have a proc I can share.

We do not execute SQL stements within our ODBC proc. Instead, we invoke Stored Procedures which do the Database stuff and use the ODBC drivers to invoke the Stored Procedure.

We have some good reasons for going that way and I am willing to share those with you if you are interested.

The proc will still give you a good start I think.

I will email the proc and doc to you.

I am in Houston this week. If the weather was better, I might make a case for meeting you half way to Dallas at some golf course and we could collaborate during and after 9 .

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Tom Rioux]

Sounds like a plan.  I’m off on Friday’s so that would work out great.  Let me know what your schedule looks like.

Tom

[Bill Tipton]

Jim, i’d like to view that proc as well.  That would be a great help to me here as well.

-Bill

[Author]

Replies