Data Merge

[Dai555]

How do I go about using an xlsx data source using more than one sheet as a data source in one document?

I'm creating a brochure as test document. and I want to use four sheets in an xlsx as my data source.
I've created the first page and data works pulling data from sheet 1.
But how do I pull Data from sheet 2 on to another page?
Have I got to repeat the data source every time I want to use a different sheet on another page in my document?

Every time I change the data source as new sheet in the new page the old page adopts that sheet name/number of the new sheet.
for instance first sheet is called rings and works OK on the first page of the document. SeNecklace instead of cond page of document I want to use same Data Source but the Necklace sheet.
As soon as I change the Data Source in the Data Merge manager to reflect the different sheet of the xslx Data the first page adopts the new sheet. Both DataMerge grids show  Necklace instead of one rings and the other Necklace.

So do I load the same Data Source into the Data Manager twice to use one sheet for one page and another sheet for the other page?

[walt.farrell]

19 minutes ago, Dai555 said:

Have I got to repeat the data source every time I want to use a different sheet on another page in my document?

 

20 minutes ago, Dai555 said:

So do I load the same Data Source into the Data Manager twice to use one sheet for one page and another sheet for the other page?

Yes.

[Dai555]

Cheers @walt.farrell

 

[David in Яuislip]

Try this

[Dai555]

Done all the updates still not having any joy with the second sheet. If you would take a look and tell me what I'm doing wrong it would be appreciated.

Brochure.7z

[walt.farrell]

Just a guess, but you may need to change the labels so the fields on each Sheet of your xlsx file have unique names.

[Dai555]

11 hours ago, walt.farrell said:

Just a guess, but you may need to change the labels so the fields on each Sheet of your xlsx file have unique names.

I think I get what you mean. But this is suppose to resemble a catalogue style brochure. Imagine doing the argos catalogue with unique names. The mind boggles.

@walt.farrell cheers!

[Dai555]

Is this what you mean Walt if so still not working.

Edited August 26, 2023 by Dai555

[anto]

Reorganize your data in the spreadsheet in this way and you will get a single sheet data source. Then you won't have any problems.

Or, if you're merging page 2, use the Page Range from 2 to 2. When merging page 5, update the source from the top (select Neclace sheet) and select Page Range from 5 to 5.

[Catshill]

Would this work?

Import data set 1 into APu doc1

Import data set 2 into APu doc2

Create APu master document and place APu doc1 and APu doc2 into relevant pages.

[thomaso]

A second sheet appears to be similar to a second source. This recent thread about multiple sources may clarify the limits or possibilities. In short (combine @Walt's + @anto's hints): if you merge (copy/paste) the various sheets + use unique field names in the source file then it should work with the content of both sheets.

On 8/17/2023 at 1:28 PM, NathanC said:

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 (…) and will only use the first added data source

[walt.farrell]

8 hours ago, Dai555 said:

Is this what you mean Walt if so still not working.

Yes, I think that's what I was suggesting.

[Dai555]

So the limitation is label names. from different sources makes a clash in the program rendering it useless for things that have repetitive data labels such as a catalogue. To achieve it only a single sheet from an xslx can be used. I can't see how you can have unique names for things like Item, price,reference, By the time you get to the third or fourth sheet Labels would start being totally unrecognizable. Defeating the object of having well organised Data. 

I do hope Affinity are going to improve the Data Merge in the future. It makes more sense to do what I want to do in the way I want to do it than have a spaghetti Data source just to get the thing to work for certain applications. Rant over. Thanks for the help lads it's been very helpful.

[thomaso]

2 hours ago, Dai555 said:

I can't see how you can have unique names for things like Item, price,reference, By the time you get to the third or fourth sheet Labels would start being totally unrecognizable.

Workaround: Spilt the table view to keep the label row visible on top, independently of the row position of selected cells.

[anto]

3 hours ago, Dai555 said:

So the limitation is label names

Seems to no.

  

3 hours ago, Dai555 said:

To achieve it only a single sheet from an xslx can be used.

No.

Look at video.

It works. Organize your data in way like in pictures.

 

[Dai555]

@anto shall give it a try and see how it goes. Cheers for the input.

 

Got it working not sure if it's unique names and explicit locations in pages in Data Merge which is working, I suspect both.
Plus the master Page had to be detached as it didn't  reproduce properly after Data Merge.

Edited August 28, 2023 by Dai555