I'm hoping someone might be able to give me some direction here. I have been tasked with taking some client data which was stored in Pervasive SQL 2000 and migrating that over into our own system which is currently in SQL Server 2000 (2005 can be used as well). I have their data which is nothing more than a series of 112 .DAT files. I do not have Pervasive SQL 2000 installed, but I do have MS SQL Server 2000 and 2005 installed. My first goal is to simply get the raw data ported over into SQL Server so that I can at least view the data and try to make some sense of how I can begin mapping it over into our current structure. I had never heard of Pervasive SQL until this project, so I'm at a loss of where to start. Any help would be appreciated.

Thanks,
Greg

Short answer is to get DDFs (FILE.DDF, FIELD.DDF, and INDEX.DDF) that describe the field structure then use ODBC to export the data and then import the data to SQL Server.
You might look at the Pervasive Data Integrator product line. It's designed to move data from one source to a different destination.
You could also export the data to CSV and then use SQL Server to import it.

You will need a PSQL engine of at least the version the data files were created with. In your case, you'll need PSQL at least version 2000.

I have been tasked with taking some client data which was stored in Pervasive SQL 2000 and migrating that over into our own system which is currently in SQL Server 2000 (2005 can be used as well). I have their data which is nothing more than a series of 112 .DAT files. I do not have Pervasive SQL 2000 installed, but I do have MS SQL Server 2000 and 2005 installed. My first goal is to simply get the raw data ported over into SQL Server so that I can at least view the data and try to make some sense of how I can begin mapping it over into our current structure.

Thanks, Mirtheil. I have PSQL 10.10 trial version and Data Integrator 9 installed now. Since I don't have any DDF files yet but I do have all of the DAT files, how do I create a database and get the DDF files created? I'm struggling with understanding this. I thought I might just be able to attach to an existing database, but I'm not seeing that as an option.

Thanks,
Greg

Creating a PSQL database will create EMPTY DDFs. You'd need to populate them. You can use SQL statements (CREATE TABLE) or DDF Builder included in the PSQL trial. There are also third party DDF builders.
The DDFs describe the data files to ODBC and third party apps. Btrieve files do not store any field information in them.

I figured out how to get the data from the DAT files, but all of the columns come up as "unnamed_0", "unnamed_1", etc. Is this what you mean by the Btrieve files not storing any field information? These unnamed columns are pretty useless to me since I can't make sense of the relationships. Is there any way to get the original field names without having DDF files to begin with?

Thanks,
Greg

Yes, Btrieve doesn't have the field name, length, or data type stored in the file. It's all in the DDFs.

Nope. You can guess at the field names and types but it would be just that, a guess. I recommend going back to the vendor of the application and asking for DDFs.

Thank you for all your help, Mirtheil. I was able to contact the vendor's support department and they were able to point me to the DDF files! Hopefully that's all I'll need to move forward now.

Hi gstenger

I am working on something similar to what you were doing. I am struglling on getting data out from .dat files. Can you please post more details on how to get the data from .dat files? Many thanks.

THe process for getting data out of Btrieve files depends on what you've actually got. For example, if you are using a recent version of PSQL (v2000 or later) and have DDFs for your data (DAT) files, then all you need to do is create an ODBC DSN. Once you've got an ODBC DSN, you can either write a program to access the data and extract it or use a third party program to export it to various formats (like my DataXtraction which can export to CSV file format).
If you don't have DDFs, you should first contact the vendor of the application to find out if DDFs already exist. If they do not, then you will need to create them using DDF Builder or CREATE TABLE SQL scripts. You will need to know the exact record structure and data types to get the best DDFs. The other option is to use the Btrieve API which does not need DDFs but still needs the record structure because there is no field information stored in the DAT files.

Thanks mirtheil for the quick reply. I can see there are 11 .ddf file. which one should point to for the ODBC DSN?

It doesn't matter. The ODBC DSN requires a path not a specific DDF. If you have to select one, select FILE.DDF though.

we have PSQL 10. i've tried that from ddf builder. i can see the .dat files. but i have to create table definition for each of the .dat files which is really time consumming. while trying that, one of the .dat files returning an error msg as below
//psql btrieve key 7 segment 1: overlapping key found. type is not compatible//

is there a easy way to export the data without creating table definition for .dat files?

Overlapping keys can be a problem. You might be able to just issue CREATE TABLE statement (adding the USING and IN DICTIONARY clauses) to create the table definition. You could add one index but not the second (overlapping key definition). It might not be the best performance (if your export query would optimize on that missing key), but it should work.

The only way to export data without creating table definitions would require using the Btrieve API and knowing the record structure.

You could also contract with someone to create the DDFs or Btrieve API program.