Skip navigation
vlassov
Currently Being Moderated

Grouping items in data merge

Mar 26, 2013 8:13 PM

Hi guys. I've read through numerous posts and webpages, watched videos - but haven't found a solution.

I am trying to do a data-merge between a csv/txt file and an InDesign document. All is fine, works well, except that this is for a catalogue and requires some grouping. Let me explain.

 

Current data has the following collumns

ITEM NAME SIZE CAS

 

If I am to run these as seperate items/names/sizes and costs - everything works as it should. However, the NAME will have a number of different ITEMs, all with different item numbers, sizes, with CAS# not changing.

 

eg.

ITEM 12345 NAME Orange Juice SIZE 100ml CAS 123-4

ITEM 54321 NAME Orange Juice SIZE 200ml CAS 123-4

 

So both of these need to appear under the same name (Orange Juice) and under the same CAS#, but have different item# and size. ie., something like this:

ITEM 12345 NAME Orange Juice SIZE 100ml CAS 123-4

ITEM 54321                                 SIZE 200ml

 

Some will only have 1 size and item#, others multiple, as above. I tried grouping the items in excel under the same name/cas - doesn't work. In datamerge, it just merges them as seperate fields or duplicates. Tried a number of options, all seem to fail. I realise there are proper catalogue programs out-there, but was hoping I could do without one this time, as this catalog is going to be done once every couple of years.

 

The list of data is 2,000+, so custom entry would take a while... unless all else fails.

 

Let me know if I didn't exaplain myself clear enough! Hope someone can help :-)

 

ps. I'm a graphic designer, not a programmer, so that solution is out of the question too

 
Replies
  • Currently Being Moderated
    Mar 27, 2013 8:39 AM   in reply to vlassov

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 27, 2013 5:10 PM   in reply to vlassov

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 27, 2013 5:13 PM   in reply to vlassov

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 2, 2013 4:37 AM   in reply to vlassov

    I don't think Data Merge is sophisticated enough to do this.

     

    Possibly xml, a commercial catalog plugin, or scripting.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 2, 2013 4:46 AM   in reply to Peter Spier

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 2, 2013 5:33 AM   in reply to vlassov

    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:

     

    capture-000185.png

    And once that it is done:

     

    capture-000184.png

    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.

     

    Mike

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 3, 2013 1:59 AM   in reply to vlassov

    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.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points