• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

CC Data Merge - Check Longest Variable Strings - Catch Overset Before Export

Explorer ,
Jun 14, 2018 Jun 14, 2018

Copy link to clipboard

Copied

Good morning,

I'm using CSV data in InDesign CC to populate address blocks for postcard mailings. After I import my flat CSV, I need a way to select a field and ask InDesign "What is the longest string in this column of the CSV"? If I could do this, I could catch errors before I export my final PDF.

We used to use FusionPro before that got too expensive, which had this feature front and center in the interface. I just need to make sure that whoever is entering data into the address columns of the Excel file didn't fall asleep on their keyboard and give me an address that looks like:

"123 Main St. AFIH:LGSDH:LKJSGD:LFJA"PUOGFBASDN:ASKLDA":LKJFA"

Thanks!

Views

1.1K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Explorer , Jun 21, 2018 Jun 21, 2018

Okay, here's what I did:

  1. Start a new blank workbook in Excel.
  2. Go to the Developer tab, click Record Macro.
  3. In the Record Macro dialog box, don't bother entering a name for the macro in the Macro name box. You can accept the name that Excel gives you, such as Macro1, since this is just a temporary macro.
  4. In the Store macro in box, pick Personal Macro Workbook > OK. This is the most important step, because if you don't already have a Personal Macro Workbook, Excel will create one for you.
  5. Click Develop
...

Votes

Translate

Translate
Community Beginner ,
Jun 20, 2018 Jun 20, 2018

Copy link to clipboard

Copied

Anyone?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 20, 2018 Jun 20, 2018

Copy link to clipboard

Copied

Hi Mark,

without using a script you could try the following:

Place the text and convert it to a table. Set your cell insets to zero. Format the text to your needs later in the merged document. Set the height of all cells to a value so that one line of text will not overflow a cell. Check overflow.

If cells overflow change the width of all columns until there is no overflow.

Now change the width of a particular table column until there is overflow. Do this using reasonable steps.

By using the Story Editor Window see what cell overflows. Now you indicated the longest text.

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jun 20, 2018 Jun 20, 2018

Copy link to clipboard

Copied

Thanks Laubender!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jun 20, 2018 Jun 20, 2018

Copy link to clipboard

Copied

Hi,

Could you just show before/after screenshots?

Best,

Michel [FRIdNG​E]

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 20, 2018 Jun 20, 2018

Copy link to clipboard

Copied

I'll do my best Michel. Here goes:capture 01.jpg

capture 02.jpg

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jun 20, 2018 Jun 20, 2018

Copy link to clipboard

Copied

Could you show the invisible chars? ...

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 20, 2018 Jun 20, 2018

Copy link to clipboard

Copied

I appreciate the help, but I'm not sure of what you're asking. As far as I know, there aren't any invisible characters. See image:

capture 03.jpg

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jun 20, 2018 Jun 20, 2018

Copy link to clipboard

Copied

The “blue chars”! ... 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 20, 2018 Jun 20, 2018

Copy link to clipboard

Copied

Oh - I see. Okay:

capture 04.jpg

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jun 20, 2018 Jun 20, 2018

Copy link to clipboard

Copied

So ... could we suppose you just want and need to detect a “second line or more” in a para?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 20, 2018 Jun 20, 2018

Copy link to clipboard

Copied

That sounds like it would help.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jun 20, 2018 Jun 20, 2018

Copy link to clipboard

Copied

So ... you just (and simply) need to include a “nested line style” in your para style (I suppose you use a same one).

Build the associated char style as an “red underlining” and you will just need to play a simple find/replace as: Find this char style!

Best,

Michel

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jun 20, 2018 Jun 20, 2018

Copy link to clipboard

Copied

Thank you for your help Michel. I haven't used Paragraph Styles or nested Character Styles in a while because we rarely design or print anything more than postcards, flyers, and short newsletters. It's something that I'll have to research more about.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 20, 2018 Jun 20, 2018

Copy link to clipboard

Copied

This is something I wouldn't check in InDesign, but in the Database beforehand. If you can put the database in Excel, use the solution that can be found on an Excel forum:

https://www.extendoffice.com/documents/excel/1692-excel-find-longest-string.html

There's also the ability to "copyfit" the text using GREP styles once you know the length of the longest line:

Episode 3: Using GREP styles to fit text into a text frame in InDesign - YouTube

If the answer wasn't in my post, perhaps it might be on my blog at colecandoo!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jun 20, 2018 Jun 20, 2018

Copy link to clipboard

Copied

Thanks Colin, that was basically what I came up with too. I figured it was easier to check the associated CSV.

What Michel wrote made me think: What I was *really* looking for was for when the people entering data pressed Alt-Enter or whatever they do on the software that they're using, throwing a hard CR-LF in their data and screwing up my output. Finding the longest string was one way to figure this out, but I found a better way.


I ended up copying/modifying a VBA script for Excel that finds and removes any of these breaks. So, now I just run the macro on all of our CSV files. It only takes a second or two and it works well enough. Maybe not the most elegant solution there is, but it's good enough for government work.

If I think of it I'll post the script here tomorrow when I'm at work just in case anyone happens by this thread while searching. I'll also take a look at those links you posted. Thanks again sir!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 21, 2018 Jun 21, 2018

Copy link to clipboard

Copied

LATEST

Okay, here's what I did:

  1. Start a new blank workbook in Excel.
  2. Go to the Developer tab, click Record Macro.
  3. In the Record Macro dialog box, don't bother entering a name for the macro in the Macro name box. You can accept the name that Excel gives you, such as Macro1, since this is just a temporary macro.
  4. In the Store macro in box, pick Personal Macro Workbook > OK. This is the most important step, because if you don't already have a Personal Macro Workbook, Excel will create one for you.
  5. Click Developer > Stop Recording, and Excel will have created your Personal Macro workbook.
  6. Close the workbook. Save the Personal Macro Workbook.
  7. Start a new blank workbook in Excel.
  8. Go to Developer > Visual Basic to launch the Visual Basic Editor (VBE), which is where your macros are stored.
  9. You can find your Personal Macro workbook in the Project Explorer pane on the left hand side. If you don't see it, go to View > Project Explorer.
  10. Click on the VBA Project (PERSONAL.xlsb) folder > Modules > Module1 and rename it “Blanks” in the properties panel.
  11. Double click on Blanks.
  12. Paste the following code:

    Sub RemoveLineBreak()

          Selection.WrapText = False 'Removing Wrap Text
          Selection.Replace What:=Chr(10), Replacement:=" ", LookAt:=xlPart, _
          SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False

       End Sub
 

13. Close VBA.

14. Open a test file and run the macro.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines