Problems with appending to a current database

Oh, for the simplicity of Panorama 6!
Each week, with a new set of campers, we transfer the current camp week’s registration data into Panorama so that we can use the forms and procedures we have taken years to develop and design.
Tried typing Append into the Help wizard but to no avail. Spent a couple of hours going through the videos, once again not finding an answer. Then I found this topic on the forum, so I was able to import and append the text file. But no matter what I try my 670 records file turns into a database with 962 records, even though the database template started with only one empty record.
With Panorama 6 it was 3 clicks and no dialogs, all the info was into the database in the right columns and ready for us to print counselors lists, snack accounting charts, mini camper lists for the lanyard badges, etc. Tasks accomplished quickly with creative macros and stylized forms that my PC Excel friends could only marvel at.
But now I can’t get my information accurately into the database. Any help will be appreciated!

I think you need to look at File>Import…, which brings up the Wizard.

Hi, Bruce!
All the wizard does it show me where I find the “Import Text File into Current Database” choice. I’m not having problems figuring out the steps to import/append now, after reading the forum topic from another user who was having problems appending. I click on File>Import and then choose Import Text File into Current Database. It picks up my 670 records from our online reg program and sets them into that Excel-looking dialog box. That’s where the problem begins. First, I have no idea what all the boxes are about. The top one seems to just list each of the records, but the bottom one asks for “template” info and “import arrangements”. I just want it to import all the info from the .csv in the same order of the columns of the Panorama database template. I know they match exactly, as I’ve transferred onto a Panorama 6 database that I used to create the Panorama X database and the columns match exactly and the number of records match exactly. However, even if I choose the Import Text File into a new Database choice, it still turns my 670 records into 952 records. Since it imports the number, I see the extra records and they have no number associated with them. So, it’s taking some of the records and making them into two records.

That would indicate that there are some records with characters that look like delimiters to Panorama. It has sometimes been frustrating to me to track these things down, especially since the delimiters may be invisible characters, or that Panorama X is not handling commas in fields correctly. You need to find the records that have been split, and look at what is in them.

I’ve wracked my brain, and I can’t think of any way to import text into Panorama 6 without a dialog or wizard. I’m guessing that you used the Open File dialog, and your three clicks were Enable Text Files (Iimport) (pop-up menu), Append to Current Database then Match Fields by Name. Then a fourth click to press the Open button. Is that correct? There certainly is a dialog involved.

As Bruce mentioned, in Panorama X appending text is done by choosing File>Import. You can find this in the help system by enabling Full Text Search and typing in Append. The first result is a page from the tutorial for importing text where appending text is shown, and the Text Import page is also listed, which explains all of the available options in the import window.

However, I suspect that you have already found this option, you say that you did manage to import (ok, during the time it has taken to enter this you posted a second time confirming this). So now the question is how many records are in your text. Where is this text being transferred from? How do you know it is 670 records? I’m guessing that there are extra carriage returns and/or linefeeds in the data, but without seeing the actual file I can only guess. If you want to send the text file to the support email address listed on the ProVUE web site I can take a look at it. I can tell you that Panorama X is actually much better than Panorama 6 at handling imported data that contains line breaks in the data, and in fact has a much much better import engine in general. There are lots of text files that would choke Panorama 6 that Panorama X handles well, and of course Panorama X can import files with Unicode characters, which is standard these days but not handled by Panorama 6 at all.

You have an excellent point here, Panorama X does not have the equivalent of the Match Fields by Name option. That was an oversight, there really should be an option to do this automatically in the Text Import dialog. When the imported text contains field names on the first row this would be a very nice enhancement. I didn’t think of it before and it hasn’t been mentioned before. I have added this to the list of ideas to be implemented, thanks for stimulating the idea.

Maybe you aren’t using the Match Fields by Name option after all? If the fields match exactly, you can just choose the ALL IMPORT COLUMNS template and everything will be set up for you.

Many users did not find appending to be simple in Panorama 6. First of all, they didn’t know to look in the Open File dialog, many users told us that wasn’t obvious at all. Secondly, the popup menu to enable import of text files was very difficult for many users to find. We’ve heard from lot’s of puzzled users on that one over the years. These things are easy and simple to you now because you learned them 20+ years ago, but for a new user, often not simple at all.

Okay. Will do. Thank you!

Glenda sent me a copy of her database and the text file. I think I’ve got the situation figured out. By the way, I completely failed at getting this text file to import into Panorama 6.

The underlying problem is that the text file is a comma separated (CSV) file, but it contains tabs in the data. This really confuses the Text Import wizard. Apparently this data comes from a web site with data entered by end users. End users frequently do silly things, like put tabs in the middle of data. In the file Glenda sent me, there are 79 tabs randomly embedded in various data cells. The web site that collects this data really should filter these out, convert them to spaces or something.

The other issue is that some of the fields contain line breaks, in other words individual cells contain multiple lines within them. So if I open this text file with BBEdit (a text editor) it shows that the file contains 953 lines. And when this data is imported into Panorama X using the Text Import window, you get 953 records. All of the records that contain line breaks are messed up. Normally Panorama should handle these embedded line breaks correctly, but the tabs confuse it so it doesn’t work properly.

It turns out that if you simply drag the text file onto Panorama, it correctly ignores the tabs and creates a new database with 671 records. You could then use the File>Import>Import Panorama Database into Current Database command. I tried this and it worked. Here’s the help page for that.

However, I suspect the 79 tabs are still embedded in the data, which could cause problems down the road. So I wrote a short program that removes the tabs and imports the data correctly. You can simply take the code below and paste it into a new procedure in your datqabase. When you run this procedure, it will display a dialog asking you to locate the text file, it will then import it. When I run this, I get 670 records.

local fileChoice
choosefileDialog fileChoice
if fileChoice="" return endif    
let newdata = lftocr(replace(binarytotext(fileload(fileChoice)),tab()," "))
importtext arrayrange(newdata,2,-1,cr()),"ExistingData","Replace"

This code will replace the existing database contents with the new data. If you want to append it to the existing data, change "Replace" to "Append" in the last line of the code.

Note: The arrayrange( function strips out the first line of the imported text, since it contains field names. This is for your convenience, I could have left that out but then you would have had to manually remove that line after importing.

I’ll still need to see if I can figure out a way to get the Text Import wizard to handle this messed up data. I’m not sure if it is possible.

I’m a bit puzzled as to how this ever worked in Panorama 6. Presumably earlier exports from this web site contained the same kinds of problems in the data. This is the type of text file I would have expected to tie Panorama 6 in knots, and as I mentioned, it didn’t work for me. Oh well, not going to worry about that any more.


P.S. So it turns out that the Match Fields by Name option wasn’t a factor here, but it’s still a good idea for the future.