Import Insurance Companies/ CMS Ids / address

Day-to-Day Users
1

gsporter wrote on Sunday, January 08, 2012:

I am setting up new OpenEMR instance and would like to avoid manually entering all the various insurance companies so I started looking around for a data source and method of importing them.

We are not bound to our current clearinghouse so I started looking around and found OfficeAlly which looked promising.   Low and behold they have a  downloadable payer list. The only issue is it does not contain address or contact
information.  Plus once I started looking into the database schema it became apparent the data you enter is actually in two tables “insurance_companies” and “addresses” linked by using the “id” from the “insurance_companies”  in the
“foreign_key” field of the address field.

I wondered if anyone had attempted this or had any ideas on the best way to proceed.   It would be great if there was an mysql dump similar to the icd9-codes-insert.sql.   I know not all clearinghouses work with all payers but it would be better to have extra companies than manually re-entering them and the CMS code should be the same.

In the mean time if I dump the CMS Id, and name into a cvs file would I cause any major problems with the data base?  The office staff could enter contact and  address information as the companies are used.

GP

  • Table structure for table `insurance_companies`

CREATE TABLE IF NOT EXISTS `insurance_companies` (
  `id` int(11) NOT NULL DEFAULT ‘0’,
  `name` varchar(255) DEFAULT NULL,
  `attn` varchar(255) DEFAULT NULL,
  `cms_id` varchar(15) DEFAULT NULL,
  `freeb_type` tinyint(2) DEFAULT NULL,
  `x12_receiver_id` varchar(25) DEFAULT NULL,
  `x12_default_partner_id` int(11) DEFAULT NULL,
  `alt_cms_id` varchar(15) NOT NULL DEFAULT ‘’,
  PRIMARY KEY (`id`)

  • Table structure for table `addresses`

CREATE TABLE IF NOT EXISTS `addresses` (
  `id` int(11) NOT NULL DEFAULT ‘0’,
  `line1` varchar(255) DEFAULT NULL,
  `line2` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `state` varchar(35) DEFAULT NULL,
  `zip` varchar(10) DEFAULT NULL,
  `plus_four` varchar(4) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `foreign_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `foreign_id` (`foreign_id`)

2

keithlofstrom wrote on Friday, January 27, 2012:

I’m interested in what you learn.  I’m setting up openemr for my wife’s clinic, lots to learn and do.

3

gutiersa wrote on Monday, January 30, 2012:

you can do both tables but you need to manipulate the data to then be able to import it  into the openemr database.
I use spreadsheets. You would Open the CVs file with whatever spreadsheet application you use.in windows right click-“open with” then find your spreadsheet program.
when importing the data you need to specify zip codes and cms id’s, maybe even phone numbers as text, otherwise all the leading zeroes in zip codes and cms id’s will be removed.

once the data is organized in the correct order you save it as csv file and get it ready for import into openemr. of course u would have two separate files for the two tables
kinda time consuming,
I use openoffice.org calc application
if you want more specific instructions post back.
this is how I do it,
sandra

4

blankev wrote on Monday, January 30, 2012:

The surest way to follow the spreadsheet advise is:

Make two very complete entries for the table you want to work with and save these to your local machine as a CSV file. Upload the same CSV file again into the DB and look at the results.

With this you will understand the principles of up and downloading in OEMR.

Why this little extra advise:

By downloading from the DB you are sure, you do not miss any field in the file you want to import in OEMR. Due to general changes it might happen that there are one or more extra columns/fields (differences)  in the latest update. 

Save a lot of time following the step wise approach. Sometimes you also need to clean the table in the OEMR DB to not get double entries.

Be aware to not use this approach with the USER table, because in deleting all entries for USERS you are excluded from entering the DB itself. and have to start from scratch.( Re-install OEMR, or ask a professional programmer, but due to security reasons it is obvious that without a registered USER with a Password you CAN NOT MAKE any changes)

There is also a limit on the amount of information you can upload with the CVS-upload approach.

Gr, Pimm

5

gutiersa wrote on Monday, January 30, 2012:

back up data fist

6

gutiersa wrote on Monday, January 30, 2012:

First I mean. the openemr data I mean