Use Sesame Import to Translate/Convert Multiple Year Databases

From the Inside Sesame Help Desk

Ok, you guys have finally gotten to me. In my years with Q&A I did what you always caution about - I split my years into individual databases. I did it because I was always afraid of having too many records in my active database. I fell into all of the traps you've written about, especially the one concerning the lack of ability to search and report on multiple year data. But now I'm doomed. I have all of these records in multiple databases (13 to be exact) from 1996 to 2008. They're almost all the same. Over the years I've added a field or two to the design. Can you advise me how to do a translation to Sesame that will allow me to take advantage of having all of my data in a single database.
Francis

It is good to see that our advice didn't fall on deaf ears. You're not doomed. What you need to do here involves a few steps in Q&A before you even consider translation. Once you complete these, then your translation will involve two parts - translation and export/import.

In Q&A before any translation
What you need to do is analyze your existing databases to determine the similarities and differences. The example here is for Q&A DOS. Q&A Windows users will need to make the appropriate modifications. Look at all your databases one at a time. Suppose you determine that all of them have 29 common fields. Maybe you find that in 2003 you added two new fields and in 2006, two more. In your versions of the Q&A database from 2006 through the latest, move all four of these fields to the end of the database. In your 2003 version of the design, move the two fields to the end of your database form and add two more to match those in the latest database design. In the rest of your designs (1996 through 2002) add four new fields to the end of your design.

Note: It isn't important that the Q&A design modifications look good or even that the fields are named exactly the same. What's important is that they appear in the same order and have the same data format from yearly database to database. It's also very important that you take care in moving fields in Q&A so that you don't harm your data. When you move a field using Shift-F5 make sure to include the two letter field designators. Always, always work on copies of your databases.

The point is that you want all the various designs, from each year, to be exactly the same in terms of the number and order of the fields. It's best to have all of the common fields first and the new fields after them. It doesn't matter if they spread across multiple pages and it doesn't matter how many fields you're dealing with. Also, if you don't have a field that indicates what year the records are for, add one at the bottom and mass update all of the records with the year they represent (you would be surprised how many Q&A databases we have seen that have no information to indicate when they record was entered).

After you've done all of this Q&A preparation work, it's time to make the data available for your final Sesame design. For every database, except the most recent design, export all of the data using Q&A's menu selections File / Utilities / Export Data / Standard ASCII. This will prevent a myriad of problems, not the least of which are numeric format and non-Y2K format problems. Name each exported file in a way that easily identifies it like 2000DATA.TXT. In Q&A you only have eight alphanumeric characters to work with.

Now it's time to work with Sesame. Do your translation on the most recent Q&A database. Make any design changes you want but do not move the fields around or add new ones yet. You want to keep the fields in the same order as they were in Q&A until you finish the next step. Now, save your design and do a Preview Application. Select Add Data and then use the Spec Window to Import your data. Since everything is in the same order, you can just choose Import /Set All and deselect the first item that reads Ignore Column. Choose the exported txt files you saved from your Q&A exports (repeat these steps until you import all of them) as the item to import.

Now you have all of the multi-year data in your Designer file. Exit Preview Mode. Make any additional changes you want to see in your final Sesame application. Save the design and, from the Application Menu, choose Save as a New Application. Overwrite your original translated db file.

You are the proud owner of a new Sesame application with all of the data from all of your separate Q&A databases now merged into a single Sesame application and database.