Skip to main content
M
Marty622
Participant
March 24, 2021
Answered

Data Merge Multiple Records specific positions for Postcard Imprint

  • March 24, 2021
  • 3 replies
  • 1412 views

I am a graphic designer for a printing company and I process a LOT of mailing files (barcoded imprint files which must be kept in order by the end of production). Years ago I found an excellent script which allows me to import multiple pdf pages in whatever position on a page I need them. This has been very helpful because I can export the barcoded imprint files as a pdf and import the pdf and keep it in order so that when the files are printed and cut, the imprint files are in order for sorting into trays:

i.e. a 5 x 7 postcard with 400 addresses prints 4-up on 11 x 17 stock; when this is printed and cut to size, I want to have addresses 1-100 in position 1 (upper left corner) so that the first stack is the first 100 postcards; position 2 (upper right corner) would have addresses 101-200, etc.

 

This works perfectly for PDF files which I can import using the script; however, I have recently been asked to use EXCEL files rather than pdfs and while I can do a data merge with multiple records, the problem I'm running into is that it puts addresss 1-4 on the first page and 5-8 on the second page, etc. So rather than having a finished stack of addresses 1-100, I have 1,5,9,etc and this does NOT WORK for production.

 

I cannot find a way to use the excel file as my data source AND place the content as-needed in specific positions (as indicated above). I've tried doing the data merge multiple times, i.e. doing the first position merge for just lines 1-100 from the excel file and then switching the data to the 2nd position and doing a 2nd data merge, but this either updates both fields (if the text is on the master page and the data source is still linked), OR it inserts the 2nd data merge on the 1st page of the file and then continues it on page 101 so you end up with 1000 pages in the file--it does not continue the 2nd merge in the 2nd position on the pages already used from the first merge.

 

My current work-around is just to data merge the excel file 1-up and export as a pdf and then import using the multi page pdf importer as I've always done; but as you can imagine, this is time consuming.

 

Is there a script (like the multi page pdf importer), or another work around for how this can be done? Using the Excel files would save me a lot of time and make my files a lot smaller than using hundreds of pages of pdf imprint files, but I cannot think of a way to do it that I've found to work. Any suggestions or help would be greatly appreciated.

Correct answer Colin Flashman

I have a script dedicated to the task of Cut and Stack for data merge. See my video here on how it works and where you can get it:

https://youtu.be/YNbtRsMZVQI

3 replies

Colin Flashman
Community Expert
Colin FlashmanCommunity ExpertCorrect answer
Community Expert
March 24, 2021

I have a script dedicated to the task of Cut and Stack for data merge. See my video here on how it works and where you can get it:

https://youtu.be/YNbtRsMZVQI

If the answer wasn't in my post, perhaps it might be on my blog at colecandoo!
    M
    Marty622Author
    Participant
    March 24, 2021

    Thank you so much, Colin. This script is perfect some of my tasks as well, and the video addresses EXACTLY the issue I've been dealing with. I will try the script out with the addresses for the postcard imprint and see how it works, but I can see already that it's amazing from the video. I've been doing form numbering by using Excel Formulas for years with text box flow, so I'm so happy to see someone else has found cleaner solutions to such (what I believe to be) a common problem!

      Lluis Scriptografus
      Lluis Scriptografus
      Participating Frequently
      March 24, 2021

      Hi, Marty:

       

      Solution 1

      The way you import the PDFs to keep them in order is also called "Cut and stack" in the printer process. You could ask your printer man if this option is available on the printer. Then you only must send the unprocessed PDF. But let's assume this is not your case.

       

      Solution 2

      I don't understand what kind of data you import from an excel in the way you do for a PDF. But maybe this method can be helpful. In my case, I need to put a number in the same way you sort your postcards, so in the upperleft corner I get 1, 2, 3,... stacked once cut.

       

      I arrange the numbers in the excel sheet in four columns so every row represents a register for the merged data process.

      Col_A  Col_B  Col_C  Col_D
  1     101    201    301
  2     102    202    302
  3     103    203    303
 ...    ...    ...    ...
 100    200    300    400

      Now, my template have 4 copies of the postcard 5x7 in a 11x17 page and I put the four data codes in that page. <<Col_A>> is set in the upper left corner postal, <<Col_B>> in the upper right, <<Col_C>> bottom left and <<Col_D>> in the Bottom right (or as needed).

       

      When data merged, I obtain 4 postcards for every register (a row in the excel sheet). It's easy to do when the data is a number. But let's assume this is too easy and your data consist in names, adressess, postal codes, and so...

       

      Solution 3

      Then, the fastes solution is to duplicate every field (column in the Excel sheet) as mentioned: _A, _B, _C and _D. For example:

      Name_A  Adress_A  CP_A  Name_B   Adress_B  CP_B  Name_C  Adress_C  CP_C  Name_D  Adress_D  CP_D

      Now you only need to cut properly the data in the excel sheet in four blocks.

       

      Get Luck!

       

       

       

        M
        Marty622Author
        Participant
        March 24, 2021

        YES!!!!

        What I'm dealing with is more complex than numbers, as it's hundreds (sometimes thousands) of addresses with barecodes, endoresements, name, title, company, address1, address2, city, state, zip, etc. but that last solution should work perfectly!!

         

        This is exactly the solution I was trying to work out over the last few days, but I couldn't get my mind to process it for some reason (I think I was focusing too much on the data merge itself, rather than the data source because I should have realized this easily). This is brilliant.

         

        Thank you so much, Lluis!

        Grant H
        Community Expert
        Grant HCommunity Expert
        Community Expert
        March 24, 2021

        Hi Marty,

        in inDesign your options are rows first or columns first per page. The simplest thing to do is run a script to change the rows in your spreadsheet or create a text file (various ways to get the order) that if you need it 4 up as you explained and you have 120 records to run: (i did this with a formula in open office and saved as csv opened as text and grep'd the end of paragraph with a replacement "," and I am sure there are easier ways 1,31,61,91,2,32,62,92,3,33,63,93,4,34,64,94,5,35,65,95,6,36,66,96,7,37,67,97,8,38,68,98,9,39,69,99,10,40,70,100,11,41,71,101,12,42,72,102,13,43,73,103,14,44,74,104,15,45,75,105,16,46,76,106,17,47,77,107,18,48,78,108,19,49,79,109,20,50,80,110,21,51,81,111,22,52,82,112,23,53,83,113,24,54,84,114,25,55,85,115,26,56,86,116,27,57,87,117,28,58,88,118,29,59,89,119,30,60,90,120, and so on then copy and paste into the range dialogue. Indesign will propagate into the stacks you desire. (and yes you can paste large sequence in the field)

        that should sort out your dilemma

        /G

         

         

          Grant H
          Community Expert
          Grant HCommunity Expert
          Community Expert
          March 24, 2021

          I tested it with 400 records:

          and it works

          1,101,201,301,2,102,202,302,3,103,203,303,4,104,204,304,5,105,205,305,6,106,206,306,7,107,207,307,8,108,208,308,9,109,209,309,10,110,210,310,11,111,211,311,12,112,212,312,13,113,213,313,14,114,214,314,15,115,215,315,16,116,216,316,17,117,217,317,18,118,218,318,19,119,219,319,20,120,220,320,21,121,221,321,22,122,222,322,23,123,223,323,24,124,224,324,25,125,225,325,26,126,226,326,27,127,227,327,28,128,228,328,29,129,229,329,30,130,230,330,31,131,231,331,32,132,232,332,33,133,233,333,34,134,234,334,35,135,235,335,36,136,236,336,37,137,237,337,38,138,238,338,39,139,239,339,40,140,240,340,41,141,241,341,42,142,242,342,43,143,243,343,44,144,244,344,45,145,245,345,46,146,246,346,47,147,247,347,48,148,248,348,49,149,249,349,50,150,250,350,51,151,251,351,52,152,252,352,53,153,253,353,54,154,254,354,55,155,255,355,56,156,256,356,57,157,257,357,58,158,258,358,59,159,259,359,60,160,260,360,61,161,261,361,62,162,262,362,63,163,263,363,64,164,264,364,65,165,265,365,66,166,266,366,67,167,267,367,68,168,268,368,69,169,269,369,70,170,270,370,71,171,271,371,72,172,272,372,73,173,273,373,74,174,274,374,75,175,275,375,76,176,276,376,77,177,277,377,78,178,278,378,79,179,279,379,80,180,280,380,81,181,281,381,82,182,282,382,83,183,283,383,84,184,284,384,85,185,285,385,86,186,286,386,87,187,287,387,88,188,288,388,89,189,289,389,90,190,290,390,91,191,291,391,92,192,292,392,93,193,293,393,94,194,294,394,95,195,295,395,96,196,296,396,97,197,297,397,98,198,298,398,99,199,299,399,100,200,300,400

           

          Terms & ConditionsAccessibility statement
          Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices