Importing Patient Data

[igork]

Hello,

I recall OD used to have an option to import text file with paient data. Does not appear to be there anymore...
Can you please let me know what it was replaced with, if any?

We using the program to server SNFs and have to work with rather large numbers of patients...

Is there an import functionality we can use?

Many thanks in advance.

Igor

[jordansparks]

Do you remember what version you were using that had that feature? I haven't seen it for a long time. I think the reason we got rid of it was that it's purpose was to allow a simple conversion from another software program to Open Dental. But after a while we realized that an expert should do that regardless of what tool was used. I don't think we ever considered that a user might need to use it on an ongoing basis. Just curious why you didn't notice that it was missing sooner. Was it because you were on an older version for a long time?

[igork]

No, this was version probably of October 2008, and we are usually up to the latest stable version.
We have SNFs giving us mostly paper/fax, therefore we have to re-enter anyway... so we 'dropped' the requirement.
Now, we have one that is willing to share the data. They are flexible, and offer either XML(HL7) or CSV etc. This particular facility is using MDI Achieve and HealthMEDX for facility patient management.

Hopefully, more will go that way, and yes, we need it on ongoing basis.

The cut-n-paste of 'Import XML' appears to only allow for one record at a time - not going to help us much .

We can do software development, but would prefer to avoid 'reinventing the wheel' of course. The most important probably is the patient information(patient table?) and what insurance the patient has, history is less important and we verify it all anyway.

What would you recommend Jordan?

Thank you in advance,
Igor

[igork]

Dear Dr. Jordan, I was wondering if you have any suggestions for us at this point...

TIA,
Igor

[jordansparks]

I'm planning on trying to put that tool back in.

[igork]

Thank you Jordan, appreciate it.

Meanwhile, we will try to import the data directly into patient table (et al) via either SQLYog tools or a small C# standalone program... please let me know if you think we are 'wasting' the time.

Yours,
Igor

[jordansparks]

That might be ok. Just be sure to set BillingType, Guarantor, and PriProv. Guarantor will be hardest because you won't know what the PatNum is until you save it, so it will probably require a second query like this:
UPDATE patient SET Guarantor=PatNum WHERE Guarantor=0;
But please be very careful with these queries. Make a good backup before you start.

[igork]

Dear Dr. Jordan, thank you for the suggestions.

I would truly appreciate your feedback to what we did to make sure we did everything correctly and did not inadvertently stumbled upon a non documented feature or an intricacy that we are not aware of.

Many thanks in advance.

We have somewhat special situation, as vast majority of our patients have Denti-Cal (California State Medicaid, dental coverage). Therefore we took out non Denti-Cal patients and entered them manually .
We wrote a small Java program. It parses the data file, and then inserts records one at a time (cursor like) and logs everything in a file.

It inserts into three tables: PATIENT, INSPLAN, and BENEFIT.

Here are the steps:
1. check by SSN and MedicaidID if patient is in, if patient found, the program skips this record
2. insert patient data into PATIENT table with BillingType=40, appropriate PriProv, SiteNum and ClinicNum
3. update Guarantor to the Patient, and create ImageFolder value
4. insert into INSPLAN table Subscriber (PatNum from PATIENT) SubscriberID (MedicaidID), Carrier (the id of Denti-Cal), ClaimFormNum (for Denti-Cal) etc.
5. insert into BENEFIT nine records with PlanNum linking to PlanNum from INSPLAN table for the patient. We modeled the nine records to insert to be the same as other patients with Denti-Cal.

Here is the code, I XXXXed the actual patient values.

Code: Select all

2 INSERT INTO PATIENT ( LName,FName,Birthdate,Gender,HasIns,SSN,MedicaidID,Bal_31_60,InsEst,PatStatus,Race,Zip,GradeLevel,City,Guarantor,PreferRecallMethod,Position,Bal_61_90,PreferContactMethod,DateFirstVisit,SchedAfterTime,SecProv,HmPhone,PriProv,EmployerNum,SchedDayOfWeek,BalTotal,ClinicNum,BalOver90,Bal_0_30,FeeSched,SiteNum,State,Address,EstBalance,ResponsParty,BillingType,PayPlanDue,Address2,SchedBeforeTime,Premed,PreferConfirmMethod,AdmitDate,Urgency) VALUES ('LLLLL','FFFF','1900-01-01','1','I','SSSSS','MMMMMM','0','0','0','0','94001','0','San Francisco','0','0','0','0','0','0001-01-01','0:00:00','0','415-333-1111','9','0','0','0','39','0','0','0','101','CA','101 First Avenue','0','0','40','0','','0:00:00','0','0','001-01-01','0') 
3 UPDATE PATIENT  SET Guarantor='11532',ImageFolder='LLLLFFFF11532' WHERE PatNum='11532'
4 INSERT INTO INSPLAN ( CodeSubstNone,FilingCode,ShowBaseUnits,DateEffective,EmployerNum,IsHidden,DedBeforePerc,MonthRenew,AllowedFeeSched,UseAltCode,ClaimsUseUCR,FilingCodeSubtype,FeeSched,AssignBen,isMedical,DentaideCardSequence,DateTerm,CopayFeeSched,ClaimFormNum,CarrierNum,ReleaseInfo,Subscriber,SubscriberID) VALUES ('0','1','0 ','0001-01-01','0','0','0','0 ','0','0','0','0','0','1','0','0 ','0001-01-01','0','14','7','1','11532','MMMMMM')
5 INSERT INTO patplan ( isPending,Ordinal,Relationship,PatNum,PlanNum) VALUES ('0 ','1','0','11532','7609')
INSERT INTO benefit ( PatPlanNum,CovCatNum,BenefitType,Percent,MonetaryAmt,TimePeriod,QuantityQualifier,Quantity,CodeNum,CoverageLevel,PlanNum) VALUES ('0','21','1','100','-1','2','0','0','0','0','7609')
INSERT INTO benefit ( PatPlanNum,CovCatNum,BenefitType,Percent,MonetaryAmt,TimePeriod,QuantityQualifier,Quantity,CodeNum,CoverageLevel,PlanNum) VALUES ('0','17','1','50','-1','2','0','0','0','0','7609')
INSERT INTO benefit ( PatPlanNum,CovCatNum,BenefitType,Percent,MonetaryAmt,TimePeriod,QuantityQualifier,Quantity,CodeNum,CoverageLevel,PlanNum) VALUES ('0','16','1','50','-1','2','0','0','0','0','7609')
INSERT INTO benefit ( PatPlanNum,CovCatNum,BenefitType,Percent,MonetaryAmt,TimePeriod,QuantityQualifier,Quantity,CodeNum,CoverageLevel,PlanNum) VALUES ('0','15','1','80','-1','2','0','0','0','0','7609')
INSERT INTO benefit ( PatPlanNum,CovCatNum,BenefitType,Percent,MonetaryAmt,TimePeriod,QuantityQualifier,Quantity,CodeNum,CoverageLevel,PlanNum) VALUES ('0','14','1','80','-1','2','0','0','0','0','7609')
INSERT INTO benefit ( PatPlanNum,CovCatNum,BenefitType,Percent,MonetaryAmt,TimePeriod,QuantityQualifier,Quantity,CodeNum,CoverageLevel,PlanNum) VALUES ('0','13','1','80','-1','2','0','0','0','0','7609')
INSERT INTO benefit ( PatPlanNum,CovCatNum,BenefitType,Percent,MonetaryAmt,TimePeriod,QuantityQualifier,Quantity,CodeNum,CoverageLevel,PlanNum) VALUES ('0','12','1','80','-1','2','0','0','0','0','7609')
INSERT INTO benefit ( PatPlanNum,CovCatNum,BenefitType,Percent,MonetaryAmt,TimePeriod,QuantityQualifier,Quantity,CodeNum,CoverageLevel,PlanNum) VALUES ('0','11','1','100','-1','2','0','0','0','0','7609')
INSERT INTO benefit ( PatPlanNum,CovCatNum,BenefitType,Percent,MonetaryAmt,TimePeriod,QuantityQualifier,Quantity,CodeNum,CoverageLevel,PlanNum) VALUES ('0','10','1','100','-1','2','0','0','0','0','7609')

[jordansparks]

Yes,that should work.

[igork]

Thank you Dr. Jordan!