How to minimize the amount of manual interaction in InDesign?

[Jeffrey_Smith]

I am looking for a workflow suggestion for a directory listing. I would like to either use a datamerge, or prepare data for import, (knowing there would be data manipulation required in either process). Below is basic sample data of what I am provided and finished layout. Please keep in mind there are many other columns of data including address, web, email, affliation icons, etc.

The basic sample data (spreadsheet) is like this:

Company	Contact	Country
ABC Company	Name 1	USA
ABC Company	Name 2	USA
Incorporated	Name 3	Canada
Incorporated	Name 4	Canada
Incorporated	Name 5	Canada

The finished layout will look this:

ABC Company
USA

Name 1

Name 2

Incorporated
Canada

Name 3

Name 4

Name 5

Open to any workflow suggestions that would minimize manipulation, and hopefully some automation.

<Title renamed by moderator>

[FRIdNGE]

Give me more and Grep will give you more queries! ...

(^/)

[FRIdNGE]

Of course, it could be played like this but it's a little more ... fastidious! 😉

https://youtu.be/VEqcgKEr6lk

(^/)

[Loic.Aigon]

I have no doubt smart minds like you can get that level of success but not all InDesign user is a developer able to build and maintain large automation projects with code. Javascript is already challenging but Applescript is for the bravest (and I say it having done quite a bit). …end tell.

[Loic.Aigon]

First of all, as a disclaimer. While I sold and accompanied design firms and designers with EasyCatalog in the past, I am not trying to promote those solutions to get any sales in return. I am not commissioned either to promote EasyCatalog. I am just here to answer your questions and I will leave it to you from there. 

• I need to perform a lot of additional data cleanup, like consistent phone format, remove "www." from website, Change UPPER case to Sentence case, etc., can the plugins perform these functions?

Yes, that's basically one of the many great features of the core features of EasyCatalog. Once the data is loaded, you can normalize entries. Phone numbers are particularly a good example. And in case your data is updated, the formatting rules applies accordingly. Any of the common cleanup actions can be achieved coding free.

• Can the plugins do conditional formatting, like change the color of a company name whether the company is US or International?

Yes, definitively. There are many conditional processing options that you can set with EasyCatalog. The beauty of it is that you could apply a character style based on some specific field value. If the value comes to change, the char style will be changed as well. Then EC can update the doc so you are always up to date regarding data and layout.

• How well do the plugins handle empty records, fields, and lines? Indesign data merge can remove blank lines, but leaves tags behind.

That as well can be managed with EasyCatalog. Data can either be filtered so your data view only presents the records you want to proceed. Or you can use conditions so text only appears if the data field is not empty.

So once again, not trying to sell anything here and I know that we, skilled developers can achieve much with code or techniques. But once again, my experience showed me that if you do regular structured projects, those plugins are gems especially for non tech users.

I leave it to your thinking and be sure I would be happy to help with coding if you want to go down this road. Otherwise, you may contact 65Bit (the editor of EasyCatalog) or EmSoftware (editor of InCatalog) so they can recommend people to advise you in your local area if you prefer such plugins.

HTH

Loic

[FRIdNGE]

Salut Loïc,

Cette vidéo date d'il y a presque 8 ans ! Depuis, je suis heureusement passé pour moi ... à l'électrique !!!

(^/)

[FRIdNGE]

To be more precise, not sure Jeffrey_Smith could need more than basic Grep Queries!

Of course, some Grep knowledges could be needed!  😉

I know it's just a sample but, using a (free) queries-launcher, he will just need here 1 click!

8 (not 5, including para styling) Grep Find/Replace.

Why 8? … Because I've no time to imagine less (but more complex) and it's ever faster to write simple queries!!  😉

Beginning …Query 0Query 1Query 2Query 3Query 4Query 5Query 6Query 7

Best,

(^/)

[Robert at ID-Tasker]

Nice 🙂 can you explain queries 1 & 3 - where you are adding @?

And how will it looks like - when country's name is more than one word? 

[FRIdNGE]

Robert,

In this "tabulated text", the main markers are of course the tabulations and the carriage-returns.

So, Query 1: ([^\t]+)\r.+\K\t(?=\1)

searches a "tabulation" ! and …

finds first the tab before the second "USA",

finds first the tab before the second "Canada",

finds first the tab before the third "Canada".

The main target of this Query 1 is its beginning: "([^\t]+)\r"

… that means a text without tabulation at the end of a para preceded by a "tabulation".

If I have "Happy New Year" instead of "USA" or "Canada", the Query would find the 3 tabs!  😉

To remove the "duplicated" texts, we need to add to them [queries 1 & 3] a specific temporary marker "@" [I could choose another glyph I won't find in the text]. The Query 1 works with the Query 2 and the Query 3 works with the Query 4.

To apply para styles, we need to add another specific temporary marker "#" [I could choose another glyph I won't find in the text] associated to an arbitrary number to differentiate each para style: "#2" for "Italic" and "#3" for "Regular". No need one for "Bold" because of Query 0.

I know the limits of such brain gymnastics for most people but, btw, learning/knowing Grep is a true time-saver for those who play with it! … Its limit: it doesn't make coffee!

(^/)

[Robert at ID-Tasker]

I know something about GREP 😉 but how can you skip 1st occurrence of the USA and then Canada? 

Which part of the query works as "skip first duplicate"?

[FRIdNGE]

Because of "\1" ! …

In the Query 1 :

([^\t]+)\r.+\K\t(?=\1)

"\1" makes reference to "([^\t]+)" I will call "blabla"!

So, the query 1 searches this "tabulation" \t":

([^\t]+)\r.+\K\t(?=\1)

… supposing the text I call "blabla" at the end of the previous para:

([^\t]+)\r.+\K\t(?=\1)

… and followed by any text in the current para:

([^\t]+)\r.+\K\t(?=\1)

is found too after the "tabulation targetted":

([^\t]+)\r.+\K\t(?=\1)

SO, the Query 1 will never find the first occurence of, e.g., "USA" because it doesn't exist in the previous para!

Simple!?  Aha!

(^/)

[Robert at ID-Tasker]

Thanks 😉 that's what I was thinking 😉 lookahead + \1 as a switch to ignore 1st occurrence.

And also \K plays it's role. 

GREP is extremely powerful tool on its own 😉 

[Jeffrey_Smith]

Thanks for the GREP suggestion. I can use some of the GREP codes for other tasks.

[Dave Creamer of IDEAS]

I did not read this entire thread, but Em Software InData would make short work of this. I worked on a project that had much more complex data and, with InData, I was able to layout about 600 pages in an hour.

[Jeffrey_Smith]

Although I would like to accomplish this task with Excel and Indesign, I am open to plugins. I mentioned previously in thread that in additonal to data manipulation for either import or merge, I would need to perform a lot of data cleanup. The question would be, can a suggested third party solution accomplish desired layout with little or no manipulation, AND perform the data cleanup as well.

Loic.Aigon had suggested EasyCatalog, and that cleanup can be done as well.

Can InData work with the data file, with little or no manipulation, and also cleanup data?

[oobie222]

Hi. I do directories and use Adobe Tagged Text Files. My data source (Filemaker) exports a text file with the data bits surrounded by paragraph and character style tags, as well as table tags. These tagged text files are placed in an InDesign document where all the styles have been defined, resulting in a fully formatted listing.

[Dave Creamer of IDEAS]

Attached is a super-old PDF on working with data from FileMaiker (could work for Excel too). Originally written in the early nineties, it was meant for PageMaker but could be modified for InDesign.

The Directory Publishing PDF shows how to use the next-style setting for quick formatting.

For the InData Job, 

This was the original data from a database and opened in Excel:

I cleaned it up with a series of Excel find/replace macros:

This was the final result in InDesign using InData plugin:

This was a sample of the InData code used:

[oobie222]

Hi. It used to be that the Em Data plugins would not allow one to make a document text flow in which every bit of text was completely formatted with Paragraph Styles and Character styles when placed. So, for example, you could make text red, but not with your Paragraph or Character Styles. Is this still the case? Thanks.

[Dave Creamer of IDEAS]

I've never run into that issue with many years of usage. You simply apply the paragraph/character styles desired to the coded text.

(I've been using InData since 2001 and Xdate for QuarkXPress before that.)

[Brad @ Roaring Mouse]

"Is this still the case?"

It works properly if you set your template properly. Haven't had any issues in that regard, and I have been using it since 2012.

This is an example of one of mine: the template is on the left with all paragraph and character styles applied, and the resulting file at right (blurred to hide info):

 

[oobie222]

Thanks,  but that's not what I meant. Using your example, the text is styled correctly, but not via Paragraph and Character Styles. So, for example, if the list were too long or short to fit the space, you could adjust the Para and Char Styles instead of reformatting your placeholders and re-importing.

[Dave Creamer of IDEAS]

>Using your example, the text is styled correctly, but not via Paragraph and Character Styles. 

How can you tell? You don't code in the para/char style names--you apply the styles directly to the coded text like you would any text. 

[oobie222]

Right. But Adobe Tagged Text is a little different. Below is a small example. You can see the different Paragraph and Character Styles that will be applied upon import. The difference from your example is that after the text is in the InDesign document, all of these styles are addressable via their definitions, so if any text attributes need to change over the entire document (font size, color, space before, etc), because P and C Styles are applied, their style definitions can be changed in one place and will be applied globally.

Partial example. The styles, eg, "50-01-FullNameCEO", are already created in the InDesign document before import: 

<pstyle:50-01-FullNameCEO><cstyle:50-01-FullNameCEO>John Q. Doe<cstyle:>

<pstyle:50-02-CompanyName><cstyle:50-02-CompanyName>ABC Inc.<cstyle:>

<pstyle:50-03-CoAddress><cstyle:50-03-CoAddress>2937 East Laurel Boulevard<0x000A>Suite 1023<cstyle:>

<pstyle:50-05-CoCSZ><cstyle:50-05-CoCSZ>River Bend, MI  48201

<cstyle:><pstyle:50-06-CoTel><cstyle:50-06-CoTel>Tel: <cstyle:>313-223-1487

<pstyle:50-08-CoEmail><cstyle:50-08-CoEmail>jqd@abcinc.com<cstyle:>

<pstyle:50-09-CoURL><cstyle:50-09-CoURL>www.abcinc.com<cstyle:>

I hope I explained that better this time!

[Brad @ Roaring Mouse]

If you are looking at a large amount of this data. you should look at some of the software plugins that do this sort of work. I do annual directory listings for one project, which is about 200 pages worth of this. I personally used Em Softwares' InData but there are others out there. It takes some time to figure out how to "program" how the incoming data needs to be formatted (using things <<if / then>> type phrases, which does automatic formatting and Styles application, and does things like ignore empty fields without adding an unnecessary carriage returns, etc. Once you get that done, the actual building of a long document takes only minutes. There will still be the odd change you need to do, but you will be 99% there.

For you right now, though, what would make your data work better is to add the extra names as extra columns in the same row (even if there aren't that many names for each listing) like this:

Then, using the built in data merge in ID, you will of course end up with extra carriage returns on the empty name cells, but you can do a simple search & replace to remove extra returns.

[Loic.Aigon]

That's my take, being code-able doesn't mean it has to be coded. Those plugins are certainly pricey but they can save so much time for non-tech users making many repeating routines in working on structured documents a joke.