van2010 wrote on Saturday, December 28, 2013:
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.
blankev wrote on Sunday, December 29, 2013:
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
blankev wrote on Sunday, December 29, 2013:
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/
fsgl wrote on Sunday, December 29, 2013:
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.
blankev wrote on Monday, December 30, 2013:
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
van2010 wrote on Wednesday, January 01, 2014:
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 wrote on Wednesday, January 01, 2014:
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:
blankev wrote on Wednesday, January 01, 2014:
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.
blatta wrote on Thursday, January 02, 2014:
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+((id4)-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+((id*4)-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+((id*4)-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+((id*4)-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;
blankev wrote on Thursday, January 02, 2014:
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 wrote on Thursday, January 02, 2014:
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?):
blatta wrote on Thursday, January 02, 2014:
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 wrote on Thursday, January 02, 2014:
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.
blatta wrote on Thursday, January 02, 2014:
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 wrote on Thursday, January 02, 2014:
[[embed url=http://www.youtube.com/watch?v=i8O0PwGSoO0]]
Without Latin, how would one distinquish a dura mater or a pia mater from an alma mater, absent an explanation?
blankev wrote on Thursday, January 02, 2014:
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.
blatta wrote on Thursday, January 02, 2014:
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.
van2010 wrote on Thursday, January 02, 2014:
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.
blankev wrote on Thursday, January 02, 2014:
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…
blatta wrote on Thursday, January 02, 2014:
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.