ICD9 Code Import

1. Downloaded ICD9 codes in excel format from here

[http://www.aafp.org/online/en/home/publications/journals/fpm/icd9.html ]

2. Reformatted to have rows as 0 or 1, took out -- in empty records

3. Select 5 columns, all rows

4. Copy and past into text file (makes it tab delimited)

5. upload text file to /var/db/mysql/clinic/

6. execute this query in the MySQL query browser

   LOAD DATA INFILE 'icd9.txt'
   IGNORE INTO TABLE icd9 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY ""
   

7. clean up the data in the table using these querries

   update icd9 set description=substring(description,2,length(description)-3) where description like Concat('"%"',char(13));
   update icd9 set description=substring(description,2,length(description)-2) where description like '"%"';
   update icd9 set section=substring(section,2,length(section)-2) where section like '"%"';
   update icd9 set subSection=substring(subSection,2,length(subSection)-2) where subSection like '"%"';
   update icd9 set description=substring(description,1,length(description)-1) where description like concat('%',CHAR(13));
   update icd9 set subSection='' where subSection = char(150);
   

Importing ICD9 categories

insert into categories(name)
  select distinct section as name from icd9;

insert into categories(name,parentCategoryID)
  select distinct subSection as name, (select ID
from categories where categories.name = icd9.section) as parentCategoryID from icd9 where icd9.subSection not like '';

Importing ICD9 codes

some of the diagnosises don't have subsections, so it has to look for the ID of the section instead. Also codes that already exist should not be imported.

insert into messages(name, messageTypeID, categoryID, icd9, top)
select description as name, 1 as messageTypeID,
  (select ID from categories c1
  where (icd9.subSection like ''
        and c1.name like icd9.section
        and c1.parentCategoryID is null)
  or
    (icd9.subSection not like ''
    and c1.name like icd9.subSection
      and c1.parentCategoryID in
      (select ID from categories c2 where c2.name like icd9.section))
  ) as categoryID, code as icd9, top
from icd9
 where code not in (select icd9 from messages);