Line breaks in merge data

[Amontillado]

I haven't been able to find an answer for this. In version 1, I could create a CSV file with line breaks to print envelopes with Affinity Publisher (fly, meet sledgehammer).

In version 2, or maybe with an option flipped, the merge data follows word wrapping. Instead of three lines in the address, I get one, word-wrapped inside the text box as if the merge data didn't include line breaks.

Is there an option to change how line breaks are processed in merge data?

Many thanks in advance.

[walt.farrell]

There is something odd going on here, in both Publisher and Publisher 2, so I suspect they fixed a V1 bug in V2, but maybe the fix broke something else.

Here's a screenshot of a record preview from V1. Note that in the Fields panel we see that the record has a multi-line Address field. Yet in the document page, we see only the first line. That seems wrong, to me.

Here's the same preview but in V2. Note that we see all 3 lines of the address. However, we see paragraph breaks between the lines, not line feeds:

So, we now see the complete preview in V2, so that's one bug fixed. But what should have been line breaks in the preview are paragraph breaks. That seems like a bug.

And what is finally generated when we run the Merge is this, which has neither the expected line breaks nor paragraph breaks:

Note that it also lost the comma after Anywhere. (Yes, I know this is a CSV file, but the multi-line address field is quoted (" surrounding it) which should "escape" the comma, and we can see in the preview and in the Fields panel that it did.)

So that's another bug.

Here's the CSV file, for others who want to work with this: Untitled 1.csv

[laurent32]

Multilines fields in csv file is a big subject…

Generally, in csv files, CR LF is used for end of line… Comma is the field separator… "" are used to delimit fields… and so there is confusion when it comes to multiline fields…

To deal with those multilines fields, Filemaker, for example, use the VT code ( = 0B = vertical tabulation)…

I feel your document Untitled 1.csv is strangely built, data isn't displayed in columns… like a classical csv file…

You have a 0D 0A (hex) after Address, 0D 0A (hex) after Address2, 0A after ["My street],  0A after [Apt 23], and 0D 0A after [Anywhere, USA"].

Where 0A = LF = line feed ; 0D 0A = CR LF = carriage return + line feed.

Because you have only one record in your file I don't know what code you would use as a line separator ? LF and CR are already used inside the record itself…

Here is the csv file with comma separated values, LF for multiline field, and CR LF as end of line :

Untitled2.csv

Note : CR LF as end of line is generally on Windows system ; LF as end of line is generally on macOS system (or Unix).

Concerning Affinity, I wonder what code is used to work with multiline fields ? LF (could be a problem for Mac files) ? VT ? Other ?

I also wonder if "" are used as field delimiters because there's the problem with the comma that disappeared as Walt noted…

[walt.farrell]

1 hour ago, laurent32 said:

I feel your document Untitled 1.csv is strangely built, data isn't displayed in columns… like a classical csv file…

It has only 1 column. Thanks for pointing out the 0D/0A issue. I had missed that.

[laurent32]

2 hours ago, walt.farrell said:

It has only 1 column

I think it should be one row ?

[walt.farrell]

1 hour ago, laurent32 said:

I think it should be one row ?

I think it has 2 rows, one column each. But that might have been an earlier version.

My point was that the data is displayed in columns, but there's only one of them.

[Seikilos]

Just to follow up: The behaviour is not fixed, right?

Because in latest Apub 2 the behaviour, that @walt.farrell mentioned, is still there. Newlines in the merged output seem to be filtered

[walt.farrell]

Serif have not updated either of the topics that has a bug tag for this to mark them as fixed, so it probably isn't: 

https://forum.affinity.serif.com/index.php?/search/&tags=AFB-7327

[Amontillado]

This has not been fixed, but there is a reasonably easy workaround.

Open your csv file in a spreadsheet app. Replace every occurrence of newline with something recognizable. I like ¶.

Merge the data into Affinity. In Affinity, replace every occurrence of ¶ with newline. Note that in the Affinity replace field, newlines appear as ¶ characters. No biggie, it works.

For more of a one-click-fix for the csv file, use BBEdit on Mac with a saved text factory (like a macro) to make the change.

On either Windows or Mac, the extremely wonderful Easy Data Transform can save transformations. Load a csv file, apply the pilcrow transform, and save it.

I don't have anything to do with Easy Data Transform. If you do much with merge data you may find it a good companion for other reasons.

For instance, say you've got customer data including name and address in an Excel file, a csv export from your accounting system showing a log of individual sales, and a JSON dump from a tracking system that relates customers to industrial categorizations. EDT can do joins across different source formats. You could extract the customers from the Excel file with sales totals in the csv file beyond some threshold that appear in targeted industries in the JSON dump, writing your distilled data to csv (or any of several formats).

Good luck with your merges!

Edited June 4, 2023 by Amontillado
Forgot a detail.

[Seikilos]

3 hours ago, Amontillado said:

This has not been fixed, but there is a reasonably easy workaround.

Open your csv file in a spreadsheet app. Replace every occurrence of newline with something recognizable. I like ¶.

Merge the data into Affinity. In Affinity, replace every occurrence of ¶ with newline. Note that in the Affinity replace field, newlines appear as ¶ characters. No biggie, it works.

For more of a one-click-fix for the csv file, use BBEdit on Mac with a saved text factory (like a macro) to make the change.

On either Windows or Mac, the extremely wonderful Easy Data Transform can save transformations. Load a csv file, apply the pilcrow transform, and save it.

I don't have anything to do with Easy Data Transform. If you do much with merge data you may find it a good companion for other reasons.

For instance, say you've got customer data including name and address in an Excel file, a csv export from your accounting system showing a log of individual sales, and a JSON dump from a tracking system that relates customers to industrial categorizations. EDT can do joins across different source formats. You could extract the customers from the Excel file with sales totals in the csv file beyond some threshold that appear in targeted industries in the JSON dump, writing your distilled data to csv (or any of several formats).

Good luck with your merges!

Nice hint. Haven't thought about replacing placeholders afterwords. Makes it a little annoying to use but would be a valid solution indeed.

Thanks for the idea! 👍

[affineuser]

Just to bump the issue:

I just tried the Data Merge functionality in Publisher 2 - which I really like - and stumbled upon the same issue in 2.3.0. Linebreaks within fields are converted to spaces.

Has Serif some sort of upvoting a feature request or bug?

[Dan C]

Thanks for letting us know @affineuser & apologies for the delayed response over the holidays.

On 12/18/2023 at 1:46 PM, affineuser said:

Has Serif some sort of upvoting a feature request or bug?

I can confirm that I have 'bumped' this issue with our development team using your report, to bring it to their attention once again.

I hope this helps

[PublicBenemy]

On 1/2/2024 at 10:29 AM, Dan C said:

[…] I can confirm that I have 'bumped' this issue with our development team using your report, to bring it to their attention once again. […]

Highly appreciated, thank you very much! While at it, maybe you can also have a look at this issue. I have an excel file with a date column and a text column

A4 is the value of "04.03.2024", displayed as custom date format TTTT, T. MMMM JJJJ

B4 is the formula =TEXT(A4;"TTTT T. MMMM JJJJ")

In my Publisher 2 document, I put in both place holders

The two columns behave differently. For the first one, there is a "/" character instead of the "." character.
The second column is displayed correctly.

It's a German excel version of Office 365.

Edited January 28 by PublicBenemy

[Komatös]

1 hour ago, PublicBenemy said:

While at it, maybe you can also have a look at this issue. I have an excel file with a date column and a text column

Hallo und willkommen im Forum.

Kannst Du uns bitte die Excel-Datei bereitstellen, damit wir schauen können, wo der Fehler sein kann?  

[PublicBenemy]

22 hours ago, Komatös said:

Kannst Du uns bitte die Excel-Datei bereitstellen, damit wir schauen können, wo der Fehler sein kann?  

Ich versuch sie mal hier reinzuhängen, ich hoffe, es klappt.

Speisenliste_4Serif.xlsx

[Komatös]

42 minutes ago, PublicBenemy said:

ich hoffe, es klappt.

Hat funktioniert. Es ist der Punkt, der von Publisher falsch ausgelesen wird. Warum auch immer.

Nutze als alternatives Zellenformat nachfolgende Formatierunsvorlage

[$-de-DE]TTTT T. MMMM JJJJ

 

@Dan C FYI It is the full stop that Publisher reads incorrectly from the Excel spreadsheet.

 

[PublicBenemy]

13 hours ago, Komatös said:

[$-de-DE]TTTT T. MMMM JJJJ

Works like a charm! Danke!

[mondze]

Update:
Have the same issue, Publisher 2.5.3 no breaks implanted

[walt.farrell]

42 minutes ago, mondze said:

Update:
Have the same issue, Publisher 2.5.3 no breaks implanted

As the Affinity Info Bot has not posted to say the issue is fixed, that's not surprising.

[spicemerchant]

Just want to confirm our deep need for this feature. It did not occur to me that newlines would not be handled well... I should've tested, but we've now got packaging labels designed in Publisher 2 and there are a lot of line breaks we need to deal with. The find/replace of a special character, seems to work, but boy does it make me nervous, and adds a step which can lead to error.

[Mahlon Stacy]

I need this fixed, too. I have a 36 page program that has many merged entries with line breaks.

[Callum]

On 10/1/2024 at 1:18 PM, Mahlon Stacy said:

I need this fixed, too. I have a 36 page program that has many merged entries with line breaks.

Hi Mahlon Stacy welcome to the forums,

This is something that is still being looked into by our dev team it should be fixed in one of our future updates, I apologise for any inconvenience in the meantime.

Thanks
C