I have a two-part question which basically involves the same issues.

We have a QA database with 9 "History" screens containing text. I have translated that to Sesame and I will be merging the separate fields into one long one.

Those are in a Status database.  Every day, changed records are copied to a Past Status database for archival purposes.  In other words, whereas there is only one File No 123456 in QA (and eventually the new Sesame version Status), Past Status could have dozens of records with that number, providing an archival history of changes sortable by Last Update.  In QA those records are Copied from Status to Past Status.

It seems the only way to transfer data from one Sesame db to a different one is with CSV export/import.  However, the User Guide says at page 352:

Export Limits
There are no limits on the number of fields, the number of records, or the total output
file size. There is a limit of 4Kb for a single field, in a single record. So if you have a
comments field and for one record there is over 4k of text in it, that will be truncated.


I suppose there is a kludgy way to write the entire field to a text file with a unique ID, pass that ID to the receiving program, and then have the receiving program read in that file, insert it into the (duplicate format) field, save it, and delete the kludge file.  But that seems an awful mess for something that should be pretty easy--copying records in the program's native record format. Plus, I have added several new text fields (emails/letters to clients, in-house emails, and "General Storage" for "things that just don't elsewhere"), so it would involve writing about 4-6 files per record.

Creating a subform with individual records by date isn't really an option--various people often have to read through all the notes searching for "something we didn't notice before".

I did some testing and there is a "sort-of" limit than can be overcome with a simple filter.

A text field up to 100,000 characters (a-z and A-Z) exports without changes. A field with 100,020 characters or more adds Chr$(13)\n after every 97th character. (According to Notepad ++ v. 5.8.2 (UNICODE))

The same happened with fields containing 200k, 300k, 500k and 1,000,000 characters.  The files with multiples of 100k consisted entirely of lower and uppercase letters.  The 200k+ files consisted of a copy/paste of the 100k file done in Windows XP Notepad saved as a file, then those files copied and pasted to new Sesame records, so the text was identical--just more of it.

The extra characters in 100,020 and 100,050-character files consisted of 0-9, i.e., the files only contained alphanumeric characters and no control characters.

All exports were done from the same field in the same database, just different records.  Even the 1,000,000-character field exported without truncation.

The exported version of the 100,020-character field is 103122 characters long, i.e., 3102 added characters, 1035 lines, the final one being partial, 1034 lines with Chr$(13)\n.

I tried re-importing the 200k field but it only took the first 97 characters.  I loaded the same file into Notepad ++ and used Extended replace to replace \r\\n (note the 2 slashes before n) with nothing.  The entire 200k field then imported.

So, if any individual field exceeds 100,000 characters, the CSV file must be run through a filter before being imported. Exports and Imports of million-plus character fields should be possible, since the same characters were added at the same interval for all fields over 100,000 characters. When exporting text fields containing blank lines, Sesame adds the characters \n but they are not preceded by Chr$(13) and the end-of-record separator won't be preceded by the \n characters, so the filter will not affect anything else.

Another interesting application of this is that a simple uuencode/decode combo should enable storing binary data or images in a Sesame text field.

How did you put the data into these fields?

Cut-and-paste in Windows, has very severe limitations, and can alter content based on assumptions about content. Additionally, the text editors available on Windows, notpad in particular, may introduce formatting to the copy buffer.

On export, Sesame will replace actual new-lines (13, 10 on Windows, just 10 on all other OSes) with figurative linefeed ("\n" / "\r") to prevent other programs (including Sesame) from breaking field mid-quoted string. Sesame knows that these exist and arranges the replacement back to true new lines on import. Since "\n" and "\r" are standard notation for a linefeeds, many other programs will also respect the intent and replace these with actual newlines as appropriate to the OS. This has nothing to do with field length.

On the other hand, almost all text editors have line length limits, and will break line automatically if these are exceeded. If you know that your export has fields exceeding the line length limit of your text editor, it is unwise to view the file in that editor as a means of determining the actual content, since it will enforce a particular maximum line length. Notepad is notorious for wrapping, truncating, and generally failing to deal with long lines (> 1024 bytes) and long files, depending on the version.

Uuencode uses base 16 (hexadecimal) to encode binary data. It is very inefficient for the encoding because it must use two bytes to encode every single byte that is encoded. You might consider using base64 encoding. Or, more simply, storing any large binary info in separate files and putting the path to that file in a text field in Sesame. Since displaying uuencoded (or base64) binary in a form is visually meaningless, unlikely to be usefully searchable, is likely to bloat the application file, and would likely need to dumped to a file to be used, it may well be better to start where you are likely to end up anyways. This basically true for any database engine, even those with block binary data types.

What sort of binary are intending to store?

I can confirm Lantica's findings that Sesame is NOT modifying the data.

I just did an import/export of a file with 1,000,500 characters, and I ended up with an exact match of what I started with. I did the test once by importing the text file, then exporting it; and again by using copy/paste to put the text into Sesame, then exporting it.

The only things extra were the opening and closing quotes, and a CR/LF after the last quote.

(I created the test data in NotePad++ by following the directions listed here. I began with 100,000 alpha characters, then added 50 numeric digits, and finally copied and pasted that until it totaled 1,000,500 total characters.)

Regarding storing binaries, I'm going to start a different topic.

I'm using the Personal Version 2.5.2.

I have an unrelated form command button, Test Code, which simply executes whatever is in the On Element Entry code.  I set that up to display the length of the field I have been exporting.  It shows the 200k record as exactly 200k but the 1 MB field (which I pasted in a few days ago) as larger.

I tried again pasting the 200k file into a different record and now it's exporting that correctly, although I don't think I've done anything differently!  (Even used same source file.)

I guess this is one of those "occasional weirdness" computer glitches.  From what I can tell, I doubt any one field in our application will ever get to 100k+.  So if it does come up again I'll know what to check out.