Importing Table

  • Creator
    Topic
  • #50554
    Bill Tipton
    Participant

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

Viewing 16 reply threads
  • Author
    Replies
    • #66583
      Bob Richardson
      Participant

      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.

    • #66584
      Chris Williams
      Participant

      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.

    • #66585
      Jim Kosloskey
      Participant

      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

    • #66586
      Jim Beall
      Participant

      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)?

    • #66587
      Tom Rioux
      Participant

      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

    • #66588
      Jim Beall
      Participant

      Thomas –

      Can you post your purge cache script?

      TIA…

    • #66589
      Tom Rioux
      Participant

      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

    • #66590
      Sergey Sevastyanov
      Participant

      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.

    • #66591
      Tom Rioux
      Participant

      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

    • #66592
      Jim Kosloskey
      Participant

      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

    • #66593
      Tom Rioux
      Participant

      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

    • #66594
      Sergey Sevastyanov
      Participant

      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

    • #66595
      Jim Kosloskey
      Participant

      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

    • #66596
      Tom Rioux
      Participant

      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

    • #66597
      Jim Kosloskey
      Participant

      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

    • #66598
      Tom Rioux
      Participant

      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

    • #66599
      Bill Tipton
      Participant

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

      -Bill

Viewing 16 reply threads
  • The forum ‘Cloverleaf’ is closed to new topics and replies.

Forum Statistics

Registered Users
4,968
Forums
28
Topics
9,109
Replies
33,637
Topic Tags
248