Where are they ?  Why missing?
I have no answer right now, will give that some thought.

How to find them?  Here are two methods that come to mind....

1.  Sort records by unique ID in both Sesame and Q&A.  Move to Table View for both. Now check every few hundred records in order to find matches.  Record 300 should be the same value for both.  Record 600 should be the same, etc.  When not the same, go backwards by half the difference to determine where the  change happens.  Continue this as needed.

OR

2.  Export from Sesame to Ascii.  Import into empty copy of Q&A file.  Do XLU from original good Q&A for unique value in imported Q&A file.  Check XLU for value that does not exist in the imported file.  If done as a report, you can have a list of those records.  Or can do a Mass Update, selecting the records that XLU does not exist, and mark a field with "MISSING".  Now can retrieve records with "MISSING" and work on what happened, or how to export to Sesame again.

Well, I powered through this.  I opened the exported file in Excel; it had 10317 lines.

Then, in Sesame, I sorted my imported records by code (it's my customer file), and got the code number to match up to the line number.  Then I just had to keep moving down the screen, watching for mismatches, and recording the missing numbers (essentially exactly as Bob suggested, though I hadn't seen his suggestion yet).  Each time, I checked the Q&A file to see if the missing records had any sort of weirdness to them, but nothing jumped out at me.  Eventually I got through all 10302 Sesame records and had a list of missing Q&A records.

Then, back to Q&A.  I exported those records, and imported them into Sesame.  Sesame accepted them all.  I now have a matching count in both programs.

But no idea why they were missed in the first pass.  I hope it doesn't happen when I export/import my invoices and workorders, because there's far more than 10,000 or each of those (approximately 3 times as many in each database, and I already know that there are missing numbers in each of them.  I've been scrupulous about keeping Customer clean, with no skipped codes, but it hasn't been a concern with Invoice or Workorder.)

Perhaps it's related to the record loss issue that Spencer brought to light?  I have not yet upgraded everything to 2.0.2, but I will before importing my Invoices and Workorders.

In any case, Customer.db is happy and full.  Time for a coffee break.  Thanks for the advice, Bob and Erika.

Scott,

Were the missed records together, or were they scattered throughout the export file?

One thing that I have seen cause this is special characters like carriage returns or quotation marks in the data itself.

If you want to send the text file and the list of ones that got skipped into us, we'll see if we can spot any quirky data.

Infinity wrote on Aug 31^st, 2007 at 4:16pm:

Yep.

Ray, you are amazing.  Thank you so much.

You mentioned a cascade.  Is it possible that data from one record got mixed into another because of the escape character?

Are there any other characters I should test for?

Is it just at the end of the field where the character causes a problem, or anywhere within it?

Quote:


The backslash will only be a problem at the end of a field. The Quote will be a problem anywhere in the field. What you can do is replace the " character with two single ' characters.

-Ray

OK.  Yikes.

How do I use @REPLACE to replace a quote, when quotes are used to enclose the text to be replaced?

Remember don't forget to fix those two fields that just have a \ in them.

-Ray

Quote:

Yep. got 'em, first thing.  Thanks!

I used F11 & F12 to get the double braces out of there, and now a search for quotes returns no results.  The escape slashes are gone as well.  So unless there are other characters I need to look for, I think I'm good to give the export/import another try.  Please let me know if there are other bugaboos I should search for.

Tomorrow I'm going to have to clean up my invoices and workorders.  Not looking forward to that.

Thanks for your help with this Ray, it is much appreciated.

Thanks again for your help, Ray.  I appear to have successfully imported 10318 records into Sesame (one of my users snuck a new record in there when I wasn't looking) and a quick scan shows no data errors.

Thanks for the info on text in non-text fields (I posted the question earlier in the Weeds forum), as it will be an issue when I do my Workorder imports.  I think what I'll just do is if they've put text in a non-text field in Q&A, I'll just make the target in Sesame into text, rather than try to clean it all up.  After all, I do want to get some sleep this weekend at some point.

Infinity wrote on Aug 31^st, 2007 at 9:19pm:

Are you sure that they are not two single quotes right next to each other? You probably have a variable width font now. Try backspacing over one of the quote marks and see if the other remains.

Now that I've changed all my quotes into double apostrophes in Q&A, exported the clean data, and imported it into Sesame, is there any reason why I shouldn't perform Mass Updates in Sesame to turn the double apostrophes back into quotes?

Bob_Hansen wrote on Sep 2^nd, 2007 at 5:34pm:

Ditto.

Sesame is smart enough to add a backslash before any double quotes that appear within your data, so it won't be a problem if you need to export/import again with Sesame.