Jump to content
You must now use your email address to sign in [click for more info] ×

[Implemented] Data merge


Recommended Posts

I have done a prelim test of the data merge.  I can honestly say that the functionality exceeds my expectations.  ☺️ ☺️  I would like to congratulate all involved.

I can see some refinements I need to make with the document I am using as the template for my initial test data merge, but suspect these are specific formatting tricks I need to learn within the basic Publisher App.  I did not try Publisher until I knew data merge was a feature.  I will post a separate query if I cannot find the answer myself, so as not to confuse this thread with a separate query.

I have written my own JavaScript which allows me do sophisticated data merges with InDesign and Photoshop documents. However, the Adobe mail Merge Tools are clunky and difficult to use (and have built in gottchas)  and I hate maintaining this functionality myself.   The functionality I have tested in Affinity Publisher allows me to achieve 90% of what my custom JavaScript does.   I am hoping I will figure out how to do the remaining 10% and will post here if I do.

Very clever to be able to have multiple source data docs.  Very clever to be able to place the data merge into different places.   The architects of this knew what they were doing and did an exceedingly good job. I am seriously impressed.

My best regards. 

Link to comment
Share on other sites

Hi, 
implemented Data merge definitely works for me. Everything seems infuitive and easily adjustable. There is however one very critical flaw, which prevents serious use of this software. If I try to create larger product catalogue (70+ pages/30 images/page), it fails on limit of opened files - running PC with Windows 10. As soon as this is solved, I can see Publisher as a Indesign replacement. 

Best regards ...

 

Link to comment
Share on other sites

I had a quick test of the data merge. It does work similar to the competition, with few exceptions - skip blank lines.

But the UI for the function is so bad. There's one panel to load the csv files, then another panel for fields in completely separate section - "Fields" with all document statistics?

No indication if the field is already placed on a page, nor how many times.

The layout tool is a nice idea but nowhere in a help file does it say how to use it. I accidentally spotted a screenshot with layer panel showing that repeated text boxes need to be dragged inside/ under the data merge layer. Also if you select the grid with arrow, no options are visible - you have to pick data merge tool and select your grid again.

I hope this is just a v.1 for this and it will be improved in future versions.

Link to comment
Share on other sites

I'm extremely happy that Affinity has implemented the Data Merge feature. I think it is still early days and they have to clean up a few things. I agree with others that having one panel for data merge and another panel for fields is kind of a strange way to do things......having them on the same panel under different tabs might be better.
I also think it would be extremely useful if you could simply put some type of tag or identifying for the individual fields straight into a textbox for example rather than having to load the database, go to field panel, select the textbox in your document and then double click the associated field in the fields panel to link them. I haven't played around with it but what happens if you change to a different database but with the exact same fields....do you have to repeat the double click process? or can you just save the publisher doc like a template file the you can reopen (let's say you are doing monthly reports) month after month, open the new monthly database with the same fields and then just click "generate" to populate the fields in the document.

Link to comment
Share on other sites

21 hours ago, Dean Reardon said:

I haven't played around with it but what happens if you change to a different database but with the exact same fields....do you have to repeat the double click process? or can you just save the publisher doc like a template file the you can reopen (let's say you are doing monthly reports) month after month, open the new monthly database with the same fields and then just click "generate" to populate the fields in the document.

Quick check, short answer yes. Same field names in csv file with same filename seems fine to update the data.

Mac Pro (Late 2013) Mac OS 12.7.4 
Affinity Designer 2.4.0 | Affinity Photo 2.4.0 | Affinity Publisher 2.4.0 | Beta versions as they appear.

I have never mastered color management, period, so I cannot help with that.

Link to comment
Share on other sites

Have finally gotten around too checking out the data merge function in Publisher. Using the just the basics it works well. I would like to see some refinement to the UI. I do not like the Data Merge Manager window always hovering, this should be able to be looked to the toolbar on the right. A big feature I think is missing (may be there but I am not sure where) is the ability to export a merge directly to PDF rather than generate a merged document with all the graphical elements and then export again. Should be able to cut out that middle step.  I tried a simple merge of around 11,000 records. Speed is good and seems to be about the same as Indesign doing the same thing. Data merge along with PDF passthrough are big steps and massive improvements that make this much more usable in a pro environment. I may try some simple live jobs when I have the time and see how things go and compare. 

Link to comment
Share on other sites

  • 3 weeks later...

Tried the data merge. Setup is simple and it works. Two requests:

1 . Often I use data merge to create multiple same files, but with different names and values. So, eg. I have a course invitation with login credentials. So I end up with 20 pages for 20 students but in one document. I would love to see the option to spit out 20 different PDF's at once with also dynamic names (using field values).

2. If this is not yet possible, then I would like the option to place an Anchor in the text using the field values from the data merge <name student> . I tried this, but the field does not get generated. Why is this important? When after generating I end up with one PDF. I open this PDF in adobe acrobat and use the SPLIT function there. In this split function there is an option to use the top level bookmark on each page as the name of the output PDF. Wonderful function: you end up with 20 PDF's each with the name of the student and therefore easy to distribute.

Link to comment
Share on other sites

  • 2 weeks later...

I'm just starting out with Publisher, and I was very interested in the Data Merge feature. For the most part it works well, but I think there's room for improvement with numbered/bulleted multi-line text.

I tried to make it work 3 ways, with mixed results:

  1. JSON field as array of strings (2nd level): I used a JSON file as a source in the following format:
    [{
        "field1": "Field Value",
        "field2": ["item 1", "item2"]
    }]

    Here, field 1 is recognized, field 2 isn't, so it doesn't work for me.
  2. Excel file with line breaks in the cell. The fields were recognized, but there was no text in the field. Didn't work either.
  3. JSON field with list of fields separated by newline character(\n). I used a JSON file with the following format:
    [{
        "field1": "Field Value",
        "field2": ["item 1\nitem2"]
    }]
    Here, both fields get recognized. This option is the closest I got to what I need, but I think it would be cumbersome to have to replace all line breaks with the \n character. Still, numbering/bullets don't work as expected (meaning, the numbers/bullets don't appear on each new line/item, only on the first line) because the "list" is one field. Of course, I could add the numbers/bullets myself, but I can't do any formatting.

I think it would be fantastic if #1 worked. That is, that arrays of strings are recognized as one field with multiple items so that I can apply bullets/numbering and customize it on the master page, rather than at the data source.

 

 

Link to comment
Share on other sites

  • 2 weeks later...

Coming from a web development background, JSON arrays were also the firs thing I tried with data merge and like Plasma above I was rather disappointed to find that nesting of any level and more complex objects with nested properties didn't work. CSV was fine, and my jsons in this case were easily flattened to something it could parse, but I feel like the merge could be a lot more powerful if it could actually use the full power of JSON as opposed to treating it like another CSV row.

Not a fan of the number of clicks required and multiple menus I need floating around for it, but it's perfectly useable and once you figure out the particular way Publisher wants you to use it I've found it works well enough for me.

Link to comment
Share on other sites

Hyperlink in data merge, please. I have an request to have the possibility to add an hyperlink to an image or text. This would make data merge useful for e-books and interactive use. So please add data-merge HYPERLINK. 

Link to comment
Share on other sites

  • 1 month later...
9 minutes ago, RGBear said:

+1 Data Merge

As per the ‘Recommended’ post at the top of this page, data merge is now available in Affinity Publisher. To what does your ‘+1’ refer? :/

Alfred spacer.png
Affinity Designer/Photo/Publisher 2 for Windows • Windows 10 Home/Pro
Affinity Designer/Photo/Publisher 2 for iPad • iPadOS 17.4.1 (iPad 7th gen)

Link to comment
Share on other sites

3 minutes ago, PaRunk said:

I would be really happy if you can use XML and JSON data in the data summary,

I'm not sure what you mean by "in the data summary".

-- Walt
Designer, Photo, and Publisher V1 and V2 at latest retail and beta releases
PC:
    Desktop:  Windows 11 Pro, version 23H2, 64GB memory, AMD Ryzen 9 5900 12-Core @ 3.00 GHz, NVIDIA GeForce RTX 3090 

    Laptop:  Windows 11 Pro, version 23H2, 32GB memory, Intel Core i7-10750H @ 2.60GHz, Intel UHD Graphics Comet Lake GT2 and NVIDIA GeForce RTX 3070 Laptop GPU.
iPad:  iPad Pro M1, 12.9": iPadOS 17.4.1, Apple Pencil 2, Magic Keyboard 
Mac:  2023 M2 MacBook Air 15", 16GB memory, macOS Sonoma 14.4.1

Link to comment
Share on other sites

5 minutes ago, PaRunk said:

I meant "I would be really happy if you can use XML and JSON data in the Data Merge".

Thanks. You can already use JSON.

Help: https://affinity.help/publisher/English.lproj/pages/Advanced/dataMerge.html

-- Walt
Designer, Photo, and Publisher V1 and V2 at latest retail and beta releases
PC:
    Desktop:  Windows 11 Pro, version 23H2, 64GB memory, AMD Ryzen 9 5900 12-Core @ 3.00 GHz, NVIDIA GeForce RTX 3090 

    Laptop:  Windows 11 Pro, version 23H2, 32GB memory, Intel Core i7-10750H @ 2.60GHz, Intel UHD Graphics Comet Lake GT2 and NVIDIA GeForce RTX 3070 Laptop GPU.
iPad:  iPad Pro M1, 12.9": iPadOS 17.4.1, Apple Pencil 2, Magic Keyboard 
Mac:  2023 M2 MacBook Air 15", 16GB memory, macOS Sonoma 14.4.1

Link to comment
Share on other sites

  • 1 month later...

I'm sorry, but I can't understand the need for the Data Merge Layout Tool??? Why I can't just run line after line in paragraphs, without this tool?
Let's say I have events. I have fixed dates, like time, price, but I also have floating text which is always different in length, and some dates do not exist. So how do I know how much data will fit on a page with flexible data? That does not work, or I have blank space between events, or overflow in-between events. The Data Merge Layout Tool handicaps.

Thinking outside the box: A flexible way to work is with InData / Xdata from Em Software. You work in a text frame, set the character of the columns a, b, c, d, and so on. You do your paragraph and character styles, and the layout is ready for going. By Generate the pages will built up fully formatted with your styles, page by page. Spacing is done by paragraph styles. A simple example how cool Em Software it does:

«if a is not empty»«a»
«endif»«if b is not empty»«b»«endif»
«if c is not empty»Overview «c»
«endif»«if d is not empty»«d»
«endif»«if e is not empty»Date «e»
«endif»«if f is not empty»Location «f»
«endif»

A lot of text for a simple wish:
Please enable "Generate" in continuous text, paragraph by paragraph – without the Data Merge Layout Tool!
PS: If or else would also be cool for Data Merge.

Link to comment
Share on other sites

On 6/23/2021 at 5:42 PM, tersmuse said:

PS: If or else would also be cool for Data Merge.

Yes this should be great, but sadly not yet available.

We use the Data Merge also and yes the missing "if and / or then x else y" part is bad but we added our Excel-Table that those Parts can be processed in Excel.

I assume that this will be also added later, but hey when I look to Adobe... Well it has taken them much longer to get the Merge Function running. Before that is was a simple "copy Excel to Indesign with no Link" Feature. 

Link to comment
Share on other sites

  • 4 weeks later...

Hi all,

I'm an Affinity Publisher newbie, playing around with the data merger. Not sure if this is a bug / feature or if I just didn't get it.

I'm trying to insert values from two different JSON / CSV files, but it doesn't work.

I'm trying out a proof of concept that shall later generate a report, where my publisher document needs to replace some dynamic parts of a textbox.

Due to the fact that - as far as I know - scripting is not supported in Publisher, a python script I wrote creates two json files which look like that:

json1.json:

[
{"Hello":"World","Hi":"Mars"}
]

json2.json:

[
{"Hello":"World","Hi":"Mars"}
]

Now I open the Data Merge Manager and add the two files as data sources.

Using the settings shown in "1_data_merge.JPG", I can double-click my text-box and from the fields tab I can insert values by double-clicking the data merge field . Luckily this works for every textbox in my document.

1_data_merge.JPG.2d7c2481eb4896aa1fe06346622d822c.JPG

2_data_merge.JPG.75f9073761d76623f70e536c605aec60.JPG

 

But when I double-click data-fields from the second file in the Data Merge Manager this does not work. The field "Merge Enabled" is not checked there by default (see file 3_data_merge.jpg). I'm not a native speaker, but as far as I could find out by ready the documentation, this field is used to select data from several sources[1].

3_data_merge.JPG.eba686f2e7bac8499f82b785999808a5.JPG

When I just enable the checkbox, nothing changes and adding fields from the second file into a textbox is still not possible (no error is shown).

When I disable the "Merge Enabled" field at the json1.json file, some really strange things happen. At first, the fields from the file json2.json, that were not inserted before, appear right now without me doing anything. Inserting values from the json2.json file is now possible. But when I just click on a textbox that includes values from the json1.json file, they just disappear.

So I would just like to know: Do I have to program my python script in a way, that every part of the dynamic text is stored in a single json file or is it possible to insert fields from various data-sources at one? Or is there any other workaround to change dynamic values at several different parts of my document (thise that were spit out by my python script) without importing each and every changed number / part manually?

[1] https://affinity.help/publisher/en-US.lproj/index.html?page=pages/GetStarted/importDocument.html?title=Importing documents --> "The Data Merge Manager also lets you disable merging (uncheck Merge Enabled) to allow switching between multiple data sources. You can also control the number of pages generated on merging."

BR,

JDO

OS: Win 10
Publisher: 1.92.1035

Link to comment
Share on other sites

Hi mate!

I had the same confusion.

Like moderator Gabe answers me,

 

Hi @Jose Alvarez,

Sorry for the delayed reply. 

 You may be confused by the fact you can only preview one data source at once and previewing one will hide the other. If you turn off all previews you will see the field placeholders. The preview works that way because that's what will happen for the output. You can only merge a page with one source at once. Merging 2 different sources is not an option. 

Maybe the window should only show the file chosen to merge.

Link to comment
Share on other sites

  • Staff

The multiple sources feature is designed for switching between alternatives (eg a small test and larger production data source) or for merging different page ranges in a document with different data sources (eg a catalog where each chapter has a different template page and a different data source file).

It's not currently possible to use more than one data source to merge a single output page, I'm not convinced that's desirable, it certainly would create a lot of complications regarding advancing the merge index and running out of records.

@jdo if you could explain your use-case more fully perhaps I can suggest a way to make it work.

Link to comment
Share on other sites

34 minutes ago, Mark Daniel said:

The multiple sources feature is designed for switching between alternatives (eg a small test and larger production data source) or for merging different page ranges in a document with different data sources (eg a catalog where each chapter has a different template page and a different data source file).

It's not currently possible to use more than one data source to merge a single output page, I'm not convinced that's desirable, it certainly would create a lot of complications regarding advancing the merge index and running out of records.

@jdo if you could explain your use-case more fully perhaps I can suggest a way to make it work.

Hi Mark!

Good explanation, the best i have seen until now, about why we can see multiple sources at Fields window.

Thanks!

Link to comment
Share on other sites

Once again to the Data Merge Layout Tool. I try again with a screenshot:

  1. If or else would skip these blank lines
  2. blank space between items due to fixed size
  3. inflexible fixed sizes

spacer.png

This is how it should look reasonable if you don't have to use the Data Merge Layout Tool. Simple with character and paragraph styles:

spacer.png

Link to comment
Share on other sites

If you could explain your complications, maybe I could help. 
I've done it in TeX with either Python or Go and was quite surprised it didn't work in Affinity. Especially as I'm not a programmer by trade and managed to get it done anyway.

If it helps, these are the options I use for mismatched array lengths:

  1. As is (98% of the time): Let all arrays run their course, what goes into next pages, goes into next pages. What ends, ends.
    1. Example 1: I have nested arrays that just contain pre-sorted file links (like a gallery or chapter files) these are read to finish into their template position within the main template before it advances.
    2. Example 2: Sidebar content has more separate items than the main content area. If it overshoots I just place a limiter in or re-write the content.
  2. Lead: I'm reading the number of array items of the lead file and use that as the array lengths for all the others. It's a rare use case but it's useful if you have a scaffold layout and need to match hard-breaks. Start / End items are cascaded through.

Also I'm targeting the outputs file-unique (using named aliases because I'm lazy) instead of reading all fields globally. It avoids a lot of headaches and keeps you from creating dead-end solutions. 

 

4 hours ago, Mark Daniel said:

It's not currently possible to use more than one data source to merge a single output page, I'm not convinced that's desirable, it certainly would create a lot of complications regarding advancing the merge index and running out of records.

 

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.