File Conversion For CPT Import

  • fsgl

    fsgl - 2013-09-08

    It seems that there are almost no open source .txt to .sql file converters. The purchased CPT data set must be converted prior to importing into Administration/Other/Database.

    It is a bit cumbersome to use OpenOffice, but the frugal user may not mind.

    Can the user just use HyperSQL which is built-in?

    Or must the user open the file in OpenOffice Calc, copy and paste in OpenOffice Base, convert either with an additional driver like PostgreSQL as explained here or with a JDBC driver?

    The JDBC driver appears to be easier to use. If not, please explain why not.

    It would be great to have all this delineated before adding information to the Wiki.

    Last edit: fsgl 2013-09-08
  • Brady Miller

    Brady Miller - 2013-09-09

    Prob best to go a csv type file import route which can also be used to import if trying to get a quick import approach documented. Suggest discussing with Pimm since he seems to be an expert at this type of import. What does the text file look like?

  • fsgl

    fsgl - 2013-09-09

    Hi Brady,

    The big problem is that I have zero experience with this because I have no need of the AMA data set, which I have never lay eyes on, in my own practice. CPT import is a recurring and unresolved issue that I decided to tackle. I thought that if I could figure out how to convert the data set in a readily importable format and to do so with freeware, I could add the information to your Wiki article Code Types.

    My goal is to provide an alternative to .plx, which you noted as a "rather complicated script/ method".

    The intended audience is the DIY practice, which does want the entire data set imported but which doesn't want to blow any more money after shelling out $95/$75.

    Because the corresponding Fee Schedule varies from region to region, I think it best to leave that up to the individual practice to enter manually.

    The other developers convert to .sql prior to import, which makes me think that this is a less time consuming route.

    Last edit: fsgl 2013-09-09
  • Kevin Yeh

    Kevin Yeh - 2013-09-09

    The basic issue is that ".txt" does not sufficiently define a format. It just means "plain text." It could be comma separated, tab delimited, free form, fixed width, or any number of other formats. So without more details, it's hard to help.

    OpenOffice Calc/MS Excel/Spreadsheet of your choice is generally a good intermediate step for importing data. As an example, here is a spreadsheet to populate a table with BMI for age data. It illustrates building up appropriate SQL INSERT statements from columns of data in the spreadsheet.

    After generating the spreadsheet and creating a new table for the data, I copied and pasted the contents from P2-P439 into MySQL Workbench (my tool of choice for running SQL queries directly, although PHPMyadmin would work too.)

    This same basic strategy could be applied to CPT Codes however instead of mapping all the columns to a new table the spreadsheet columns would be mapped to the appropriate columns in codes table.

  • Kevin Yeh

    Kevin Yeh - 2013-09-09

    From what I can tell from an examination of the perl script, the format (at least when the script was written) is the first 5 characters on each line are the code, then there is a delimiter character (could be a space, comma,tab). The perl script doesn't care, it just skips it. Then the remainder of each line is the description.

    So one reasonable approach would be to load the AMA's text file into a spreadsheet, and split the contents into two columns. (call them A and B).
    Then in column C do something like =CONCATENATE("INSERT INTO codes ( code_text, code, code_type) VALUES ('",B1,"','",A1"',1)"
    (syntax might not be perfect)
    Then copy that formula down for each line.

    Column C will then contain a listing of SQL statements one could execute via method of choice (mysql on the command line, phpmyadmin, mysqlworkbench).

    The perl script checks for duplicate/preexisting rows which this method won't.

    Last edit: Kevin Yeh 2013-09-09
  • fsgl

    fsgl - 2013-09-09

    Hi Kevin,

    Did not realize that the .txt extension was non-specific. File formats for spreadsheets did not make the distinction.

    I can ask the AMA whether it is ASC II, Tab Delimited or Document Extension, but they may not respond if I am only "window shopping".

    The nascent Wiki addition is for non-techies, so it should not be too complex. Additionally the documentation should have screenshots; which in my case, not possessing the AMA data set, is difficult to pull off.

    Assuming that OpenOffice Calc has no difficulty opening the file and that it can be copied and pasted, how should the user proceed with the conversion employing OpenOffice Base and one of the drivers? Kindly indulge an old person in this approach.

  • Kevin Yeh

    Kevin Yeh - 2013-09-09

    Dr. Lee,
    If one is doing a "one time import" a driver is not really needed. The purpose of such a driver is to "live updating" of the database from the spreadsheet. In other words, if you change a cell in the spreadsheet, it can directly modify the row in the database table through the use of JDBC or the PostgresSQL driver.

    The goal in generating a .sql file for import is to basically have one line for each code that looks like.

    INSERT INTO codes (code_text,code,code_type) VALUES ('Intermediate Eye Exam','92002',1);
    INSERT INTO codes (code_text,code,code_type) VALUES ('Comprehensive Eye Exam','92004',1);
    INSERT INTO codes (code_text,code,code_type) VALUES ('Phaco','66850',1);

    The 1 for "code_type" corresponds to CPT4 in OpenEMR's definitions in the code_types table.

    Once, such a file is created, all of the statements can be executed using a number of different methods that are already part of a running OpenEMR installation.

    The "simplest" mechanism IMHO (although it requires using the command line) would be to use the "mysql" command.

    Suppose the created file is called "cpt.sql" Then executing

    mysql -u<mysqlusername> -p<mysqlpassword> openemr < cpt.sql
    Will load all of the datarows specified in the file.
    Where mysqlusername and mysqlpassword are the same as the info defined in sqlconf.php.

    One could also copy and paste the insert statements into "Run SQL queries" in phpmyadmin, or other query tool.

    When someone adds a new code using administration/services, whenever they hit "Add as New Code" OpenEMR is bascially executing an "INSERT into codes..." statement

  • fsgl

    fsgl - 2013-09-10


    I am in your debt and have run out of Latin laudatories (I realize it's not a noun in English, but the word "laudatoria" connotes folks who eulogizing the dearly departed and that would hardly be auspicious.)

    Being a mere mortal, it will take me a bit of time to understand and work through your advice.


    Please correct any error when my addition appears in your Wiki article.

  • Kevin Yeh

    Kevin Yeh - 2013-09-10

    Dr. Lee,
    The best praise is for you to continue contributing to the project even in the face of challenging concepts.

    On the advice of our esteemed colleague Albert.
    If you can't explain it simply you don't understand it well enough.

    Part of the problem is we don't understand the format of the data from the AMA. (Although we can make some guesses).

    The first goal for adding CPT codes is the need to create INSERT statements for each of the codes.

    The most basic and reliable way to add ANY data to OpenEMR/MySQL is with a basic INSERT SQL statement. Which looks like

    INSERT into TABLENAME (column1,column2,column3) VALUES (column1value,column2value,column3value);

    Such a SQL statement can be executed through one of several mechanisms (as part of the install/upgrade scripts, by a .PHP page in OpenEMR after hitting "save", or one of the previously mentioned SQL tools.

    I think I've outlined a reasonable strategy for this based, but until we actually have the file it's all just speculation

    Last edit: Kevin Yeh 2013-09-10
  • fsgl

    fsgl - 2013-09-12

    My old CPT coding book indicated that the data files had been in ASCII format, therefore I would hazard a guess that the format has remained the same. I would also guess that the data set will have the codes, the descriptions, perhaps the modifiers in a separate location on the file and nothing else.

    Kim Weesner had kindly supply us with a .sql file for a small collection of CPT II codes which will serve well for illustration purposes.

    INSERT INTO openemr.code_types
    VALUES ('CPT II',4,4,12,'ICD9','',1,0,0,0);

    (code_text, code_text_short, code, code_type, modifier,
    units, fee, superbill, related_code, taxrates, cyp_factor, active, reportable)
    ('Fall risk screening','','1101F',4,'',NULL,NULL,'','','',0,1,0),
    ('Most recent diastolic blood pressure >=90mm Hg','','3080F',4,'',NULL,NULL,'','','',0,1,0),
    ('Most recent diastolic blood pressure <80mm Hg','','3078F',4,'',NULL,NULL,'','','',0,1,0),
    ('Most recent systolic blood pressure >=140mm Hg','','3077F',4,'',NULL,NULL,'','','',0,1,0),
    ('Screening for depression performed','','3725F',4,'',NULL,NULL,'','','',0,1,0),
    ('Most recent systolic blood pressure <130mm Hg','','3074F',4,'',NULL,NULL,'','','',0,1,0),
    ('Tobacco use cessation intervention, pharm/therpy','','4001F',4,'',NULL,NULL,'','','',0,1,0),
    ('Positive microbuminuria test doc/rev w/labs','','3062F',4,'',NULL,NULL,'','','',0,1,0),
    ('Positive microbuminuria test documented/reviewed','','3060F',4,'',NULL,NULL,'','','',0,1,0),
    ('Most recent LDL-C 100-129 mg/dl','','3049F',4,'',NULL,NULL,'','','',0,1,0),
    ('BMI documented','','3008F',4,'',NULL,NULL,'','','',0,1,0),
    ('Dilated retinal eye exam interpreted by optomologist','','2022F',4,'',NULL,NULL,'','','',0,1,0),
    ('Review of all meds by prescriber','','1160F',4,'',NULL,NULL,'','','',0,1,0),
    ('Most recent diastolic blood pressure 80-89mm Hg','','3079F',4,'',NULL,NULL,'','','',0,1,0),
    ('Most recent systolic blood pressure 130-139mm Hg','','3075F',4,'',NULL,NULL,'','','',0,1,0),
    ('Tobacco screening, recv/cessation/counseling','','4004F',4,'',NULL,NULL,'','','',0,1,0),
    ('Tobacco use cessation intervention, counseling','','4000F',4,'',NULL,NULL,'','','',0,1,0),
    ('Negative microbuminuria test documented/reviewed','','3061F',4,'',NULL,NULL,'','','',0,1,0),
    ('Most recent LDL-C >130 mg/dl','','3050F',4,'',NULL,NULL,'','','',0,1,0),
    ('Most recent LDL-C <100 mg/dl','','3048F',4,'',NULL,NULL,'','','',0,1,0),
    ('Most recent HbA1c level greater than 9.0%','','3046F',4,'',NULL,NULL,'','','',0,1,0),
    ('Functional status assessed','','1170F',4,'',NULL,NULL,'','','',0,1,0),
    ('Most recent HbA1c level less than 7.0%','','3044F',4,'',NULL,NULL,'','','',0,1,0),
    ('Most recent HbA1c level between 7.0-9.0%','','3045F',4,'',NULL,NULL,'','','',0,1,0),
    ('Advanced care planning','','1158F',4,'',NULL,NULL,'','','',0,1,0),
    ('Medication list documented in records','','1159F',4,'',NULL,NULL,'','','',0,1,0),
    ('Pain severity quantified, pain present','','1125F',4,'',NULL,NULL,'','','',0,1,0),
    ('Advanced care plan or legal document','','1157F',4,'',NULL,NULL,'','','',0,1,0),
    ('Pain severity quantified, no pain present','','1126F',4,'',NULL,NULL,'','','',0,1,0),
    ('Discharge medication list reconciled w/current list','','1111F',4,'',NULL,NULL,'','','',0,1,0);

    I took the file and imported it into the 4.1.2 Demo using the Import Configuration in Backup. These codes and associated columns were faithfully populated in Administration/Codes and in the codes tables as well. I was unable to import into Database using the Import tab (Error in processing request. Error Code: 500. Error Text: Internal Server Error.)

    The AMA data set should be a one time import for most practices.

    After the AMA data set had been unzipped, where should the user place this file?

    The code types insertion should not be necessary because they had been preloaded. The columns for taxrates and cyp_factor are also unnecessary. The code_type should be should be 1. Is that correct?

    With these modifications to Kim's file, what script should be run in Command Prompt to render the .sql file? I assume that the use of Command Prompt obviates the need to use OpenOffice.

    While using Command Prompt, will mysqlusername and mysqlpassword be asked?

    It should not matter very much the location of the new .sql if Import Configuration is used. Is this method of Import ill-advised?

    Last edit: fsgl 2013-09-12
  • fsgl

    fsgl - 2013-09-14

    I was using the incorrect tab to import in phpMyAdmin, which generated Code Error 500.

    Got it right after using Import function as depicted in attachment.

    It is so mind boggling that Import "knew" where to place the file! (Pimm would tell me it is logical and not magical.)

    Last edit: fsgl 2013-09-14
  • fsgl

    fsgl - 2013-09-17

    Thank you, Kevin.

    I think that finally I got it.

    Will load some Eye codes into OpenOffice Calc, add the concatenation stuff and do a dry run in the Demo's.

    Will ask users to test the protocol in the real world and modify the Wiki addition accordingly.

    Old dogs can be taught new tricks, but it's presto with pups.

    Last edit: fsgl 2013-09-19

Log in to post a comment.