Data Character Issues

[ppachen]

I'm running into issues with the carriage return (Enter) and ampersand (&). I typically see the carriage return used in an Address 1 field, separating two lines that should have been placed into Address 1 and Address 2. Once I compose, the rest of the address will be missing after the carriage return.

The ampersand only causes an issue when it is placed next to two letters. "D & B" will not cause a problem but "D&B" will make the "&B" disappear in the final PDF output.

I would like to have a fix in FusionPro for these issues, although I'm open to ideas for correcting the problem within Excel.

Oh, and I'm using FusionPro v4.2P1d.

Thanks!

[LeberMac]

I've seen this happen as well, if you replace:

&
with...
&

It seems to work - It looks wrong in the preview, but, upon composing, it works correctly.
Did this using FusionPro 5.8, though. Dunno if it will work in 4.2...

[Dan Korn]

This problem is exactly what the NormalizeEntities function was designed to resolve. There are several things you can do:

Use the NormalizeEntities function in a rule with the "Treat returned strings as tagged text" box checked, like so:

return NormalizeEntities(Field("MyFieldName"));

Or, you can simply return the field value in a rule with the "Treat returned strings as tagged text" box unchecked:

return Field("MyFieldName");

Or, you can uncheck the "Treat field values as tagged text" box on the "Flat File" tab of the Data Source Wizard.

In FusionPro 6.0, the new TaggedTextFromRaw function supercedes the NormalizeEntities function and has more robust support for various characters which can cause problems for the tagged markup parser.

[MartyrSyx]

Dan,

For some reason I can't find documentation on the new features in 6.0 on how to use them. I don't know if I'm missing it or looking in the wrong place. Do you know where I can look?

[Dan Korn]

Originally Posted by MartyrSyx

For some reason I can't find documentation on the new features in 6.0 on how to use them. I don't know if I'm missing it or looking in the wrong place. Do you know where I can look?

http://forums.printable.com/showthread.php?t=329
http://forums.printable.com/showthread.php?t=324

[ppachen]

Thanks Dan! I was able to fix the issue with the ampersand. I'm unfortunately still running into issues with the carriage return. I tried both methods in v4.2P1d but neither seemed to work. The Preview Record Selector shows "<Missing>" for all fields after the offending carriage return. Ideally I would like the carriage return to be read so that each line in the single field is placed as a separate line in the output PDF.

Will an upgrade to v6 correct this problem (assuming I'm also using your suggested fixes)? Or does the carriage return inherently cause issues?

[Dan Korn]

Originally Posted by ppachen

Will an upgrade to v6 correct this problem (assuming I'm also using your suggested fixes)? Or does the carriage return inherently cause issues?

Yes, the carriage return inherently causes issues, and No, simply upgrading to 6.0 won't resolve them.

Since you mention Excel, I assume that you're dumping to a flat input data file, probably either comma-delimited (CSV) or tab-delimited. The problem is that in flat file data, a newline character denotes a new record, i.e. a new "line" of data, by definition. So newlines are not valid within field values in delimited data. (If you're using tagged markup input, then newlines are silently stripped out as whitespace and ignored.) Nothing about this has changed in FusionPro 6.0, and I can't foresee it changing in any subsequent version. Newlines are row delimiters, and there's really no getting around that.

This situation is further complicated on Windows by the fact that an embedded newline is actually composed of both a newline (ASCII 10, or "\n" in JavaScript) character and a carriage-return (ASCII 13, or "\r" in JavaScript) character, either of which will be treated as an end-of-line character (record delimiter) by FusionPro. Line-ending variations on different computing platforms have been a source of headaches for decades:
http://en.wikipedia.org/wiki/Newline#Common_problems

So, you'll need to somehow tell Excel to not output extra newlines or carriage returns to the flat data file. If you want to preserve them so that they actually cause line breaks in the FusionPro output, your best bet is to convert them to <br> tags. You'll need to use a VBA macro to do the conversion and make sure you get all of the newline and carriage-return characters. Here's a macro I found on Google and modified which should do what you need:

 Sub ReplaceCR()
    Dim ws As Worksheet
    For Each ws In Worksheets
        With ws.Cells
            .Replace vbCr, "<br>"
            .Replace vbLf, ""
            .Replace vbCrLf, ""
        End With
    Next
    MsgBox "Done"
End Sub

Again, this is an Excel VBA macro, not anything you can use in FusionPro directly. You can find more examples with a search like this:
http://google.com/search?q=excel+rep...urn%22+newline

Once you've converted these characters in Excel, you can export to CSV and then tell FusionPro to treat the field values as tagged text so that the <br> tags get processed properly.

But unfortunately, we're not done yet, because now we're back to the problems with the ampersands and such, since now those are treated as markup again. The way out of this dilemma is to modify the VBA macro above to output something else that you can replace with a <br> tag at composition time in FusionPro. So the Excel macro could look like this:

 Sub ReplaceCR()
    Dim ws As Worksheet
    For Each ws In Worksheets
        With ws.Cells
            .Replace vbCr, "***NEWLINE***"
            .Replace vbLf, ""
            .Replace vbCrLf, ""
        End With
    Next
    MsgBox "Done"
End Sub

Then you can do something like this in a rule:

return ReplaceSubstring(NormalizeEntities(Field("MyFieldName")), "***NEWLINE***", "<br>");

You'll need to do the replacement above for each field in the job. If you're using a version of FusionPro previous to 6.0, you'll have to create a set of rules like this, one rule for each data field, with the same name as the field.

If you're using FusionPro 6.0, you can do it all in one shot in OnRecordStart like so:

for (var i in FusionPro.Fields)
{
  FusionPro.Composition.AddVariable(i, ReplaceSubstring(TaggedTextFromRaw(Field(i)), "***NEWLINE***", "<br>"));
}

The underlying problem here is that, even though Excel is The World's Most Popular Database (TM), it's not really a database at all, and the step of exporting to a flat file doesn't handle embedded newlines well. That said, your other option, if you're on Windows, is to extract the data directly from Excel to FusionPro with ODBC instead of going through a CSV or a tab-delimited file.