drbowen wrote on Monday, February 09, 2009:
I have a couple of scripts that I have been working on that import data from CSV file. The data was to exported from a Kareo system to OpenEMR. This worked pretty well but still has a couple of small bugs. For instance if the incoming sex is "M" that is what gets loaded. Whereas I believe that OpenEMR really wants "male" for this drop down list to work correctly.
All scripts like this have to be customized to some extent (unless you happen to be running Kareo). The variables like $data[1] refer to the field number in the CSV script.
I created a directory:
/openemr/interface/patient_file/history/kareo/
and placed the CSV file and the script in the same directory.
[Code starts here]
<?php
/*
Copyright (C) 2008 Sam Bowen, MD <drbowen@charter.net>
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.
/*
/* UploadKareo.php takes the demographinc information from a CSV file exported from
the Kareo office management Software and uploads it into the OpenEMR patient_data table.
The arguments on the command line are the name of the script "Kareo_upload.php
followed by the name of the file to upload such as KareoPatientDate.csv.
*/
include_once("…/…/…/globals.php");
include_once("…/…/…/…/library/adodb/adodb.inc.php");
$userauthorized =0;
$count=18;
$file=0;
$file="./KareoPtDataSample.csv";
//open the csv file from the command line with the file name as the second field
$handle = fopen($file, “r”) or die (“can’t open file”);
//call fgetcsv the first time to read the first line containing the field names.
//fgetcsv conveniently stores the column names with underscores inserted into the
//field names that contain spaces in the test CSV file.
//
//currently this just resets the pointer to the second line of the csv file.
$data = fgetcsv($handle, 100000);
//open the csv file again and loop through the contents
//$data is an array containing each field of the CSV which is read one line
//at a time.
while (($data = fgetcsv($handle, 100000)) !== FALSE) {
//Start the new ADODB connection to the database
$db = ADONewConnection(‘mysql’);
$db->debug = off;
$db->Connect($host,$login,$pass,$dbase);
//$db->Connect($host,$login,$dbase);
if (!$db) die(“Connection failed”);
$sql = "INSERT INTO patient_data (
id,
title,
language,
financial,
fname,
lname,
mname,
DOB,
street,
postal_code,
city,
state,
country_code,
drivers_license,
ss,
occupation,
phone_home,
phone_biz,
phone_contact,
phone_cell,
pharmacy_id,
status,
contact_relationship,
date,
sex,
referrer,
referrerID,
providerID,
email,
ethnoracial,
interpretter,
migrantseasonal,
family_size,
monthly_income,
homeless,
financial_review,
pubpid,
pid,
genericname1,
genericval1,
genericname2,
genericval2,
hipaa_mail,
hipaa_voice,
hipaa_notice,
hipaa_message,
hipaa_allowsms,
hipaa_allowemail,
squad,
fitness,
referral_source,
usertext1,
usertext2,
usertext3,
usertext4,
usertext5,
usertext6,
usertext7,
usertext8,
userlist1,
userlist2,
userlist3,
userlist4,
userlist5,
userlist6,
userlist7,
pricelevel,
regdate,
contrastart)
values (
‘".$count."’,
‘".$data[2]."’,
‘English’,
‘".$data[0]."’,
‘".$data[3]."’,
‘".$data[5]."’,
‘".$data[4]."’,
‘".$data[8]."’,
‘".$data[17]." “.$data[18].”’,
‘".$data[22]."’,
‘".$data[19]."’,
‘".$data[20]."’,
‘US’,
‘’,
‘".$data[7]."’,
‘".$data[13]."’,
‘".$data[23]."’,
‘".$data[25]."’,
‘’,
‘".$data[27]."’,
‘’,
‘".$data[12]."’,
‘’,
‘".$data[0]."’,
‘".$data[10]."’,
‘".$data[32]."’,
0,
1,
‘".$data[29]."’,
‘’,
‘’,
‘’,
‘’,
‘’,
‘’,
‘’,
‘".$data[1]."’,
‘".$data[1]."’,
‘’,
‘’,
‘’,
‘’,
‘YES’,
‘YES’,
‘YES’,
‘NO’,
‘NO’,
‘NO’,
0,
0,
‘".$data[32]."’,
0,
‘".$data[11]."’,
‘".$data[33]."’,
‘".$data[31]."’,
‘’,
‘’,
‘’,
‘’,
‘’,
‘’,
‘’,
‘’,
‘’,
‘’,
‘’,
‘Standard’,
‘".$data[0]."’,
‘’)";
$db->Execute($sql);
//change the pointer to the next row of the csv file.
$count++;
$row++;
}
//close the csv file
fclose($handle) or die (“can’t close file”);
exit;
?>
[/code stops here]
This script could be modified pretty easily to be run from the command line if desired. There are some idiosyncrasies of the MySQL connection that vary from one operating system to another and would have to be adjusted for your system.
Sincerely,
Sam Bowen, MD
