Importing pharmacy data

OEMR User
2013-12-28
2014-01-03
1 2 > >> (Page 1 of 2)
  • OEMR User

    OEMR User - 2013-12-28

    Hi,

    Rather than individually keying in the pharmacy (Aministration=>Practice=>Pharmacies=>Add a Pharmacy), Is there a way to import the pharmacy file (like in the ICD9 code file) for an average DIY user?

    I am setting up to use the free version of the AllScripts, but I would like to have the pharmacy list in OpenEMR also.

    Thanks for your help and have a great day.

     
    • Pieter W

      Pieter W - 2013-12-29

      There is. If it is not somewhere in the WIKI -pages we should make it a project.

      I did manage to make a list, but not easy since there are more than one table involved. If you feel like makenig this project a new Wiki-page worth, lets make a starter as soonj as I am back on home-base (about 2 weeks).

      Gr, Pimm

      To: 202504@discussion.openemr.p.re.sf.net
      From: van2010@users.sf.net
      Subject: [openemr:discussion] Importing pharmacy data
      Date: Sat, 28 Dec 2013 20:35:44 +0000

      Hi,

      Rather than individually keying in the pharmacy (Aministration=>Practice=>Pharmacies=>Add a Pharmacy), Is there a way to import the pharmacy file (like in the ICD9 code file) for an average DIY user?

      I am setting up to use the free version of the AllScripts, but I would like to have the pharmacy list in OpenEMR also.

      Thanks for your help and have a great day.

      Importing pharmacy data

      Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/openemr/discussion/202504/

      To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/

       
  • Pieter W

    Pieter W - 2013-12-29

    There is. If it is not somewhere in the WIKI-pages we should make it a project.

    I did manage to make a list, but not easy since there are more than one table involved. If you feel like making this project a new Wiki-page worth, lets make a starter as soon as I am back on home-base (about 2 weeks).

    Gr, Pimm

     
  • fsgl

    fsgl - 2013-12-29

    For a batch transfer of the list of the names and email addresses of local pharmacies used by a practice, a CSV file of that list can be imported into the pharmacies table in phpMyAdmin. The list of addresses would go into the addresses table. The list of phone numbers could be imported into the phone_numbers table.

    If one knows how to code, this exercise would be more streamlined than serial import into phpMyAdmin.

    If one does not know how to code, it may be simpler and less frustrating to enter the data manually than to use phpMyAdmin, especially if the number of pharmacies is less than ten.

     
    Last edit: fsgl 2013-12-30
  • Pieter W

    Pieter W - 2013-12-30

    Take a look at:

    http://open-emr.org/wiki/index.php/Pharmacy_Dispensary_Module

    If this does not solve your problems, feel free to ask for more advise.

    Importing to tables is a rather easy thing to do. Through any spreadsheet. This is also somewhere in the WIKI.

    Gr, Pimm

     
  • OEMR User

    OEMR User - 2014-01-01

    Dear Pimm and fsgl,

    Thank you for the information. I will search further in WIKI. I also appreciate your other posts.

    Have a Happy New Year.

     
  • fsgl

    fsgl - 2014-01-01

    A healthy and prosperous New Year to you, NewLife.

    The most relevant Wiki article to your situation is Code Types, but it does not tell you how to construct the .csv file for import. Each of the tables have multiple fields, therefore working with a .sql file will be trickier. Concatenation is not for the faint of heart. If that term is unfamiliar, definitely go the .csv route.

    Basically what you would do:
    1. Observe the structure of the tables.
    2. Line up the data in each of the columns of the spreadsheets.
    3. Import the respective .csv files into the tables.

     
  • Pieter W

    Pieter W - 2014-01-01

    SMALL ADDITION TO THIS EXCELLENT ANSWER:

    (You need to understand the basics of import, export and structure of CSV files)

    Use a clean OpenEMR, (any version,) preferably the latest. Start adding some drugs extensive as possible, in the OpenEMR part for Drugs, -all details and particulars-, drug seller if needed etc, at least three complete sets of rows.

    Than find the tables that have changes. Export these files in CSV format.

    Now compare with the AllScript list in any spreadsheet. Be sure to observe the Table ID-Key for OpenEMR CSV files. Take the used information of the OpenEMR tables to get the correct info of the AllScript file. Take all the little parts of the AllScript file that matches the Info of the OpenEMR files and connect the different parts with the correct ID-Keys.

    Import the CSV files in the correct Tables and be sure to exclude the first three entered lines of your own first included medications.

    Sounds complicated? It might sound like, but as long as you stick to the little parts and make changes in the CSV to match the length and amount of fields it should work. Every row of every table should have the correct ID-Keys to make the correct connections.

    I did import CSV files in OpenEMR of about 5000 rows (not the drug tables, but ok for comparison) and this should be enough for most medical professions.

     
    Last edit: Pieter W 2014-01-01
  • David H

    David H - 2014-01-02

    I think the original poster was simply asking how to automate the insertion of pharmacy information in the practice section. My technique follows, although I use it on a Mac so it probably would work on a Linux system but might require some minor modification for Windows. In the example there is a CSV text file on the desktop named "pharmacies.txt". You can pattern your insertion data after the attached file then run the code. It will automatically insert the pharmacy data, address, phone and fax numbers and then update the sequences.id number.

    Code:
    -- / Create temporary OpenEMR table for importing pharmacies /
    CREATE TABLE openemr.t_pharm_import (
    id smallint,
    name varchar(76),
    address1 varchar(76),
    address2 varchar(76),
    city varchar(36),
    state varchar(2),
    zip varchar(10),
    areacode varchar(3),
    prefix varchar(3),
    number varchar(4),
    faxareacode varchar(3),
    faxprefix varchar(3),
    faxnumber varchar(4),
    transmit_method smallint default 1,
    email varchar(36),
    primary key (id)
    );

    -- / needs UNIX-style new-lines at end of each line and no smart-quotes (as written - this can be modified) /
    LOAD DATA LOCAL INFILE '~/desktop/pharmacies.txt'
    INTO TABLE openemr.t_pharm_import
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'"
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES;

    SET @i = (SELECT id FROM openemr.sequences);

    -- / import basic pharmacy data /
    INSERT INTO openemr.pharmacies(id, name, transmit_method, email)
    SELECT @i+((id*4)-3), name, transmit_method, email
    FROM openemr.t_pharm_import
    ORDER BY openemr.t_pharm_import.id;
    SELECT * FROM openemr.pharmacies;

    -- / import addresses /
    INSERT INTO openemr.addresses(id, line1, line2, city, state, zip, plus_four, country, foreign_id)
    SELECT @i+((id4)-2), address1, address2, city, state, zip, NULL, 'USA', @i+((id4)-3)
    FROM openemr.t_pharm_import
    ORDER BY openemr.t_pharm_import.id;
    SELECT * FROM openemr.addresses;

    -- / import phone numbers /
    INSERT INTO openemr.phone_numbers(id, country_code, area_code, prefix, number, type, foreign_id)
    SELECT @i+((id4)-1), '+1', areacode, prefix, number, 2, @i+((id4)-3)
    FROM openemr.t_pharm_import
    ORDER BY openemr.t_pharm_import.id;
    SELECT * FROM openemr.phone_numbers;

    -- / import fax numbers /
    INSERT INTO openemr.phone_numbers(id, country_code, area_code, prefix, number, type, foreign_id)
    SELECT @i+(id4), '+1', faxareacode, faxprefix, faxnumber, 5, @i+((id4)-3)
    FROM openemr.t_pharm_import
    ORDER BY openemr.t_pharm_import.id;
    SELECT * FROM openemr.phone_numbers;

    -- / reset sequences.id to proper value (contingent upon number of pharmacies added /
    Set @i = 4*(select max(id) from openemr.t_pharm_import);
    UPDATE openemr.sequences SET id = id + @i;

    DROP TABLE openemr.t_pharm_import;

     
    Last edit: David H 2014-01-02
  • Pieter W

    Pieter W - 2014-01-02

    I want to make apologies for the mistake to explain not the import of Pharmacy Data and I tried to explain what was needed to make your own medication files and import this in OpenEMR.

    CSV import of the Pharmacy data is a great alternative for your script. It just shows there are more ways to make a computer do what you want.

     
  • fsgl

    fsgl - 2014-01-02

    NewLife,

    David has demonstrated how professionals would import the data. Considerable time and effort have gone into his post.

    If OpenEMR has been installed on a Windows machine and Command Prompt is terra incognita, here are 2 helpful links for non-professionals (does that make us amateurs?):
    1. How to create a CSV file.
    2. PHPmyAdmin importing csv file to MySql database.

     
  • David H

    David H - 2014-01-02

    Thanks for the glowing compliment. I am indeed a professional, but only as a physician. With respect to computers I'm entirely self-taught. My motto: Keep pecking, even a blind robin gets a worm occasionally.

    At any rate, I've now managed to almost entirely export my old billing software's database (it used Pervasive SQL) into a temporary MySQL database and then reimport the relevant portions of that data (preserving the associations between encounters and payments, etc.) into the OEMR database. It's been time-consuming and difficult because the old system stored data in different tables and in a different fashion with different relationships. On the other hand, I've now done it so many times that I can almost do it in my sleep but, fortunately, I do keep notes. As such, it was relatively quick to de-personify a few sample files and post the notes here.

    To the rest of you out there... Keep up the good work.

    P.S. Thanks for the links. They should help someone else too. I had to make up all this stuff on the fly.

     
  • fsgl

    fsgl - 2014-01-02

    Refreshing to see that modesty and evidence of a classical education (Fields of Elysium = Elysian Fields = Champs-Élysées) are alive and well. One of the pharmacies must be Parisian.

     
  • David H

    David H - 2014-01-02

    Ah, my classical education has gone largely to waste. Now I'm stuck with the following Lazy Latin...

    Popoculus nautus sum,
    Popoculus nautus sum,
    Pugnabo al finem quod edero spinem,
    Popoculus nautus sum!

    Any translators? I'll be watching.

     
  • fsgl

    fsgl - 2014-01-02


    Without Latin, how would one distinquish a dura mater or a pia mater from an alma mater, absent an explanation?

     
    Last edit: fsgl 2014-01-03
  • Pieter W

    Pieter W - 2014-01-02

    The question was:

    O Oracle magnus, quid est veritas?

    Fluffy Oracle, did you know that you can learn the most amazing stuff on the Internet? For instance, I learned that you look just like Harvey, the invisible rabbit! Now I begin to understand how you can be so smart and not show it, all at the same time.

    I have a rabbit, but he's a lot smaller than Harvey. What I need to know is, how can I keep my friends from trying to pick him up by the ears. (This will seriously injure or kill a rabbit, and you well know.) I yell at them, and they laugh, and go for his ears. I have to bop them in the face to make them stop. I'm losing friends very fast. Apparently I am neither pleasant nor smart. Please advise alternate methods.

    But is this what you are looking for:

    I'm Popeye the Sailor Man
    I'm Popeye the Sailor Man
    I'm strong to the finish
    'Cuz I eats me spinach
    I'm Popeye the Sailor Man!

    As you can see I am not a Classic. You need fsgl for more power translations.

     
  • David H

    David H - 2014-01-02

    Very good. My work is done. I think I've successfully hi-jacked this thread. My apologies to NewLife. I hope we were able to help.

     
  • OEMR User

    OEMR User - 2014-01-02

    You all are funny! Thank you for the laughter... Does it mean that I need to post more questions in order to read more of your side subjects (beside OpenEMR)? just kidding...

    Thank you all for the helps. This importing information is very valuable for me since I am starting to use OpenEMR and need to extract demographic data from my old software (Medisoft) and eventually import to OpenEMR. I am a DIYer and a self-taught individual also, and OpenEMR creates a new challenge for me. I'm excited about it, and I hope to read and learn more of your teachings on this matter both professionally and non-proffessionally in the future...

    Happy New Year to all.

     
  • Pieter W

    Pieter W - 2014-01-02

    After this little side step, please feel free to continue explaining your stepwise inclusion of the old towards the new OpenEMR conversions.

    Although it seems as if this is only important for you, many OpenEMR users to be, might find the same helpful hints as you found in this Forum, and eventually might lead towards an own WIKI page with the bare essentials needed to make the change from one product to the OpenEMR product.

    Make my eyes pop eye.....

     
  • David H

    David H - 2014-01-02

    NewLife: I've been messing around for about 1 1/2 years with the conversion. Unfortunately, I was stopped for about 9 months in the middle due to a family tragedy. I view all this as something of a big puzzle to be figured out. It drives my wife nuts but every day I manage a little more progress. I'm hoping to fully convert my systems and be up and running in OEMR before the end of the month. Honestly, I think I could do it now but I'm hesitant to upset the way things are running in the office. I've come to a conclusion about two things. First, almost anything is possible with enough diligence and exploration. Second, Google is your friend - you just have to figure out which question to ask (that's the hard part).

    Pieter W: I'd be happy to discuss my conversion process however I use an antiquated version of a system called POM (Physician Office Manager) that works well but hasn't been updated in years. The principles behind the conversion would probably remain similar across different platforms, however. My main problem is that there are so many questions and no easy place to ask them all without getting confused by the variety of responses. Furthermore, an answer that works with Pervasive SQL (the older version that I use) doesn't always work with MySQL. And that's just the data aspects. Then we get into the web design and modifications to make the system more applicable to my particular specialty. All in all, I've made amazing progress and feel, as noted above, that I could go live now. I wouldn't even begin to know how to put this all into a WIKI, however. As such, my currently preferred method is simply to sit back and follow the forum threads, adding in whatever little pearls I've discovered when someone asks a question about something to which I feel competent to respond (that's a debatable concept but we'll leave it for now). The nice part about this is that one could take the methods by which I insert pharmacy data and easily modify them to insert other types of data (insurance companies, active patients, referring physicians, etc.). The only important key to remember is that FOR THOSE TABLES WHOSE KEYS DON'T AUTO_INCREMENT IT IS USUALLY IMPORTANT TO RESET THE sequences.id VALUE (I learned that the hard way - so I remember it!). I capped that last statement so someone doesn't just scan the thread and start messing around without understanding the repercussions.

     
  • fsgl

    fsgl - 2014-01-03

    NewLife,

    Once the question has been answered, we try to have fun. All work and no play...

    The Demo's are great places to experiment. The 4.1.1 Demo is better than the others, if there is a question about which table to use, because one does not have to deal with the drop down menu. Simply add a sample piece of datum and then look to see where it lands.

    In the event that you are unaware, this DIY Wiki article may be helpful, especially in matters relating to Billing.


    David,

    The transition from another EHR, without professional support, requires a great deal of patience and perseverance; but the greater the effort, the greater the sense of accomplishment.

    We, as practicing physicians, bring to this project a valuable perspective that our colleagues in IT don't have. Therefore, if we are undaunted enough to contribute codes that had been informed by our clinical experience, this would make OpenEMR vastly superior and uniquely priceless to the larger medical community.

     
  • David H

    David H - 2014-01-03

    For the record, since I'm not live yet I have found it most convenient to simply set up a localhost copy of OEMR on my laptop. I can do all my experimenting on that and, since I've become quite proficient at making and restoring from backups, I can do all the experimenting I like without having to deal with internet access and speed issues (I live where it's warm, do most of my stuff on my laptop and frequently mess around in the evenings on my lounge chair in the front yard, where internet access is sometimes marginal). Also, this limits the amount of things, tables and data I have to look at to the changes that I personally have made and I don't have to get bogged down looking at others' contributions unless I want to (although it's sometimes helpful to understand that not everyone does things the same way). In fact, as I consider this to be an ongoing project with no realistic limitations I'll probably maintain two separate databases, one working and the other experimental, even after I go live. That way I'll be able to experiment to my heart's content without the chance of corrupting my precious data.

    BTW, I did notice that on one particular (I think older, 4.1.1?) demo I was unable to view the database tables even when logged in as admin. That severely limited its utility to me.

    As a side note, patience and perseverance have been the name of the game. My personal paranoia is such that I don't want to be held hostage to any particular company for "support" for a system after it's installed. I view the rather considerable time investment I've made in understanding HTML, PHP, MySQL and OEMR not only as a fairly geeky hobby and/or waste of time but also as my hedge against continuing annual support charges. After all, I've now become a reasonably competent, although limited, systems and database administrator and have learned how to create and fill my own tables and insert them into the OEMR system (as an ENT I find much of the Primary Care stuff not relevant to my practice) so as to bend the system to my will. I've personally set up the network in my office (including even stringing the ethernet cable and crimping on the connectors). There's no reason why I can't be my own support if I know and understand every aspect of how the system works (particularly if I can ask questions in this forum).

     
  • Pieter W

    Pieter W - 2014-01-03

    Great review of your own investment in OpenEMR. This should have a place in the thumbs-up page in WIKI!

     
  • David H

    David H - 2014-01-03

    NewLife,

    Getting back on topic here. Do you have any idea what type of database software Medisoft uses? and what is the general database structure (table names and how they are related)? In my particular conversion from POM to OEMR I discovered that POM used an older version of Pervasive SQL (which was similar although not identical to MySQL) and that of the hundred or so tables within the database only seven or so were important enough to be relevant to the conversion (contained patient data, referring physician data, encounter data, billing data, payment data, accounts-receivable data and so forth). From there, I had to experiment with different SQL select statements, most of which involved INNER JOINS (and a few LEFT JOINS), to couple the data in the various tables (for example, coupling a patient id number to an encounter to a service to a date to a fee to a diagnosis to a bill to a payment to a residual balance...) so I could extract the data I needed either directly to the relevant table in OEMR or to a temporary table from which it could be accessed later. I have no doubt that there are more efficient ways to do this but my way is working for me (so far).

    If the above doesn't look somewhat familiar you'll need to do some research on the internet. Try googling "mysql tutorial" or "mysql select syntax" or whatever and do some basic reading. I've had pretty good luck with www.w3schools.com and www.mysqltutorial.org but there are others as well. A good MySQL reference text could be helpful as well but I do much of my work on the fly and it's inconvenient to be dragging large texts around. Furthermore, the top few google hits will usually point you in the right direction a lot quicker than trying to find the right entry in a large text's index. It's pretty daunting at first but before long you'll have at least a basic knowledge base skeleton upon which you can hang additional little tidbits. Then it begins to become a little more fun.

    Good luck and keep us posted...

     
    Last edit: David H 2014-01-03
  • David H

    David H - 2014-01-03

    I might add a few other things. At least when you're experimenting you will save a lot of time if you can pare down the data in the Medisoft tables. Otherwise you'll likely have hundreds of thousands of entries, the vast majority of which are closed accounts and will not necessarily need to be imported (but nevertheless still slow things down). As such, a simple line such as "DELETE FROM [tablename] WHERE [date] < '2011-01-01'" will get rid of the old data. Obviously, you'll need to select an appropriate cut-off date and make certain that anything less than the date in question has been zeroed out and is no longer relevant. DO NOT DO THIS TO YOUR ORIGINAL TABLES - YOU WILL LOSE YOU DATA! It's best to export all the data first (or selectively export the data) and then fiddle with it later outside the realm of whatever database is holding your Medisoft data.

     
1 2 > >> (Page 1 of 2)

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks