I have been having a nightmare of a time trying to import a CSV into the patient db. I have tried using commas and semicolons as the delimeter. But no sucess. I made sure that I had delimiters fro empty fields and no sucess. I thought my file was to big so a createad a 3 line file and still no sucess.
These are my 3 test lines in a notepad file
;;;"john";"rockef5eller";"d";1992-12-05;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;
;;;"john";"rockef2eller";"d";1992-12-05;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;
;;;"john";"rockef3eller";"d";1992-12-05;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;
One time I got this to import the first line but then nothing else.
Anyone have any idea at all why this does not work? I have added and deleted delimeters I have limitied to just a name and nothing else.
I am using the phpmyadmin. ANd the file is hand written in windows notepad. I just hit enter at the end of each line and try imports that way. I also have just tried putting \r\n at the end of each line and still no sucess.
Help me understand, if you are using a csv file, why are your field delimiters showing as semicolons? There is no way SQL statements will work with all these semicolons. The semicolon is the normal end-of-line character in SQL.
Things are a bit different in linux versus Windows. "Edit" is actually the cleanest editor in the Windows arsenal though notepad is a close second.
Are you getting errors reported in the Apache error log?
I recently used a csv file to repair my database and wrote a PHP script to do it. Very fast and effective. (With Rod’s help). Here is the script if the SourceForge Filter doesn’t hack it to death first:
$res=mysql_query(“UPDATE patient_data SET DOB=’”.$mysql_date."’ where pid=’".$account."’" )
or die(“SQL ERROR in line “.__LINE__.”, function mysql_query”);
mysql_free_result($res);
mysql_close();
}
fclose($handle);
Per Rod’s recommendation I just copied and pasted it into my script.
In pseudocode:
Open the CSV file.
split the line into an array with fgetcsv
Open a database connection
Insert the values into the database
close the database connection
free the result
Then move recursively through the CSV file line by line.
This may be the cleanest way but it definitely works.
I have used the semicolons becuase that is what the myadmin shows fro a default delimiter on it’s screen. I also changed that to a comma and changed the file to be delimited by commas and still had no luck with it. I will pull up Edit and write a few new test lines and try again. Do I have to have as many fields in my file as the sql table has?
I can send you the script I wrote. You will need to modify it to include all your fields.
I just put the csv file and my script in the openemr directory tree ~webroot/openemr/interface/patient_files/history/
There is nothing special about this directory except it is inside the ~webroot path. This way I set up just like the rest of the other OpenEMR pages.
You should be able to do the same thing with command line PHP. I used the former because it generated errors in the Apache error_log that used for debugging.