Data Merge Multiple Records specific positions for Postcard Imprint

[Marty622]

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.

[Grant H]

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]

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

[Marty622]

Thank you, Grant. I can see how this would work as well. I could set up the range numbers via excel to make it easier. I will test this to see if it works with the postcard addresses and let you know.

You guys are all so awesome. I appreciate the help so much.

[Lluis Scriptografus]

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!

[Marty622]

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!

[Colin Flashman · Accepted solution]

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

[Marty622]

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!