InDesign's Data Merge pulls in data row by row - there is no way to 'group' items as you would like. You will need to add the correct data to each row, even if it repeats some of the previous row.
From a Data stand-point, this is common practice anyway as what you have listed above are effectively 2 separate SKU numbers (ITEM). If you were to ever use this data as a database (maybe that's where it came from?) you would expect to see a NAME next to a SKU - it would not be clear they are 'grouped' items.
Repeating the data will be an issue, as there are 1,000s of products, some come in 1 weigh, others in many. So Data Merge might not be the best tool to use here. If you or someone else can recommend another way to do this - would be great. Don't feel like doing all this manually and trying to avoid paying for a plugin if I can.
I know it is grunt work and you will feel like gouging your eyes out afterwards, but correcting the data file in Excel via copy the Name field, drag down to where it stops to fill in the blank Name fields, do it again (and again) is pretty quick in the larger scheme of things. I corrected many large Excel files before merges. But I also charged the clients for it.
It is always best to get them to correct their files, of course.
Best of luck to ya. Mike
Can you describe what you are trying to achieve? The main issue I see is not with InDesign or the Data Merge function, it's with the data itself.
I see you are making a catalogue. For example, are you trying to show one image of Orange Juice, but have ALL the ITEM numbers and sizes under that one image? If so, you will still need to format the data correctly but you could create those into one row of the data sheet. As an example, you could have the columns be: NAME, ITEM 1, WEIGHT 1, ITEM 2, WEIGHT 2, ITEM 3, WEIGHT 3, CAS, IMAGE. Then you would just position those fields correctly on the InDesign page.
Hi guys, thank you for the replies.
To make things a little easier to understand, I've made a couple of screenshots.
The problem is probably in the data itself, which I can get the client to fix up to how I need it, but right now I'm trying to figure out what is the best way. I've played around with merging tables, etc - doesn't work.
So here is an example of the data:
And here is what it is meant to come out as:
If it was just one product per heading - no problem. But they vary, which makes things a bit complicated.
I don't think Data Merge is sophisticated enough to do this.
Possibly xml, a commercial catalog plugin, or scripting.
Well, maybe I shouldn't say that. If you have enough columns to cover the maximum number of procducts you can add placeholders for each column and set Data Merge to skip blank lines (but it looks to me like you'd have trouble making any blank lines, even with null fields unless you can figure out a way to concatenate the number and size with the tab into a single field and leave that field null if there is no data in the record for a particular column). Your frames would all be the same size, still, so you'd have to do addtional work after the merge, possibly with Rorohiko.com's TextStitch script, to thread the individual stories and eliminate the gaps.
Peter's idea in post #7 probably works for getting the data in just fine. But there are bugaboos in the data itself.
As in your screen shot of the spreadsheet, the data needs moved around so the CAS numbers and the sizes are in order. There is one instance (and so likely others) where there are more than one CAS number used, etc. But for getting the data in, you just need enough columns to handle the largest number of items under one name:
And once that it is done:
And then once the merge is done into a new document, the frames stitched together, a second column added to the frame (or threaded seperate frames), you will still have a lot of clean-up to do I think.
I don't know how many items drugs/items there are, but if in the hundreds, there is a lot of work in the spreadsheet.
This is great, thank you for going into so much effort! I've created the table as suggested and it worked. It now reads all the information correctly from the table and I will get them to change the excel sheet.
My only issue now is to make the tables fit well. I can either get them to fit all the items, but with space left if there are less than 5... or if I shrink the table, the text oversets. Tried the option of removing blank lines for empty fields, doesn't seem to work (unless I again didn't set up the table correctly).
Here is 1 that overlaps:
And here there is extra space left:
The CSV file that I'm currently testing:
ps. there is only 1 CAS per NAME, so no need to create others
As you can see, all the info is now there, which is excellent. :-)
This is the part we were talking about where you need to stitich together the frames. This is a basic weakness in Data Merge -- all the merged frames are exactly the same size regardless of content, and the text does not flow from frame to frame. I suspect you'll need to stitch the text, then thread the frames, and finally copy/paste the text to a new file (I do that with a directory I update every year).
An important detail that is easy to overlook in this workflow is that you should have a paragraph break in the placeholder template after the last field to be sure when you stitch together the individual frames the last pargarph in one frame does not merge with the first paragraph in the next. Go to Rorohiko.com and check out the TextStitch script.
Big thanks to everyone for their help.
I've had some trouble installing the TextStitch script, but apart from that - everything works as explained. I'm sure I'll get the script right eventually too. Now the fun begins :-)