Multiple search and replace script

[Pyanepsion]

I am using a custom business document that has about 80 variables. Some are repeated several times, others only appear once.

How can I automate the cut and replace the entire document in one pass for each new customer?

Here is an extract of some variables for a fake user record:

• $company$=Principality of Tingry
• $civility$=Your Highness
• $gender$=Masculine
• $first name$=Christian-Louis (5 times)
• $surname$=Montmorency-Luxembourg (3 times)
• $street$=57 Rue de Varenne
• $postalcode$=75007
• $city$=Paris=
• $country$=France
• $landlinetelephone$=01 42 75 79 15
• $radiotelephone$=
• $e-mail$=
• $website$=

Each sequence of $civility$ should be replaced by Your Highness, $fistname$ by Christian-Louis, etc. in one pass instead of 80.

Thank you for your explanations.

[walt.farrell]

Sounds like you want Data Merge. You could set up a 2-line CSV or TSV file (heading to give field names, then a data line with the field values), and a source file with the Data Merge field names in it.

[Old Bruce]

I agree that Scripting would be great, apparently there will be at some point some Javascript ability. Do not get your hopes up, at some time may not be the next year, or even the next version.

Walt's suggestion for Data Merge is the way I would go right now, and that might be the better way even if we did have Scripting available.

[Pyanepsion]

@walt.farrell, and @Old Bruce,

You are talking about merging business card type data. It won't work here, because you're using a whole brochure and not just one sheet, and the number of variables is several dozen, not just two or three.

So we come back to the first idea: which script to use?

[Old Bruce]

Do I understand that you have a text document with 

"

 

• $company$=Principality of Tingry
• $civility$=Your Highness
• $gender$=Masculine
• $first name$=Christian-Louis (5 times)
• $surname$=Montmorency-Luxembourg (3 times)

"

scattered throughout and you need to replace them with the Actual Names (those after the = sign)

And... you have several different people (Kings, Queens, Dukes etc) to send a personally addressed/designed brochure. So you will need several different replacements made, several different times.

Am I understanding this correctly? 

[walt.farrell]

18 hours ago, Pyanepsion said:

You are talking about merging business card type data. It won't work here, because you're using a whole brochure and not just one sheet, and the number of variables is several dozen, not just two or three.

You can merge full documents.

See, for example, these files:

template-for-data-merge.afpub

merged-file.afpub

test-data-merge.csv

[Pyanepsion]

2 hours ago, Old Bruce said:

Am I understanding this correctly? 

Yes, that's exactly it. Here is a concrete example that is completely made up.

You have to imagine an initial text that you adapt to each recipient. Most of the text is the same, except for a few <variable> things that we'll put in an Excel table.

First recipient

<man>=lumberjack
<woman>=lumberjack
<number>=seven
<age>=seven
<name>=Hop-o'-My-Thumb

Second recipient

<man>=king
<woman>=evil queen
<number>=three
<age>=eight
<name>=Kirikou

Here is an example of the text.
Page 1

Quote

Once upon a time there was a <man> and a <woman> who had <number> children, all boys; the eldest was only ten years old, and the youngest was only <number>. 
It will be a wonder that the <man> had so many children in so short a time; but it was because his wife was quick at work, and had not less than two at a time. 

Page 5

They were very poor, and their seven children caused them great inconvenience, because none of them could yet earn a living. What grieved them still more was that the youngest was very delicate and did not say a word, taking for foolishness what was a mark of the goodness of his mind. 
He was very small, and when he came into the world he was hardly bigger than his thumb, so that he was called the little <name>. This poor child was the scapegoat of the house, and was always given a bad name. However, he was the shrewdest of all his brothers, and, if he spoke little, he listened a lot. There came a very unfortunate year, and the famine was so great that these poor people resolved to get rid of their children. 

Page 7

One evening when these children were in bed, and the <man> was by the fire with his <beaker>, he said to her, his heart clenched with grief:
[Blah blah blah]

Etc.

[walt.farrell]

You can do that with my approach, but for each recipient you need to use a separate CSV or TSV input file, and you need to run the data merge "n" times, once per recipient.

[Old Bruce]

I think I would use a Python Script on text files and then import the finished file(s) into Publisher.

You'll need the various sets of names etc in separate text files (you could just use one file but that is beyond my ability to describe).

<man>=lumberjack
<woman>=lumberjack
<number>=seven
<age>=seven
<name>=Hop-o'-My-Thumb

Second recipient

<man>=king
<woman>=evil queen
<number>=three
<age>=eight
<name>=Kirikou

Now you have your multi page story with the <variable>s in it. Run through the story word by word checking to see if the word is <man> or <woman> or ... and then replace it with the text after the equals sign.

The script will first read each recipient text file and then generate a new text file after stepping through the Story. Or it can generate one text file after reading all the recipient text files, your choice.

The script would be in two basic parts. You would have the script generate two lists inside itself by reading the recipients text files (one at a time), one is the <variables> and the other is the actual name or value. Second part is reading the story with the variables. Reading word by word and when it encounters a <man> that is number 0 and it will replace that word with number 0 from whichever text file it has most recently read in.

[Pyanepsion]

15 hours ago, walt.farrell said:

You can do that with my approach

Thank you very much, Walt.

With this example files, the explanation becomes much easier to understand.

I will now be able to prepare my ‘personalized’ business letters much faster.

1. Prepare the list of variables in an Excel sheet (here Origin.xlsx) in 2 columns. The first column indicates the field and the second, the value of the field. We thus obtain 1+7 rows = 8 rows in the example you have given. Note that you can give the field any name as long as it is not identical to the name of another existing field. This is much more readable than my $variable$.

2. Transpose into another Excel sheet (here  Fusion.xlsx) the rows and columns to be in conformity with the import format. We now have only 2 rows, but 7 columns.

3. Import.

[Old Bruce]

4 hours ago, Pyanepsion said:

2. Transpose into another Excel sheet (here  Fusion.xlsx) the rows and columns to be in conformity with the import format. We now have only 2 rows, but 7 columns.

 

You have one Header Row with the names of the fields (champs) and then one row with one record's worth of fields. Add more rows with the actual names and city etc for each client/customer.

[walt.farrell]

4 minutes ago, Old Bruce said:

You have one Header Row with the names of the fields (champs) and then one row with one record's worth of fields. Add more rows with the actual names and city etc for each client/customer.

Yes, you can do that.

But if you do, the .afpub file generated by Data Merge will have multiple sets of pages. That is, in my 3 page template file above, if I run the Data Merge using a CSV file with 2 data rows, my generated file has 6 pages. If the CSV file has 3 data rows, the generated .afpub file has 9 pages. Sammple included below for 2 data rows. (The template is in my earlier post.)

You will then need to split the final exported PDF or final print file to separate the relevant pages for each customer.

merged-file-with-two-data-rows.afpub

test-data-merge-2-rows.csv

[Pyanepsion]

This data fusion functionality is quite interesting.

It allows you to automate most of the commercial correspondence, sales pitches, of course, but also invoices, reminder letters, all the repetitive things in the daily life of a company.

In the publishing world, I can see the possibility of automating catalogues, labels, name envelopes, but also covers.

😊It has little to do with the similar functionality of Word, which tends to be a bit of a headache.

[wonderings]

As Walt mentioned you can do that in a document. You are not limited to just one data field per page. I regularly do multi page letters and brochures with variable throughout. I do it in Indesign though not Publisher so can't be of any help with the functionality of it. Data merge itself is a pretty powerful function and opens a lot of doors, I was surprised when it was added to Publisher, I thought for sure it would be a V2 feature

[Pyanepsion]

The French helper made a big mistake. It tells us that to be successful in our data fusion we have to ‘design on only one spread’, which is not true as Walt showed us.

 

Quote

 

Les étapes clés pour réussir la fusion de données sont les suivantes :

• Créer une source de données
• Concevoir sur une seule planche ou grille ↔ Design on only one spread or grid
• Ajouter la source de données externe
• Filtrer des enregistrements de données
• Insérer des champs
• Fusionner et publier

 

•  

[Old Bruce]

3 hours ago, wonderings said:

I thought for sure it would be a V2 feature

Throughout the suite over the last couple of years there have been many additions made that surprised me. "I get this for free !?"

[wonderings]

1 minute ago, Old Bruce said:

Throughout the suite over the last couple of years there have been many additions made that surprised me. "I get this for free !?"

yes, Serif brought some great updates at no charge, updates that I think easily could have been left for V2 and people would not have had an issue with.