05-12-2010 03:12 PM
Hi Folks, I'm hoping somebody can help me with this.
Short version: For Act! 6, in the raw database, what is the format or method for decoding a compressed field such as the timestamps and unique_id fields?
I have inherited a project from another developer (no longer here) where we are building a tool that anaylizes relationships between contacts. The source of the contacts is an Act! 6 database. In addition, I have to access the activity data, as well. The flow here is that we import the data from an Act database into our own database and run our analytics there. We then take the results and pump them into a series of charts and graphs.
I have written code to successfully extract all of the data from the native dBase files and import them into our target database in mySql. The problem that I am having is that the date and unique identifier fields are, according to the Act SDK documentation, compressed. Unfortunately, the SDK does not explain how to uncompress the values. Adding to my pain is that using a 3rd party tool (such as Act Exporter) has been rejected by my manager. Use of the OLE object in Act 6 is also out, as we do not use Microsoft tools such as Visual Basic - we're a C and Smalltalk shop.
If anybody could offer some guidance on how to convert the compressed data, I would be very appreciative.
Thanks in advance,
05-12-2010 03:48 PM
05-13-2010 03:21 PM
Thanks for the response, Mike. Unfortunately, the field values are not numeric. Here are a few of the timestamp values that I see. Again, keep in mind that this is Act 6.0.
I guess I need to figure out how to decode these values first. Any suggestions?
05-14-2010 02:14 AM
Base 256 using the ASCII chart - that's the "compression" :-)
You will get a value (on the order of 970,000,000) that is the number of seconds that have elapsed between the time in question and Jan 1, 1970.
06-03-2010 08:14 AM
I want to thank you for taking the time and trying to help. It means a lot to me.
I wish that I could say that I've been successful but converting the encoded date as a base256 value keeps resulting in a number that is too small, with a date that is around 1982. It should be 2005.
If you're interested in helping, I can post the code snippet here. Likewise, if you have some code that you're willing to share, that works, too.
Regardless, thanks again for your help, Mike.
06-06-2010 12:47 PM - edited 06-06-2010 12:47 PM
I'm also seeing some timestamps with six characters which results in a value far into the future.
Here's a breakdown on the conversion of one of the examples listed above, "]E'B3"
Char ASCII Value
3 51 51.0
B 66 16896.0
' 39 2555904.0
E 69 1.157627904E9
] 93 3.99431958528E11
conv: 400592159283 -> Sat Sep 11 07:35:59 EDT 1982
Total = 4,005,921,592,835 seconds
Resulting date = Apr 4, 14664 03:48:03
If the value is to be treated as milliseconds instead of seconds, the resulting date is Sep 11 1982 07:35:59.
The expected date should be Sep 27, 2002 10:26:11 AM
Also, I'm struggling to decode the recurrance detail field (RECURID in the activity table). I've found information on how to translate the value, I just can't seem to decode it.
Lastly, for what it's worth, I tried the conversion that you have recommended for other people. Unfortunately, it does not preserve the relationships between an activity and multiple attendees (as defined in the REL table), as well as the recurrence details. The recurrence info is encoded and I have not been able to get any further information from the vendor.
Again, thanks for all of your help,
06-06-2010 12:53 PM
Why don't you just use the SDK?
You'll spend a very long time trying to access the data directly
06-06-2010 01:18 PM - edited 06-06-2010 01:20 PM
We're not a .NET shop and, as I understand it, the SDK requires Visual Studio (?). Despite that, I looked into the SDK from home (where I could install VB Express) but could only find support going back to ACT v7. This is an ACT v6 database. I wasn't able to locate an SDK for v6.
I guess if I had the v6 SDK I could write an export app. Without it, I have managed to extract all of the data with the exception of the record timestamps (for creation times) and the recurrence details.
p.s. What are you doing up so early? Isn't it something like 4am in AUS?
06-06-2010 01:22 PM
No, you do not need .Net for ACT! 6.0... it's was written well before .Net.
We still code apps for it.
I also use an export app for ACT! 6.0 - http://www.glcomputing.com.au/main/69/2
06-06-2010 02:00 PM
Yeah, I looked at ExportIT, as well. It's difficult to see what it's capable of doing as the demo claims to only output 10 sample records. In reality, it converted between 1 and 4 records, and only about 7 or 8 fields - none of which preserved the recurrence information or the fact that an appointment can involve multiple contacts. So, in this situation, I am not able to use it.
So, that leaves me with the SDK or decrypting the fields. And you're right about it not being .NET for ACT 6. I remember from my research that it is actually OLE. Still, without having the SDK itself, I'm still stuck.