Data Merge References The Incorrect Data Source When Using Multiple Excel Data Merge Source Files

[Hangman]

It is not possible to add placeholders from more than one data source for the purpose of a Data Merge.

Pre-flight throws up an error which indicates the data-merge field is being sought from the wrong Excel file.

Links back to this thread

[thomaso]

51 minutes ago, Hangman said:

not an issue when using multiple .csv files

What entry do you get if you double-click the "Source" field in the default "Data Merge" with two connected .csv files?

To me in V1 this default "Data Merge" seems to know / handle only 1 source at a time even with two .csv files (no .xlsx at all). If I switch/add/remove a source in the Data Merge Manager dialog then all already entered field "Source" entries get updated and show the changed source instead, regardless of their former display. While preflight reacts with confusion, missing sometimes this, sometimes that source (respectively the field in that source file).

[Hangman]

1 hour ago, thomaso said:

To me in V1 this default "Data Merge" seems to know / handle only 1 source at a time even with two .csv files (no .xlsx at all).

It turns out that if both data sources use the same headers then you can add from multiple data sources (as in double-click to add the placeholder), both .xlsx and .csv, however, regardless the data is only coming from the first added file not both...

[thomaso]

40 minutes ago, Hangman said:

if both data sources use the same headers then you can add from multiple data sources (as in double-click to add the placeholder), both .xlsx and .csv, however, regardless the data is only coming from the first added file not both

I experience it different (still in V1): If I add fields of different sources than they all get generated but one after the other while the total number of results gets added up. So for instance if I place the "Source" field then the generated result contains the file names of all connected source files sequentially, even if no other field than just 1x "Source" was placed in the layout.

[Hangman]

2 hours ago, thomaso said:

I experience it different (still in V1): If I add fields of different sources than they all get generated but one after the other while the total number of results gets added up. So for instance if I place the "Source" field then the generated result contains the file names of all connected source files sequentially, even if no other field than just 1x "Source" was placed in the layout.

Can you upload a quick screen recording so I can check in v2 that I'm following the same steps...

[thomaso]

8 hours ago, Hangman said:

Can you upload a quick screen recording so I can check in v2 that I'm following the same steps...

I prefer to upload my V1 .afpub including its setup and source files which you could open in V2. data merge multiple sources V1.zip

Note the three 'empty' lines (pink), they contain data but display them in the generated result only. I wonder if they appear in V2 in the layout with their content.

The used fields are marked in green and added in this order from top to bottom. The Picture Frame has the URL field assigned (which works to me only for the JPG in the attached folder, but not for the two linked URLs. – Also interesting: In the results the field "Long Date" appears twice … but twice for both sources.

[NathanC]

Hi @Hangman,

Going back to your original post, I've discussed this with QA and it looks like it's working as expected, data merge manager does allow you to add multiple source documents for the purpose of using different source files but this is essentially limited to a single data source per page/page range when merging. If you have 'Merge enabled' on both source files with the range set to 'All pages' and attempt to use fields from both sources on the same text frame concurrently, this creates a conflict (as demonstrated by the preflight errors showing confusion with missing fields) and will only use the first added data source in the final Generated Merge/Record Preview if you're trying to use both fields in the same text frame.

If you have Merge enabled and preview with record ticked, you can alternate between previewing the records on the same page and text frame by unticking/ticking 'merge enabled' on either source like in the example recording below. Edit: Or alternate toggling 'Preview with record'.

 If you have two separate text frames on a single page with merge enabled, it is possible to use fields/headers from separate sources so long as the fields/headers exist in separate frames, when you generate the merge it will generate the pages for the for the first data source first, and then the second lot of pages for the second source separately, it's not able to use two separate sources on the same page in the final document, as this is not currently supported.

The ability to use multiple sources concurrently on the same page has been requested before back in V1 (Example thread below) but some users have pointed out some current pitfalls of implementing this.

 

[Hangman]

Hi @NathanC,

Many thanks for taking the time to look at this, it all makes perfect sense...

Clearly Publisher would require relational database support using a key for cross referencing (as already talked about in the linked post) for this to work and maintain data integrity, something I can't see being added to Publisher any time soon, if at all...

1 hour ago, NathanC said:

If you have two separate text frames on a single page with merge enabled, it is possible to use fields/headers from separate sources so long as the fields/headers exist in separate frames, when you generate the merge it will generate the pages for the for the first data source first, and then the second lot of pages for the second source separately, it's not able to use two separate sources on the same page in the final document, as this is not currently supported.

I'm assuming this isn't designed to be automatic, as in, you have to chop and change which data source has merge enabled and generate a new merged file for each manually?

Trying this with three data sources in three separate frames on a single page with all three data sources set to merge only generates the merge with the first data set (as you mention), so the three data sets need to be set to merge independently and three pages generated manually which I assume is the expected behaviour here.

Again, as you say, when the three data merge frames appear on different pages, you can use one data source per page successfully and generate three pages with the respective data from each data source in a single pass.

[NathanC]

18 minutes ago, Hangman said:

I'm assuming this isn't designed to be automatic, as in, you have to chop and change which data source has merge enabled and generate a new merged file for each manually?

In my example say I have two separate data merge sources both containing three headers and three records and two separate text frames on a singular page, If I enable 'Merge enabled' and assign the headers into their two separate respective frames, when I generate the merged document Pages 1-3 will contain only data from the first merge source with the second text frame being completely empty and pages 4-6 only contain data from the second merged source and the first text frame is completely empty.

Since they haven't merged on the same initial three pages in my opinion this is pretty redundant, and you might as well just Chop and change the data sources out as you've mentioned.

Unless I've misinterpreted and you mean something different?

[Hangman]

Hi @NathanC,

I'm not seeing that on macOS, when I replicate I'm only ever seeing a single set of data after the data merge, i.e., the first data set, am I doing something differently to you here?

I have tried this two ways:

• A Single Excel file containing three worksheets using both identical and non-identical headers loaded three times selecting the respective worksheet from the Data Merge Manager
   
• Three separate Excel files each containing a single worksheet, again using both identical and non-identical headers

I've attached both of my test files if it helps...

Data Merge Multi File Data Source.zip

Data Merge Single File Data Source.zip

[NathanC]

Thanks for the files and follow up, It looks like your three source headers were placed into data merge layouts on the same page, which it looks like they don't behave in the same way as standard text frames do when considering multiple data sources on a single page, if you move the nested text frames out of the layouts and then delete the layouts and generate it again, you'll end up with 18 pages with all three data sources used similar to my earlier example.

I'll check internally whether or not this is intentional.

[Hangman]

Hi @NathanC,

Could you perhaps upload the sample file you showed in your screen recording so I can see how you've set it up?

I've just tried your suggestion of moving the nested text frames out of the layouts and then deleting the layouts and generating them again, but (for me) it makes no difference, I still only end up with three pages when doing the data merge...

Out of interest, do you see the same result on Mac as shown in your screen recording?

I'm clearly doing something wrong here, I just can't quite figure out what at the moment...

[NathanC]

No problem, I've used your files from earlier and re-formatted them to no longer use data merge layouts, saved them and also saved the generated result, I did run the tests on MacOS just to be sure.

Data Merge Multi File Data Source Modified.zipData Merge Single File Data Source Modified.zip

[Hangman]

Hi @NathanC,

Many thanks for the updated files...

I see what you've done now and what you said earlier makes sense, so basically the issue is that you can't use a Data Merge frame with multiple data sources on the same page, as in, if you wanted to produce say business cards eight up on a page with the data coming from multiple data sources, this isn't possible.

Do you anticipate this something that is ever likely to change do you think, obviously the solution is to either combine the data into a single data source or even reference the other data sources in the main data source file though I can see instances where personal identifying information data is kept in separate files for data security purposes.

It would of course provide a lot more flexibility if multiple data sources could be combined on a single page with the proviso of using a relational database style key to ensure data integrity but I suspect that is very much outside the scope of Publisher at least for the foreseeable future...

Thanks again for taking the time to explain this, very much appreciated...

[NathanC]

As far as I'm aware there isn't any current plans for changes to data merge's current functionality in regard to multiple data sources but since there isn't an up to date feature request it may be worth creating a new post for visibility. 🙂

[Hangman]

Hi @NathanC,

Thanks, I will certainly add a feature request as I can see this being a useful feature, though maybe it's too niché I don't know.