Importing HCPCS Codes?

vivian765 wrote on Thursday, October 17, 2013:

I have the 2013 HCPCS code set downloaded from the CMS site (in a .txt and a .xlsx file). I want to import these codes into OpenEmr. I’m not sure how to accomplish this. We’ve googled this topic, but, have not found much. I’m sure this has been done before; What am I missing?

Our System:
OpenEmr v4.1.2 with latest patch
Ubuntu Server: 12.04

Thanks for any help!

fsgl wrote on Thursday, October 17, 2013:

Presently the External Database Import Utility (Administration/Other/External Data Loads) is not set up for HCPCS.

However, they can be imported like CPT codes, either via a .plx or a .sql file. See this article for details.

Instead of ct_id of 1 for the CPT codes, use 3.

In Administration/Lists/Code Types, verify that the field for the HCPCS has been properly configured as shown in the attachment; otherwise they won’t shown up in Administration/Codes.

blankev wrote on Thursday, October 17, 2013:

Can you make a image to show from the first couple of rows to be imported? How man y rows in total?

fsgl wrote on Thursday, October 17, 2013:

Hi Pimm,

Section 1.1.5.2 of the above link has very specific screenshots. Take your pick. The number of rows is the total number of codes in the data set. Vivian may have to divide them up into bite size pieces to make import manageable.

blankev wrote on Thursday, October 17, 2013:

Sorry but sometimes I do not understand bits and bytes… What link please?

blankev wrote on Friday, October 18, 2013:

If you are quick, GOTO V.4.1.1 Demo OpenEMR.

Export the CODES table and Import the Codes table in your own version. Should be compatible with V4.1.1 and V4.1.2 but the Demo does not allow to Import Files via phpMyAdmin.

If you want to know how this was accomplished, I need to redo the whole exercise. There were some problems encountered that I solved the dirty way. Also the prices and the date created are not included.

But it shows it can be done with things I found on the Internet for free.

fsgl wrote on Friday, October 18, 2013:

Here is the link: Code Types. Probably more screenshots than what you want.

fsgl wrote on Friday, October 18, 2013:

Pimm,

That was so kind of you!

I deleted the CVX codes from the .sql files so that import would not be impaired.

Attached are both the zipped and unzipped files.

Freeware is the best.

Vivian,

Pimm did the heavy lifting for you.

Import, if successful, should take about 1 second.

Just to be on the safe side, backup and test on a second copy of OpenEMR before import into your phpMyAdmin.

blankev wrote on Friday, October 18, 2013:

I am flattered by the compliment. There were more fileds stored in the downloaded files with the HCPCS codes, but I had not enough time to find out where and what could also be included.

During the CSV import of the HCPCS codes in the long- and short- names it showed that a - " - - " - signs were included for a part of the name, I changed it into a *.

There might have been modifiers and fees included. May be FSGL you can have a closer look?

Now I know how to make the CSV file it is easy to make another with more info included from the download.

blankev wrote on Friday, October 18, 2013:

Another option is to ignore duplicates, so you dont have to delete what is already in during the CSV import. During import of the CSV files you have the option to exclude the first line, that can have or does not have the names of the fields, the header line. DO NOT IMPORT THE HEADER LINE!

But the option to exclude duplicates can give rise if to other problems during an update. But time comes etc.

Just a reminder: When Importing the file for USERS: Never delete or re-import the first line with the Admin and PASS, since that is the line where the SUPER ADMINISTRATOR is situated and once this is deleted there is real trouble!

fsgl wrote on Saturday, October 19, 2013:

Hi Pimm,

I’m traveling and I only have the little Chromebook at my disposal. I am unable to open the .sql file as a result.

I did try to re-import it into the 4.1.1 Demo, but it is not available this morning.

When I tried to do it in the 4.1.0 Demo:

Error
SQL query:

– -- Dumping data for table codes – INSERT INTO codes (id, code_text, code_text_short, code, code_type, modifier, units, fee, superbill, related_code, taxrates, cyp_factor, active, reportable, financial_reporting) VALUES (147, ‘Dressing for one wound’, ‘Dressing for one wound’, ‘A1’, 3, ‘’, 0, NULL, ‘’, ‘’, ‘’, 0, 1, 1, 1), (148, ‘Dressing for two wounds’, ‘Dressing for two wounds’, ‘A2’, 3, ‘’, 0, NULL, ‘’, ‘’, ‘’, 0, 1, 1, 1), (149, ‘Dressing for three wounds’, ‘Dressing for three wound’, ‘A3’, 3, ‘’, 0, NULL, ‘’, ‘’, ‘’, 0, 1, 1, 1), (150, ‘Dressing for four wounds’, ‘Dressing for four wounds’, ‘A4’, 3, ‘’, 0, NULL, ‘’, ‘’, ‘’, 0, 1, 1, 1), (151, ‘Dressing for five wounds’, ‘Dressing for five wounds’, ‘A5’, 3, ‘’, 0, NULL, ‘’, ‘’, ‘’, 0, 1, 1, 1), (152, ‘Dressing for six wounds’, ‘Dressing for six wounds’, ‘A6’, 3, ‘’, 0, NULL, ‘’, ‘’, ‘’, 0, 1, 1, 1), (153, ‘Dressing for seven wounds’, ‘Dressing for seven wound’, ‘A7’, 3, ‘’, 0, NULL, ‘’, ‘’, […]

MySQL said: Documentation

#1054 - Unknown column ‘financial_reporting’ in ‘field list’

There does not appear to be a column for financial reporting in the 4.1.0 Demo, but it’s there in the 4.1.2 Demo. Vivian may not have any problems.

blankev wrote on Saturday, October 19, 2013:

As I tried to explain. Demo import in V4.1.2 is impossible as long as I know this new phpMyAdmin software.

Importing in V4.1.1 was easy once the fields, Columns and Rows were corrected in the HCPCS download.

Some problems with " in the fields so these have to be changed in the original import file to OpenEMR Codes Table.

fsgl wrote on Saturday, October 19, 2013:

Hi Pimm,

I knew empirically from working on the .sql files for Henry Alvarez’s Eye forms that the 4.1.2 and the 4.1.3 Demo’s won’t import. Kevin did explain in your Browser thread about the version of Php being 5.2 and not 5.4 causing the importation problem.

Because I’m not home and cannot open up the HCPCS .sql file, as you had asked me to do; the next best thing was to try importing into the 4.1.0 Demo, with 4.1.1 Demo being unavailable earlier.

OK, the 4.1.1 Demo is available now, but:

Error
SQL query:

– -- Dumping data for table codes – INSERT INTO codes (id, code_text, code_text_short, code, code_type, modifier, units, fee, superbill, related_code, taxrates, cyp_factor, active, reportable, financial_reporting) VALUES (147, ‘Dressing for one wound’, ‘Dressing for one wound’, ‘A1’, 3, ‘’, 0, NULL, ‘’, ‘’, ‘’, 0, 1, 1, 1), (148, ‘Dressing for two wounds’, ‘Dressing for two wounds’, ‘A2’, 3, ‘’, 0, NULL, ‘’, ‘’, ‘’, 0, 1, 1, 1), (149, ‘Dressing for three wounds’, ‘Dressing for three wound’, ‘A3’, 3, ‘’, 0, NULL, ‘’, ‘’, ‘’, 0, 1, 1, 1), (150, ‘Dressing for four wounds’, ‘Dressing for four wounds’, ‘A4’, 3, ‘’, 0, NULL, ‘’, ‘’, ‘’, 0, 1, 1, 1), (151, ‘Dressing for five wounds’, ‘Dressing for five wounds’, ‘A5’, 3, ‘’, 0, NULL, ‘’, ‘’, ‘’, 0, 1, 1, 1), (152, ‘Dressing for six wounds’, ‘Dressing for six wounds’, ‘A6’, 3, ‘’, 0, NULL, ‘’, ‘’, ‘’, 0, 1, 1, 1), (153, ‘Dressing for seven wounds’, ‘Dressing for seven wound’, ‘A7’, 3, ‘’, 0, NULL, ‘’, ‘’, […]

MySQL said: Documentation

#1062 - Duplicate entry ‘147’ for key 1

Well, there no point in all this frenetic activity until the original poster has additional comments. Sometimes they just read the answers and don’t bother to post again.

blankev wrote on Saturday, October 19, 2013:

Duplicate key means there is already a key147… in the table, hahaha sounds stupid but that is the reason. (Open the table in browse and find the last entry for key1. You have to start your new import with a new ID-Key number or delete the duplicate row. OR Empty the table if there are no duplicate keys in the Spreadsheet. Import the sql file.

It all depends on the exported file of codes table and what you did delete in the OpenEMR database table.

I also saw some duplicate codes due to truncating of the Long names
line with: (hcpsa) around line 157
line with: 98941, 98942) about line 170

Hope this clears the problem.

vivian765 wrote on Sunday, October 20, 2013:

Hi fsgl,
Hi Pieter W,
Thanks to both of you for your responses and helping me with my issue, I really appreciate your time.

Sorry for my silence on the issue, I am learning to generate forms from XML files; specifically a UB04 form.

Pieter W, you saved me sooo much work by providing those .sql and zipped .sql files. I imported the .sql file into my test environment and everything worked like a champ.

When I imported the .sql file into the production environment, I was surprised to see the same duplicate errors fsgl experienced. Not sure why I saw duplication errors. Anyway, I have a work-a-round for now until I can return to this error. I simply took those Primary Key values in the INSERT statements and changed them from 147 and 148 to 10047 and 10048, saved the file with a different name to preserve the original and re-imported it. No duplicate errors; the production environment is happy for now.

Here is the updated file:

blankev wrote on Sunday, October 20, 2013:

In my spreadsheet there were some errors. Parts of the long name have been placed on the next row with the same Code.

So you have to redo the exercise or I can upload my spreadsheet and you can correct the Double codes.

PLS, let me know. Next week I might have some extra time to make corrections and than upload the corrected file.

vivian765 wrote on Monday, October 21, 2013:

Hi Pieter W,

Yes! I appreciate any help you can give.

We have a little time before our site goes live, so, if you had time the week of October 28th, it would help me and not rush you.

fsgl wrote on Monday, October 21, 2013:

Hi Pimm,

I imported 5 Eye CPT codes with insert statements for code text, code and code type only. PhpMyAdmin automatically assigned the id’s and used defaults for the other columns.

The attempt to import the unzipped file was successful today. Yesterday I used the Chrome browser which gave the second error, which made no sense because 147 is not a duplicate in the .sql file. The import function tends to be erratic with the 4.1.1 Demo.

In general with such codes, only a short description, code type and code are all that is required. Everything else is superfluous. Because fees vary from region to region, the user has to insert this column himself.

If I could make a suggestion, it would be helpful if you wrote an addition in Brady’s Code Type article how you converted the HCPCs data set to the CSV file. The instructions for the .plx file are rather unwieldy. That is why I wrote the .sql section.

blankev wrote on Monday, October 21, 2013:

I tried to explain, but it is rather difficult, since I did import with ommission of double lines, at least that was my intention.

In my orriginal spreadsheet I discovvered some double codes for example (not the real issue) in the table there is code A1 and in my own spreadsheewt there is another code A1 with only a numer for long description. Obvious you can’t have two of the same codes one with an almost correct long name and anohther with a wrong long name wtih only numbers that does not give a clue for the code.

I will come back with the correct code. Give/take a week.

mishik27 wrote on Sunday, December 01, 2013:

I am curious which parts of OpenEMR you had to customize since UB-04 needs different data than CMS-1500 (like service start time etc.) ?