Jump to content

Data Character Issues


ppachen

Recommended Posts

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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+replace+%22carriage+return%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 , 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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...