2

First time poster here, so be gentle... I am completely stuck, I have been given the task of importing some old .DTA files into tables in SSMS so we can run queries off of them. Being brand new to .dta formats, I did some digging and this is the information I was able to gather up so far:

  • .DTA files are data files generated by Btrieve software, which Btrieve then had a name change to PervasiveSQL, which is now owned by Actian
  • .DTA files have accompanying system catalog DDF files (data definition files)
  • According to this post https://www.tek-tips.com/viewthread.cfm?qid=364542 I should be able to download the PervasiveSQL demo to be able to explore these DTA files but now I am taken to the Actian website when I click on the links provided.
  • We do not have access to the front end software anymore

When I go to the Actian website, its asking me for a username and password to be able to download any trial software.
Before I go signing up for anything, I wanted to check in with you smart people to see if you know of any alternatives to extracting the data out of these legacy .DTA files so I am able to populate it into SQL tables. BTW, I have tried all of the obvious; exploring in notepad, excel, access, using any/all drivers in SSMS for importing data/openrowset and even trying to use an ODBC driver for Btrieve and none of those have given me any success.
I would just LOVE it if there was something obvious I am missing or if anyone has had any success using the Actian software.

Thanks in advance!

MichelleSQL
  • 21
  • 2

1 Answers1

0

You must have an engine that can read the DTA files. If you have the DDFs that match the DTA files and the engine that can read them, you can create an ODBC DSN because the engine includes ODBC drivers. Once you have the ODBC DSN, you can use SSMS, Access, Excel, or any other ODBC tool to access the files.

I would recommend downloading the Zen Workgroup engine v14 Trial for Windows x86 32/64-bit from the Actian website (as of Sept 2019). It is the current version and should be sufficient for a one time (or after purchasing a license, multiple time) import.

mirtheil
  • 8,952
  • 1
  • 30
  • 29
  • Oooouuu, this all sounds very exciting! Do you know where can I find this engine that can read the DTA files? – MichelleSQL Sep 26 '19 at 22:36
  • Added some info on downloading a trial version – mirtheil Sep 27 '19 at 02:47
  • Ok thanks very much for the direction! I will give this a try :) – MichelleSQL Sep 27 '19 at 13:32
  • So I was able to use a Pervasive ODBC engine and extract most of the data out however for some of the longvarchar fields, I am getting the error "the chunk offset is too big" even when I am simply trying to view the table'/column in pervasive SQL control centre. This isnt the case for all of the longvarchar fields, only some. Do you have any tips on resolving this Mirtheil? – MichelleSQL Oct 10 '19 at 17:37
  • Usually that error means the field in question is defined as a LongVarChar but is really a legacy Note field. You might be able to change the field type within the Pervasive Control Center and try again. It could also mean that data file has a variable length portion but the DDFs don't define it or define it as a fixed portion. It's been a long time since I've seen that error. – mirtheil Oct 10 '19 at 18:19
  • Yes that is correct, it is a LongVarChar data type. We have tried to change the field type in Pervasive Control Centre with no luck. It continues to throw that error. Are DDF's editable where I could change whatever the definition is for that column? Or have I reached a dead end? – MichelleSQL Oct 10 '19 at 19:47
  • You could try using DDF Builder to fix the DDFs. Or you could ask the vendor of the application to generate correct DDFs for the data files. I haven't worked with it in a few years – mirtheil Oct 11 '19 at 13:44
  • The DDF builder was absolutely the answer! Thanks so much, we were able to use that tool and change the data types from longvarchar to a string. Appreciate all the help and guidance! – MichelleSQL Oct 15 '19 at 20:30
  • Hey Mitheil, I was hoping you might be able to provide additional helpful tips. The fields we converted using the DDF builder from longvarchar to string are actually larger than the largest string size available. We have it set to 8,000 characters max however, because these are legacy clob fields, they contain so many more characters than that. Like a memo field. We need to be able to extract out the full dataset, which is greater than 8,000 characters, but cannot get past this limitation it seems. Do you have any suggestions for how to obtain the full dataset from these clob fields? – MichelleSQL Nov 19 '19 at 18:20
  • Did you set it as a string or as a Note? It sounds like this field is a variable length field and if it isn't a LongVarChar then it is probably a legacy Note field. You might be able to switch the column from a String(8000) to a Note field type and get all of the data. – mirtheil Nov 19 '19 at 18:52
  • There was no option to choose Note as a field type unfortunately. It seemed like string was the only text based field we could choose in the DDF builder tool. – MichelleSQL Nov 20 '19 at 14:15
  • If the field really is a variable length (As seen in a BUTIL -STAT on the file), you should see a Note or LVAR in the Btrieve data type for that field. Here's one I created using PSQL v11: https://i.imgur.com/k6K3BLz.png If you don't see it, it might not be a variable length field. – mirtheil Nov 21 '19 at 01:12
  • Actually, when I checked I was able to choose Note when going from the String(8000) however, it does not give you the Note option when going from clob. I am afraid that when we changed it to String(8000) we have lost the data beyond that character limitation. What is the max character limit for a Note field anyways? I see you entered 50 in your screenshot. – MichelleSQL Nov 21 '19 at 15:43
  • You wouldn't lose data unless you updated the record based on the DDF definition. The 50 in my screen shot was just what was put in by DDF Builder. The largest I was able to set was 32,765. Not sure why it isn't 32,767 but it probably is related to overhead in the file. – mirtheil Nov 21 '19 at 19:14
  • Strange, when we changed it from String(8000) to Note(20000) and did a character count on the field, we only saw max counts of 8116 and 8179 when we know there is data upwards of 12,000 characters in there. We are going to restore our data back to the original clob definition and try to change it to Note or LVAR as you suggested. I will let you know how it goes, wish us luck! – MichelleSQL Nov 22 '19 at 16:27
  • Well I tried to change the field to Note(32,000) in the DDF Builder and was only able to see a max of 8,191 characters and when I tried LVAR(32,000) it only kept a max of 8,190 characters. This is when I was going from the original clob(8) datatype. We know from the front end application that this field has at least 16,000+ characters in some cases. Is there any other way you know of that we can access the full dataset? – MichelleSQL Nov 25 '19 at 15:52
  • Without actually seeing the data and the definition, I can't offer any other suggestions. Normally the NOTE field is the answer for older Btrieve files with variable length fields. – mirtheil Nov 25 '19 at 16:23
  • Is there a way I could use Python to extract the data from the .DTA file? When I google DTA export using Python, I see alot of posts about stata and using the pandas library to export it however I am guessing "stata" is something completely different than these btrieve .dta files. – MichelleSQL Nov 28 '19 at 13:28
  • There's no direct access to Btrieve data files in Python. If you are using PSQL / Zen v13 or later, you might look at https://www.actian.com/company/blog/python-btrieve-2-windows-accessing-actian-zen-data-no-sql/ which shows how to use the Btrieve2 API from Python. – mirtheil Nov 28 '19 at 16:22