Strategy for migrating Pan 6 data to Pan X database

Panorama X
1

In all my years of Panorama programming, I’ve never had to do this kind of thing. But as Pan X progress goes on, I’m nearing the point that I must do it, and get it right. A little less than a year ago, I started using Pan X, and converted my major databases to Pan X to get used to it and make things work, in addition to adding a number of features. They are performing reasonably well, but their data is now very out of date. Eventually I will need to bring them all up to date with Pan 6 data, and have Pan X be my working system with current data, when the day comes that Pan X is ready. I want to do a few dry runs of the process to make sure I can do this successfully when it counts.

There are so many Export and Import options available, and I’m uncertain which will work best. My databases have thousands of records, with as many as 200+ fields. All data types, many with embedded commas and quotation marks. Nothing has embedded tabs, as far as I know. I would guess tab-delimited export/import would work best. My plan is to delete all (but one) records in my Pan X database, then import all records from my Pan 6 database without the “title” row so that Pan X now has all the data. I expect to use File>Export from Pan 6 using the Text Export Wizard, creating a file, then use File>Import>Text Import Wizard in Pan X to bring the contents of that file in. Will this be expected to work without issue, or are there gotchas I have to look out for? Simply converting my current Pan 6 databases to X is not an option, since I have made many procedure and form changes in my Pan X versions that I do not want to make again.

Am I on the right track? Anything to watch out for that I’m not aware of? I will certainly make backups of my current Pan X databases in case I foul something up.

2

I tried the Export/Import technique on a very small database (8 records, 8 fields) and I got this error:

My database (6 and X) has some superfluous fields, so I reduced the fields in my Exported text file to just the 2 that mattered. Same error, which I don’t understand.

Since my X version originated as a converted file from 6, and I haven’t modified it at all, I don’t understand how I could have “insufficient fields” to be able to import.

3

Scott, with your small test database it should be quite simple to check if the Pan X database has the same number of fields (and the same field types) as your Pan6 database. Or you will easily see which field is ignored during the import. Make sure your export formula does not add an extra field accidentally.

I am doing text imports and exports daily, and it works very well for me. When all the fields exist in both databases in the same order, then it is really easy. Otherwise you can adjust the order of the fields or select fields during the import with the importtext statement and the REARRANGE option.

There might be problems with cells containing multi line text. Then it may be necessary to prepare the data before the export and replace carriage returns in those cells with e.g. vertical tabs.

4

Kurt, I did finally solve this particular problem by dragging the data fields from the Import Text File area into the Import Arrangement area in the Text Import Wizard, and that worked. I didn’t found documentation for this action, I just tried it and got lucky. And it lets me overwrite existing data, which saves me a big step in my larger databases. But this technique will be a major job with my much bigger databases, unless I’m missing a much more direct way with this wizard. I did find the Help entry for Text Import, and I will study it in depth before I come back with more questions.

5

This is documented on the Text Import page, under Assigning Text to Database Fields. In this case, however, you probably want to click on the Template icon and choose ALL DATABASE FIELDS, also documented, instead of individually dragging the fields.

Also, if you read further down, you can save your import configuration. Also, I believe that the import action is recordable.

Directly Transferring the Data

If your Panorama X fields are the same as your Panorama 6 fields, I think it it might be better for you to rename the Panorama 6 database (perhaps add a suffix), open the renamed database in Panorama X, then use the File>Import>Import Database dialog to directly transfer from one database to another, without needing to use text as an intermediary. Again, this is recordable so you could easily make a procedure if you find you need to do it over and over again.

6

On a related topic, which might be useful here: seems like I’ve always had problems with the special characters Tab and CR in Panorama. On the one hand, it was very convenient to be able to have them embedded into the data, and view that data occasionally in the Data Sheet. The more that I needed to manipulate that data, though, the more problematic it became - particularly when working with arrays. For one, it was tough sometimes to get a quick look at the data in the datasheet, since vertical tabs, horizontal tabs and space runs all look the same. Hidden CRs were also a problem with exports, etc.

In Pan6 I became accustomed to doing virtually all my data manipulation by exporting data into an array first, then working on it for either display or re-import - very powerful, and useful when formatting for lists, matrices and other databases. Exportline(, ArrayBuild and ArrayFilter and other array( functions did most of the work. Since I was doing this a lot, I started converting the data before storing in the cell. The character conversion scheme that worked best, for problems with embedded CRs and tabs, was to simply substitute the literal quoted character for the special characters recognized by Panorama. This works as well as any others I’ve seen, with the added bonus of making the text easy to read in the cell. In fact, I wish Panorama could do this automatically, as it makes exports and arrays much easier to handle. Converting text to store in cells would look like this:

Text=replacemultiple(Text,¬+";"+¶,"¬;¶",";")

I was doing this at the end of most procedures. Databases with text formatted this way can have data imported, exported and otherwise manipulated all day long without a hiccup. Exportline( works great, as do all the array/build/filter( functions. The text can be restored to it’s original format using the same function, with the parameters inverted:

replacemultiple(Text,"¬;¶",¬+";"+¶,";")

(for display, or to handle a field like a table in a relational flat file scheme). When used without the quotes, Panorama sees them as tabs or carriage returns. Most of the time, though, you can do everything using the quoted literal separator characters ("¬", "¶").

In your case, bringing in the data through conversion from Pan6 to PanX, then importing into your updated database, sounds like the most reliable solution. However, embedded Tabs & CRs will always be problematic, just waiting to pounce on some innocent exporter, so a dependable conversion protocol might come in handy someday.

7

Text Import, or better Database Import, looks like the ticket. I have a couple of questions about it.

I assume that when records are replaced (overwritten) by an import, that the entire record overwrites the old one, regardless of whether any fields are empty. That is, if the original record has data in field A, and the replacement record was blank in that field, “blankness” will overwrite existing data?

I have at least one case where my Pan X database version has a few more fields than the original Pan 6 version. They are all added at the end of the database. Does this cause any issues with the import?

I found a typo in the Help topic “Import Database.” This line:

Opening the Wizard — To start, choose File>Import>Text Import Wizard to open the wizard, then select the database to be imported.

should read “…File>Import>Import Database” as the little movie shows.

8

When an import uses the “Replace Existing Data”, all trace of the old data is removed.

There should be no problem with the additional fields at the end of the Panorama X database.

I’ve corrected the typo in the documentation. FYI, from within the Panorama Help wizard you can open the corrections wizard and make the correction yourself and submit it. It’s probably actually less work than reporting a typo here.