Importing patient history from PracticeWorks?

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
ithcy
Posts: 9
Joined: Wed Jun 27, 2007 12:27 pm

Importing patient history from PracticeWorks?

Post by ithcy » Wed Jun 27, 2007 12:38 pm

Hi,

I'd like to write a little script to grab the patient history data out of Practiceworks 6 via ODBC and import it into Open Dental (I'm running the OD 5.0 beta).

I am a programmer, but I've never messed with Pervasive, so I could use some pointers.

Has anyone here delved into the Pworks database structure? Have any notes or documentation that might get me going in the right direction?

Thanks!

ithcy
Posts: 9
Joined: Wed Jun 27, 2007 12:27 pm

Post by ithcy » Mon Jul 23, 2007 8:35 am

okay, well, i guess the replies here got lost. oh well. i'm still working on this problem and i'll post something soon.

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Post by jordansparks » Mon Jul 23, 2007 9:13 pm

Yes, terribly sorry about the loss. I don't remember anything very important on this thread. There was a link to the MySQL website for conversions from Pervasive, but we can find that again if we need to.
Jordan Sparks, DMD
http://www.opendental.com

ithcy
Posts: 9
Joined: Wed Jun 27, 2007 12:27 pm

Post by ithcy » Mon Jul 23, 2007 9:49 pm

no, there wasn't anything super important.

by the way, this is Bill, from Dr. Youngman's office. i spoke to your brother on the phone earlier today. thanks, both of you, for your help with the database conversion to 5.0.9.

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Post by jordansparks » Mon Jul 23, 2007 10:09 pm

Great. Glad it worked for you.
Jordan Sparks, DMD
http://www.opendental.com

ithcy
Posts: 9
Joined: Wed Jun 27, 2007 12:27 pm

Post by ithcy » Wed Jul 25, 2007 12:05 pm

Here's how to get access to your PracticeWorks data through ODBC. This is just a copy of a private message I sent to a user on this forum. When I get the foreign key structure figured out, I'll post it.


There are two main steps to this: Create the ODBC data source, and create a junction point (symlink.)

---------------------------------------------
Okay, I'll see if I can explain how I got it to work. This may not be the best way to do it, and if I find a better way I'll let you know, but here goes:

The PWorks database files are usually stored in C:\PWORKS\DATA. They are .dat files. For example, the patient info is stored in patient.dat.
  1. Go into Administrative Tools->Data Sources (ODBC).
  2. Pick the System DSN tab.
  3. Click Add.
  4. Scroll down and pick the "Pervasive ODBC Engine [not Client!] Interface" driver and click Finish.
  5. Enter "pworks" or whatever for the Data Source Name.
  6. Under Database, click Create.
  7. Enter "pworksd" or whatever for the Database Name.
  8. Under Dictionary Locations, click Browse and browse to the location of the PWorks database files (above).
  9. Click Test. You should see an alert box that says "Connection successful!" You can dismiss it.
  10. Click OK and click OK again to close the ODBC Data Source Administrator.
At this point you would think that you have full ODBC access to PWorks. However, this is not quite the case.

If you run the Pervasive Control Center (C:\PVSW\Bin\Pcc.exe on my install) you will see your new ODBC data source, and the tables from your PWorks database. Right click on one, and pick Properties. You will see a Table Location which, on my install anyway, points to a folder that doesn't exist (C:\PWDATA\Conv60_2\600Base). So Pervasive ODBC Engine is looking for the tables in this folder instead of the folder where they're actually installed (C:\PWORKS\DATA). Of course this leads to problems accessing the data.

There are a couple of ways to fix this. Your fix depends on whether your data is stored on an NTFS partition, or whether you're using Practiceworks actively anymore.

The most obvious (and simplest) solution is to move or copy the .dat files from \PWORKS\DATA to the location specified in the table properties (that Conv60_2\600Base folder above.) However, this is only a solution if you're not using Practiceworks anymore and the data files are no longer being updated.

If you are still using Practiceworks, and your data is on an NTFS partition, it is possible to create a "junction point" to C:\PWORKS\DATA in another place, so that you will be able to access the files from both locations even though they are actually only stored in one place. (junction points are analogous to symlinks on unix systems.) This means that you can access the data through ODBC and not have to worry about your data being unsychronized or unavailable in PracticeWorks. junction points are transparent to Windows; you can read and write to the same files from both locations simultaneously.

You need a tool to create the NTFS junction points. For some reason the capability exists in Win2000/XP but the only Microsoft tool to manipulate junction points is found on the Resource Kit CD, which costs money. I don't know why MS seems to want to hide this feature from users. I used the free Junction Link Magic. There is also NTFS Link.

To create the junction point:
  1. Create the host folder (this will be C:\PWDATA\Conv60_2\600Base or whatever is specified as the Table Location via the steps above.)
  2. Run Junction Link Magic
  3. Click Create
  4. For the Junction Point (left side) pick the folder you just made.
  5. For the Destination (right side) pick the actual location of the .dat files.
  6. Click Create again.
(remember, you have 2 points of access to your data folder now, and if you delete the new folder you made for the junction point, you will also delete the practiceworks data! so be careful. you need to remove the junction point BEFORE you delete the new folder.)

That's it. You should be able to access your PWorks data through ODBC now. There is no password.

Post Reply