Importing patient history from PracticeWorks?

[ithcy]

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]

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

[jordansparks]

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.

[ithcy]

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.

[jordansparks]

Great. Glad it worked for you.

[ithcy]

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.