Dear House,
I need to know if it’s possible to upload up to 5,000 patients data on excel sheet containing only their names and addresses.
Am on 3.2 running on centos 5.5. I posted this same issue but cant find it on the forum any longer.
Long answer: I’ve done it before on a smaller scale. You go MS Excel document -> RFC 4180 document(s) -> Script you write yourself -> MySQL.
You take the Microsoft Excel document and convert it to one (or more) text/csv (RFC 4180) files. MS Excel is pretty poor at do that itself. OpenOffice (or the new LibreOffice) outputs better text/csv files and I found scripting it to be not too difficult. I wrote script to pick a directory and loop to save each sheet as a separate .csv based on it’s name, but my previous employer still has that.
Then, you’ll have to flex your scripting skills. I recommend PHP (so you can borrow code from OpenEMR), but any language that has facilities for reading text/csv and connecting to a MySQL database is fine. Your script doesn’t necessarily have to be very long, just loop over the CSV input and do INSERTs into the database. You don’t even actually have to have your script connect to the database, it might be able to just output the SQL statements.
If this is anything like most clinics, you’ll want to do data cleaning: some manual, in the MS Excel document; some automatic, in the PHP script. If you don’t, you’ll probably find one or two “oddly named” clients that might be suitable for an “Error’d” article on The Daily WTF.
The easiest way is to create the necessary SQL statement and execute it in phpmyadmin (as others have pointed out).
This is how I’ve dealt with a similar problem.
I exported the excel sheet into csv and opened it in excel.
I then wrote the sample sql for inserting a new patient record to match with the data in the original excel sheet.
something like. INSERT INTO patient_data (……) VALUES (……);
Use the above sql statement as a reference.Then, create additional columns between the csv (opened in excel) where ever you need to add necessary syntax. I usually put symbols like $ or # or something unique and use search/repace function to create the suitable syntax. Remember that you nullare working on a 5000 line csv file. So be patient in getting the query right.
To test the query, just try executing the first line. If it’s sucessful, delete the line from the csv, save it as an sql file and upload&execute it using phpmyadmin.
That’s a csv file saperating each column with a ‘,’. I put the $,%,# and @ symbols in excel sheet.
Then I replace the ‘$’ with INSERT into patient_data VALUES(
replace % with ‘,’
so on… to build the right syntax.
It took me about 20 min to dump about 6000 entries into the database.
Jeremy just finished writing a script for this which we used to do a data conversion for Medical Mastermind. He’s going to add some more documentation to it and then we will post it as a contributed utility.
-Tony
www/mi-squared.com