hitechelp wrote on Saturday, March 10, 2012:
I’m trying to load ICD9 codes using the following instructions from the wiki “Load_ICD9_Codes” page
LINUX INSTRUCTIONS
Modify the openemr/contrib/util/load_icd_desc.plx file:
Place database name at line 36 (get this from the openemr/library/sqlconf.php file)
Comment out line 45 and 46
Uncomment line 48 and 49, and substitute the your sql username and database for username
and password in this line (line 48). (get these from theopenemr/library/sqlconf.php file )
Execute the script:
perl /var/www/openemr/contrib/util/load_icd_desc.plx
First off: the database name is not in the openemr/library/sqlconf.php file
I was able to hunt down the sqlconf.php file in the /var/www/openemr/sites/default/ directory but
this was not clearly indicated by the openemr/library/sqlconf.php file, it would be simple to point
it out clearly in the first place. (things like this call the entire procedure into question.)
Secondly: Same issue for “the your sql username and database” for username and password" part of
the instructions. Edited as directed, the script bombs with the following error(s)
> sudo perl /var/www/openemr/contrib/util/load_icd_desc.plx
DBI connect(‘dbname=openemr’,‘openemr’,…) failed: Access denied for user ‘openemr’@‘localhost’
(using password: YES) at /var/www/openemr/contrib/util/load_icd_desc.plx line 48
Access denied for user ‘openemr’@‘localhost’ (using password: YES) at
/var/www/openemr/contrib/util/load_icd_desc.plx line 48.
Here is my /var/www/openemr/sites/default/sqlconf.php
<?php
// OpenEMR
// MySQL Config
$host = ‘localhost’;
$port = ‘3306’;
$login = ‘openemr’;
$pass = ‘euIatxiisQwm’;
$dbase = ‘openemr’;
//Added ability to disable
//utf8 encoding - bm 05-2009
global $disable_utf8_flag;
$disable_utf8_flag = false;
$sqlconf = array();
global $sqlconf;
$sqlconf= $host;
$sqlconf = $port;
$sqlconf = $login;
$sqlconf = $pass;
$sqlconf = $dbase;
//////////////////////////
//////////////////////////
//////////////////////////
//////DO NOT TOUCH THIS///
$config = 1; /////////////
//////////////////////////
//////////////////////////
//////////////////////////
?>
Here is load_icd_desc.plx as used
#!/usr/bin/perl
use strict;
#######################################################################
# Copyright (C) 2007-2010 Rod Roark <rod@sunsetsystems.com>
#
# 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.
#######################################################################
# This loads ICD9 codes and descriptions into the “codes” table of
# OpenEMR, scraping from from www.icd9data.com.
# Alternatively you can just dump the INSERT statements to stdout.
#######################################################################
# You might need to install one or more of these dependencies.
# The Debian/Ubuntu package names are noted as comments:
#
use DBI; # libdbi-perl and libdbd-mysql-perl
use WWW::Mechanize; # libwww-mechanize-perl
use HTML::TokeParser; # libhtml-parser-perl
#######################################################################
# Parameters that you may customize #
#######################################################################
# Change this as needed for years other than 2010.
#
my $START_URL = “http://www.icd9data.com/2010/Volume1/default.htm”;
# An empty database name will cause SQL INSERT statements to be dumped
# to stdout, with no database access. To update your OpenEMR database
# directly, specify its name here.
#
my $DBNAME = “openemr”;
# You can hard-code the database user name and password (see below),
# or else put them into the environment with bash commands like these
# before running this script:
#
# export DBI_USER=username
# export DBI_PASS=password
#
#my $dbh = DBI->connect(“dbi:mysql:dbname=$DBNAME”) or die $DBI::errstr
# if ($DBNAME);
my $dbh = DBI->connect(“dbi:mysql:dbname=$DBNAME”, “openemr”, “openemr”)
or die $DBI::errstr if ($DBNAME);
# Comment this out if you want to keep old nonmatching codes.
#
#$dbh->do(“delete from codes where code_type = 2”) or die “oops”
# if ($DBNAME);
#######################################################################
# Startup #
#######################################################################
$| = 1; # Turn on autoflushing of stdout.
my $countup = 0;
my $countnew = 0;
#######################################################################
# Main Logic #
#######################################################################
# This function recursively scrapes all of the web pages.
#
sub scrape {
my $url = shift;
my $browser = WWW::Mechanize->new();
$browser->get($url);
my $parser = HTML::TokeParser->new($browser->content());
while(my $tag = $parser->get_tag(“li”, “div”)) {
# The <li><a> sequence is recognized as a link to another list
# that must be followed. We handle those recursively.
if ($tag-> eq “li”) {
$tag = $parser->get_tag;
$tag = $parser->get_tag if ($tag-> eq “strong”);
next unless ($tag-> eq “a”);
my $nexturl = $browser->base();
# $nexturl =~ s’/+$’/’;
$nexturl =~ s’/20.+$’’;
scrape($nexturl . $tag->{href});
}
# The <div><img> sequence starts an ICD9 code and description.
# If the “specific green” image is used then we know this code is
# valid as a specific diagnosis, and we will grab it.
else {
$tag = $parser->get_tag;
next unless ($tag-> eq “img”);
next unless ($tag->{src} =~ /SpecificGreen/);
$tag = $parser->get_tag(“a”);
my $tmp = $parser->get_trimmed_text;
unless ($tmp =~ /Diagnosis Code (\S+)/) {
print STDERR “Parse error in ‘$tmp’ at $url\n”;
next;
}
my $code = $1;
$tag = $parser->get_tag(“div”);
my $desc = $parser->get_trimmed_text;
$desc =~ s/’/’’/g; # some descriptions will have quotes
# This creates the needed SQL statement, and optionally writes the
# code and its description to the codes table.
my $query = "INSERT INTO codes " .
"( code_type, code, modifier, code_text ) VALUES " .
“( 2, ‘$code’, ‘’, ‘$desc’ )”;
if ($DBNAME) {
my $usth = $dbh->prepare("SELECT id FROM codes " .
“WHERE code_type = 2 AND code = ‘$code’”)
or die $dbh->errstr;
$usth->execute() or die $usth->errstr;
my @urow = $usth->fetchrow_array();
if (! @urow) {
++$countnew;
}
else {
$query = "UPDATE codes SET code_text = ‘$desc’ " .
“WHERE code_type = 2 AND code = ‘$code’”;
++$countup;
}
$dbh->do($query) or die $query;
}
print $query . “;\n”;
}
}
}
# This starts the ball rolling.
scrape($START_URL);
#######################################################################
# Shutdown #
#######################################################################
if ($DBNAME) {
print “\nInserted $countnew rows, updated $countup codes.\n”;
$dbh->disconnect;
}
After some careful thought, I set the username to “root” and the password to null “”,
The script ran for about a half hour and did not return any errors but the “service”
page in openemr only listed the first three ICD9 codes.
Any suggestions?
Regards,
David
I’m trying to load ICD9 codes using the following instructions from the wiki “Load_ICD9_Codes” page
LINUX INSTRUCTIONS
Modify the openemr/contrib/util/load_icd_desc.plx file:
Place database name at line 36 (get this from the openemr/library/sqlconf.php file)
Comment out line 45 and 46
Uncomment line 48 and 49, and substitute the your sql username and database for username
and password in this line (line 48). (get these from theopenemr/library/sqlconf.php file )
Execute the script:
perl /var/www/openemr/contrib/util/load_icd_desc.plx
First off: the database name is not in the openemr/library/sqlconf.php file
I was able to hunt down the sqlconf.php file in the /var/www/openemr/sites/default/ directory but
this was not clearly indicated by the openemr/library/sqlconf.php file, it would be simple to point
it out clearly in the first place. (things like this call the entire procedure into question.)
Secondly: Same issue for “the your sql username and database” for username and password" part of
the instructions. Edited as directed, the script bombs with the following error(s)
> sudo perl /var/www/openemr/contrib/util/load_icd_desc.plx
DBI connect(‘dbname=openemr’,‘openemr’,…) failed: Access denied for user ‘openemr’@‘localhost’
(using password: YES) at /var/www/openemr/contrib/util/load_icd_desc.plx line 48
Access denied for user ‘openemr’@‘localhost’ (using password: YES) at
/var/www/openemr/contrib/util/load_icd_desc.plx line 48.
Here is my /var/www/openemr/sites/default/sqlconf.php
<?php
// OpenEMR
// MySQL Config
$host = ‘localhost’;
$port = ‘3306’;
$login = ‘openemr’;
$pass = ‘euIatxiisQwm’;
$dbase = ‘openemr’;
//Added ability to disable
//utf8 encoding - bm 05-2009
global $disable_utf8_flag;
$disable_utf8_flag = false;
$sqlconf = array();
global $sqlconf;
$sqlconf= $host;
$sqlconf = $port;
$sqlconf = $login;
$sqlconf = $pass;
$sqlconf = $dbase;
//////////////////////////
//////////////////////////
//////////////////////////
//////DO NOT TOUCH THIS///
$config = 1; /////////////
//////////////////////////
//////////////////////////
//////////////////////////
?>
Here is load_icd_desc.plx as used
#!/usr/bin/perl
use strict;
#######################################################################
# Copyright (C) 2007-2010 Rod Roark <rod@sunsetsystems.com>
#
# 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.
#######################################################################
# This loads ICD9 codes and descriptions into the “codes” table of
# OpenEMR, scraping from from www.icd9data.com.
# Alternatively you can just dump the INSERT statements to stdout.
#######################################################################
# You might need to install one or more of these dependencies.
# The Debian/Ubuntu package names are noted as comments:
#
use DBI; # libdbi-perl and libdbd-mysql-perl
use WWW::Mechanize; # libwww-mechanize-perl
use HTML::TokeParser; # libhtml-parser-perl
#######################################################################
# Parameters that you may customize #
#######################################################################
# Change this as needed for years other than 2010.
#
my $START_URL = “http://www.icd9data.com/2010/Volume1/default.htm”;
# An empty database name will cause SQL INSERT statements to be dumped
# to stdout, with no database access. To update your OpenEMR database
# directly, specify its name here.
#
my $DBNAME = “openemr”;
# You can hard-code the database user name and password (see below),
# or else put them into the environment with bash commands like these
# before running this script:
#
# export DBI_USER=username
# export DBI_PASS=password
#
#my $dbh = DBI->connect(“dbi:mysql:dbname=$DBNAME”) or die $DBI::errstr
# if ($DBNAME);
my $dbh = DBI->connect(“dbi:mysql:dbname=$DBNAME”, “openemr”, “openemr”)
or die $DBI::errstr if ($DBNAME);
# Comment this out if you want to keep old nonmatching codes.
#
#$dbh->do(“delete from codes where code_type = 2”) or die “oops”
# if ($DBNAME);
#######################################################################
# Startup #
#######################################################################
$| = 1; # Turn on autoflushing of stdout.
my $countup = 0;
my $countnew = 0;
#######################################################################
# Main Logic #
#######################################################################
# This function recursively scrapes all of the web pages.
#
sub scrape {
my $url = shift;
my $browser = WWW::Mechanize->new();
$browser->get($url);
my $parser = HTML::TokeParser->new($browser->content());
while(my $tag = $parser->get_tag(“li”, “div”)) {
# The <li><a> sequence is recognized as a link to another list
# that must be followed. We handle those recursively.
if ($tag-> eq “li”) {
$tag = $parser->get_tag;
$tag = $parser->get_tag if ($tag-> eq “strong”);
next unless ($tag-> eq “a”);
my $nexturl = $browser->base();
# $nexturl =~ s’/+$’/’;
$nexturl =~ s’/20.+$’’;
scrape($nexturl . $tag->{href});
}
# The <div><img> sequence starts an ICD9 code and description.
# If the “specific green” image is used then we know this code is
# valid as a specific diagnosis, and we will grab it.
else {
$tag = $parser->get_tag;
next unless ($tag-> eq “img”);
next unless ($tag->{src} =~ /SpecificGreen/);
$tag = $parser->get_tag(“a”);
my $tmp = $parser->get_trimmed_text;
unless ($tmp =~ /Diagnosis Code (\S+)/) {
print STDERR “Parse error in ‘$tmp’ at $url\n”;
next;
}
my $code = $1;
$tag = $parser->get_tag(“div”);
my $desc = $parser->get_trimmed_text;
$desc =~ s/’/’’/g; # some descriptions will have quotes
# This creates the needed SQL statement, and optionally writes the
# code and its description to the codes table.
my $query = "INSERT INTO codes " .
"( code_type, code, modifier, code_text ) VALUES " .
“( 2, ‘$code’, ‘’, ‘$desc’ )”;
if ($DBNAME) {
my $usth = $dbh->prepare("SELECT id FROM codes " .
“WHERE code_type = 2 AND code = ‘$code’”)
or die $dbh->errstr;
$usth->execute() or die $usth->errstr;
my @urow = $usth->fetchrow_array();
if (! @urow) {
++$countnew;
}
else {
$query = "UPDATE codes SET code_text = ‘$desc’ " .
“WHERE code_type = 2 AND code = ‘$code’”;
++$countup;
}
$dbh->do($query) or die $query;
}
print $query . “;\n”;
}
}
}
# This starts the ball rolling.
scrape($START_URL);
#######################################################################
# Shutdown #
#######################################################################
if ($DBNAME) {
print “\nInserted $countnew rows, updated $countup codes.\n”;
$dbh->disconnect;
}
After some careful thought, I set the username to “root” and the password to null “”,
The script ran for about a half hour and did not return any errors but the “service”
page in openemr only listed the first three ICD9 codes.
Any suggestions?
Regards,
David
I’m trying to load ICD9 codes using the following instructions from the wiki “Load_ICD9_Codes” page
LINUX INSTRUCTIONS
Modify the openemr/contrib/util/load_icd_desc.plx file:
Place database name at line 36 (get this from the openemr/library/sqlconf.php file)
Comment out line 45 and 46
Uncomment line 48 and 49, and substitute the your sql username and database for username
and password in this line (line 48). (get these from theopenemr/library/sqlconf.php file )
Execute the script:
perl /var/www/openemr/contrib/util/load_icd_desc.plx
First off: the database name is not in the openemr/library/sqlconf.php file
I was able to hunt down the sqlconf.php file in the /var/www/openemr/sites/default/ directory but
this was not clearly indicated by the openemr/library/sqlconf.php file, it would be simple to point
it out clearly in the first place. (things like this call the entire procedure into question.)
Secondly: Same issue for “the your sql username and database” for username and password" part of
the instructions. Edited as directed, the script bombs with the following error(s)
> sudo perl /var/www/openemr/contrib/util/load_icd_desc.plx
DBI connect(‘dbname=openemr’,‘openemr’,…) failed: Access denied for user ‘openemr’@‘localhost’
(using password: YES) at /var/www/openemr/contrib/util/load_icd_desc.plx line 48
Access denied for user ‘openemr’@‘localhost’ (using password: YES) at
/var/www/openemr/contrib/util/load_icd_desc.plx line 48.
Here is my /var/www/openemr/sites/default/sqlconf.php
<?php
// OpenEMR
// MySQL Config
$host = ‘localhost’;
$port = ‘3306’;
$login = ‘openemr’;
$pass = ‘euIatxiisQwm’;
$dbase = ‘openemr’;
//Added ability to disable
//utf8 encoding - bm 05-2009
global $disable_utf8_flag;
$disable_utf8_flag = false;
$sqlconf = array();
global $sqlconf;
$sqlconf= $host;
$sqlconf = $port;
$sqlconf = $login;
$sqlconf = $pass;
$sqlconf = $dbase;
//////////////////////////
//////////////////////////
//////////////////////////
//////DO NOT TOUCH THIS///
$config = 1; /////////////
//////////////////////////
//////////////////////////
//////////////////////////
?>
Here is load_icd_desc.plx as used
#!/usr/bin/perl
use strict;
#######################################################################
# Copyright (C) 2007-2010 Rod Roark <rod@sunsetsystems.com>
#
# 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.
#######################################################################
# This loads ICD9 codes and descriptions into the “codes” table of
# OpenEMR, scraping from from www.icd9data.com.
# Alternatively you can just dump the INSERT statements to stdout.
#######################################################################
# You might need to install one or more of these dependencies.
# The Debian/Ubuntu package names are noted as comments:
#
use DBI; # libdbi-perl and libdbd-mysql-perl
use WWW::Mechanize; # libwww-mechanize-perl
use HTML::TokeParser; # libhtml-parser-perl
#######################################################################
# Parameters that you may customize #
#######################################################################
# Change this as needed for years other than 2010.
#
my $START_URL = “http://www.icd9data.com/2010/Volume1/default.htm”;
# An empty database name will cause SQL INSERT statements to be dumped
# to stdout, with no database access. To update your OpenEMR database
# directly, specify its name here.
#
my $DBNAME = “openemr”;
# You can hard-code the database user name and password (see below),
# or else put them into the environment with bash commands like these
# before running this script:
#
# export DBI_USER=username
# export DBI_PASS=password
#
#my $dbh = DBI->connect(“dbi:mysql:dbname=$DBNAME”) or die $DBI::errstr
# if ($DBNAME);
my $dbh = DBI->connect(“dbi:mysql:dbname=$DBNAME”, “openemr”, “openemr”)
or die $DBI::errstr if ($DBNAME);
# Comment this out if you want to keep old nonmatching codes.
#
#$dbh->do(“delete from codes where code_type = 2”) or die “oops”
# if ($DBNAME);
#######################################################################
# Startup #
#######################################################################
$| = 1; # Turn on autoflushing of stdout.
my $countup = 0;
my $countnew = 0;
#######################################################################
# Main Logic #
#######################################################################
# This function recursively scrapes all of the web pages.
#
sub scrape {
my $url = shift;
my $browser = WWW::Mechanize->new();
$browser->get($url);
my $parser = HTML::TokeParser->new($browser->content());
while(my $tag = $parser->get_tag(“li”, “div”)) {
# The <li><a> sequence is recognized as a link to another list
# that must be followed. We handle those recursively.
if ($tag-> eq “li”) {
$tag = $parser->get_tag;
$tag = $parser->get_tag if ($tag-> eq “strong”);
next unless ($tag-> eq “a”);
my $nexturl = $browser->base();
# $nexturl =~ s’/+$’/’;
$nexturl =~ s’/20.+$’’;
scrape($nexturl . $tag->{href});
}
# The <div><img> sequence starts an ICD9 code and description.
# If the “specific green” image is used then we know this code is
# valid as a specific diagnosis, and we will grab it.
else {
$tag = $parser->get_tag;
next unless ($tag-> eq “img”);
next unless ($tag->{src} =~ /SpecificGreen/);
$tag = $parser->get_tag(“a”);
my $tmp = $parser->get_trimmed_text;
unless ($tmp =~ /Diagnosis Code (\S+)/) {
print STDERR “Parse error in ‘$tmp’ at $url\n”;
next;
}
my $code = $1;
$tag = $parser->get_tag(“div”);
my $desc = $parser->get_trimmed_text;
$desc =~ s/’/’’/g; # some descriptions will have quotes
# This creates the needed SQL statement, and optionally writes the
# code and its description to the codes table.
my $query = "INSERT INTO codes " .
"( code_type, code, modifier, code_text ) VALUES " .
“( 2, ‘$code’, ‘’, ‘$desc’ )”;
if ($DBNAME) {
my $usth = $dbh->prepare("SELECT id FROM codes " .
“WHERE code_type = 2 AND code = ‘$code’”)
or die $dbh->errstr;
$usth->execute() or die $usth->errstr;
my @urow = $usth->fetchrow_array();
if (! @urow) {
++$countnew;
}
else {
$query = "UPDATE codes SET code_text = ‘$desc’ " .
“WHERE code_type = 2 AND code = ‘$code’”;
++$countup;
}
$dbh->do($query) or die $query;
}
print $query . “;\n”;
}
}
}
# This starts the ball rolling.
scrape($START_URL);
#######################################################################
# Shutdown #
#######################################################################
if ($DBNAME) {
print “\nInserted $countnew rows, updated $countup codes.\n”;
$dbh->disconnect;
}
After some careful thought, I set the username to “root” and the password to null “”,
The script ran for about a half hour and did not return any errors but the “service”
page in openemr only listed the first three ICD9 codes.
Any suggestions?
Regards,
David