Characters "\N" stripped from table data exported "Tab Delimited"

[A9G-Data-Droid]

I have been using table export "Tab Delimited" to keep track of some front end settings I like to ship with my front ends.

I noticed in the most recent test with version 3.4.13 that my data got mangled. Specifically I have a field value that contains a path which includes "\N". These two characters were stripped from the restored table data.

It looks like this is part of ExportTableDataAsTDF
MultiReplace(Nz(fld.Value), "\", "\\", vbCrLf, "\r\n", vbCr, "\r", vbLf, "\n", vbTab, "\t")

and ImportTableDataTDF
MultiReplace(CStr(varLine(intCol)), "\\", "\", "\r\n", vbCrLf, "\r", vbCr, "\n", vbLf, "\t", vbTab)

EXAMPLE:
This
\\PathThatContains\Nwillbetruncated\example.txt

Would become this
\\PathThatContainswillbetruncated\example.txt

[joyfullservice]

Hmm... That's a tricky problem to solve. The \n code is very likely to be included in legitimate content, as you found in your settings. Of course you could export to XML instead, but it doesn't have the simple readability of a CSV. Any thoughts based on this discussion?

[A9G-Data-Droid]

The solution involves escaping each symbol that is going to be modified by the replace, before export, and then de-escaping after import. I know that this replace code is itself an attempt to escape tab and EOL markers. Now you have to escape your escaped output because what you are outputting is valid text that could exist in the value string.

So if the field value contains "\r\n", "\r", "\n", or "\t" those need to be escaped. To avoid an endless loop of escaping we need to use an invalid character as the escape character so that a user can't accidentally use an escape character in their table values.

According to Microsoft Access Data Types

"CHAR, LONGVARCHAR, and VARCHAR | A character string literal can contain any ANSI character (1-255 decimal)"

The only thing I can think of is to use a UNICODE character as the escape char but we know that could get us in to trouble with internationalization.

[hecon5]

So, what if we use an emoji for fire exit? 👨‍🚒 or give up a d use the white flag of surrender to give in to escapes?🏳

[A9G-Data-Droid]

Or we could choose an unlikely candidate like an unprintable character. If someone is putting unprintable characters in to their database I would wonder why.

[joyfullservice]

We could also wrap them in curly braces like this: My text {\n} on the next line. It would be pretty obvious in the code, and much less likely to collide with legitimate content. The drawback is that it is non-standard...

[joyfullservice]

Unless anyone else has a better suggestion, the curly braces should solve the issue of avoiding the data collisions while still being readable in the source file.

[joyfullservice]

Thinking about this some more this morning, it occurred to me that we can just use an interim substitute character for the backslash in the sequence of replacements to completely resolve this issue. A slash in the original content will be replaced with a double-slash, and when restoring, the double-slash will be restored back to a single slash. Because we are using the interim replacement of Chr$(26) (ASCII substitution character), it will no longer collide with existing data. (Unless someone decided to use the substitution character in the content, which seems unlikely.)

    FormatStringForTDF = MultiReplace(strValue, _
        "\", Chr$(26), _
        vbCrLf, "\r\n", _
        vbCr, "\r", _
        vbLf, "\n", _
        vbTab, "\t", _
        Chr$(26), "\\")

    FormatStringFromTDF = MultiReplace(strTDFValue, _
        "\\", Chr$(26), _
        "\r\n", vbCrLf, _
        "\r", vbCr, _
        "\n", vbLf, _
        "\t", vbTab, _
        Chr$(26), "\")

This achieves the ideal solution of human-readable standard, recognizable codes in the text, but not causing problems if you happen to use a path like c:\my\new\folder where \n is already embedded in the string. It will look like this in TDF: c:\\my\\new\\folder and restore to the original value when loading back into the table.

I am pretty confident that this resolves the issue, but feel free to reopen if you encounter any problems!