Import CSV Files

tommckellips wrote on Monday, January 23, 2006:

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.

andres_paglayan wrote on Monday, January 23, 2006:

please give more info,
how are you importing, phpmyadmin or using a source?
have you checked end of line compatibility problems,
such as CR/LF

tommckellips wrote on Monday, January 23, 2006:

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.

drbowen wrote on Monday, January 23, 2006:

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:

$hyphen="-";
$mysql_date="0";
$row = 1;
$handle = fopen("test.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
   $num = count($data);
      $row++;
         $account=$data[0];
         $date=$data[1];

         list($month, $day, $year) = split(’[/.-]’, $date);
         //echo $account . “<br />\n”;
         $mysql_date = $year . $hyphen . $month . $hyphen . $day;
         //echo $mysql_date . “<br />\n”;
         mysql_connect($host,$login,$pass,$dbase);
         @mysql_select_db($dbase) or die(“OpenEMR database connection error”);

         $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);

echo "Finished";

Sam Bowen

drbowen wrote on Tuesday, January 24, 2006:

Well, about what I should expected.  Source Forge won’t allow all the special characters.

&quot; means double quote

The heart of the program is the PHP  fgetcsv  function.

This is described at:

http://www.php.net/manual/en/function.fgetcsv.php

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.

Sam Bowen

tommckellips wrote on Friday, January 27, 2006:

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?

andres_paglayan wrote on Friday, January 27, 2006:

yes, unless you use a query for inserting,
(such as insert into table set …

tommckellips wrote on Saturday, January 28, 2006:

Ok among other things I am brand new to OpenEmr and to PHP. Where would I put this script?

drbowen wrote on Monday, January 30, 2006:

Dear Tom,

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.