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

How to keep line breaks in CSV, TXT docs during Data Merge?

Community Beginner ,
Apr 07, 2009 Apr 07, 2009

Copy link to clipboard

Copied

I have tried \r\n and <br> in a .csv and .txt file data merge. Is there an escape character that allows InDesign to read the formatting from the source doc? My source doc has hundreds of records each containing 7 fields, one of which is a list of procedures. These vary from 2 to 8 procedures. Is there an easy way to merge this data into ONE text field on my target doc so that Data Merge preserves the line breaks and my Paragraph Style formats the text in an appropriately numbered list?

TOPICS
How to

Views

61.4K

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
Adobe Employee ,
Apr 07, 2009 Apr 07, 2009

Copy link to clipboard

Copied

Line breaks in the data source aren't preserved when you merge the data. One workaround is to create a separate field for each line. Another workaround is to place a unique symbol where the line break should go, and then do a find/change.

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 ,
Apr 07, 2009 Apr 07, 2009

Copy link to clipboard

Copied

Thanks Bob and Peter.

It is definitely more work to create a unique field for each procedure in the database. And, the Document is considerably less flexible, demanding a lot of extra work to make it presentable. You have to enter empty quotes to indicate no content in every field in the source doc, and then remove the excess list items in the target document.

Keeping the procedures in the same field, separated by a unique string (I chose \r\n to make the text readable in other formats outside ID) is the way to go. Then, command-F to Find/Change all the \r\n's: NOT with a 'forced line break' but an 'end paragraph' symbol (which looks like '∧p') This allows for Bullet/List formatting to properly display the sequence of procedures.

(I also tried doing this with all the data in XML format, but that requires almost a 1:1 ratio of creating, tagging and styling each field. Also, automation is not available.)

Case closed. Not a perfect solution, but near enough. Creating an automated 'Find/Change' makes it very simple.

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 ,
Apr 07, 2009 Apr 07, 2009

Copy link to clipboard

Copied

bryangough wrote:


Is there an easy way to merge this data into ONE text field on my target doc so that Data Merge preserves the line breaks and my Paragraph Style formats the text in an appropriately numbered list?

Most spreadsheets have some sort of "concatenate" function that will allow you to combine the contents of multiple cells into one, even adding additional text if desired, but line breaks will need to added after the merge if you need them between statements in a single field. As Bob said, you can use a placeholder character of some sort and do a find/change to accomplish that.

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 ,
Apr 07, 2009 Apr 07, 2009

Copy link to clipboard

Copied

Solution: To simulate a line break in a csv file that is to be Data Merged into an InDesign document, enter a unique placeholder symbol where you want a line break in the string. Then, data merge. Then, select Edit>Find/Change. Enter the unique placeholder in the Find field, replace with special character 'End of Paragraph' which is a '^p' and then select Replace All. Your string should now be separated into separate lines (and numbered if styled as a list) relative to where you placed the unique placeholder symbol.

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 ,
Jan 18, 2010 Jan 18, 2010

Copy link to clipboard

Copied

I did the same thing...

  1. Replace line breaks with a special string in your CSV data file. (I use TextWranger for text editing.)
    1. Select from the end of one line to the next.
    2. Copy the link break.
    3. Click (command + F) or (Edit > Find) to open Find and Replace.
    4. Paste the line break into the Find/Replace Tool.
    5. Place your special character into the Replace field.
      • I picked "<p>" but anything would work, i.e. "<br />" or "\r\n" or even "{heck-yes}"
    6. Do your data merge into InDesign.
    7. Replace your special character with real line breaks.
      1. Click (command + F) or (Edit > Find/Change) to open Find and Replace.
      2. Type your special string into the Find field.
      3. Next to the Replace field click the @> button and select "Force Line Break"
      4. Test it on just one with Find then Change.
      5. Use Change All to get all your line breaks back.

    This was a long 3-day ordeal to find this solution, so I wanted to be thorough for someone else out there.

    Be sure to post your solutions to nasty ones like this you figure out.

    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
    New Here ,
    Dec 17, 2010 Dec 17, 2010

    Copy link to clipboard

    Copied

    d0ublej0sh I tried using your method but it doesn't work if you have different groups of answers laid out sequentially. When I search and replace a paragraph break I end up changing the paragraph breaks between groups of datas and the result is an invalid .csv file, InDesign can't even import it. Any ideas on how to solve this?

    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 ,
    Dec 17, 2010 Dec 17, 2010

    Copy link to clipboard

    Copied

    You do the repalcement AFTER the merge is complete.Your data file should have special "tag" identifiers of some sort to represent where the breaks will be, and those tags get replaced by find/change after the merge is complete. You would only need to do this if a particular field requires multiple paragraphs, otherwise just do a separate field for each paragraph.

    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 ,
    Dec 17, 2010 Dec 17, 2010

    Copy link to clipboard

    Copied

    Yeah, that's what I eventually did (putting in a special tag to replace afterward.) Definitely wouldn't do sperate fields for different paragraphs, since it was a variable number.

    I borrowed a little from HTML and replaced line-breaks with a <p> character. Then swapped that for a line break after the inport.

    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
    New Here ,
    Dec 17, 2010 Dec 17, 2010

    Copy link to clipboard

    Copied

    The problem is that I'm exporting data from a Google Docs Form -> Spreadsheet that's structured like this:


    Question 1, Question2, Question3 (paragraph break)

    Answer 1, Answer 2, Answer 3 (paragraph break)

    Answer 1, Answer 2, Answer 3 (paragraph break)

    So when I search and replace the paragraph breaks within the answers that are multi-line he's gonna put my chosen tag on those paragraph breaks between groups of answers and InDesign won't be able to import the .csv.

    After the merge is complete it's impossible, I think, because either InDesign changes the breaks in multi-line answers to simple spaces or, if I choose not to change the breaks into spaces, the text doesn't even get pasted from the source file (unless it's invisible somehow, which I doubt).

    The workaround I found out was to tag those paragraph breaks on the multi-lined responses imediately in the Google Spreadsheet with a formula that searches for CHAR(10) within the responses and tags it with <p> on a transitional spreadsheet that feeds from the raw results stored by the form. That way I get those paragraph breaks between the questions and groups of answers which are necessary so that InDesign imports the file and, at the same time, I get a tag like <p> which afterwards I can grep into a line break within InDesign.

    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 ,
    Dec 20, 2010 Dec 20, 2010

    Copy link to clipboard

    Copied

    Yeah, with CSV you really need to get one row per line.

    Perhaps possible as the first step. Step two then being the paragraph replacements.

    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
    New Here ,
    Mar 01, 2011 Mar 01, 2011

    Copy link to clipboard

    Copied

    Hi again,

    Do you happen to know if there's some way to automate changing the placeholder "<p>" to line breaks automatically on export instead of having to do search and replace? I have 30+ documents being created through data merge and didn't really want to do search and replace on all of them.

    Thanks!

    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 ,
    Mar 01, 2011 Mar 01, 2011

    Copy link to clipboard

    Copied

    There are various ways to automate actions like that on each platform (mac or PC.)

    I'd say perhaps alter your output view, script, whatever it is to convert them. Assuming it's from a CMS.

    -josh

    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 ,
    Mar 21, 2011 Mar 21, 2011

    Copy link to clipboard

    Copied

    Unbelievable that we need to jump through these sorts of hoops, especially since you can find this same problem in posts made YEARS ago.  As I write, this very thread is almost 2 years old.

    Of course, as users, we should be a bit understanding.  After all, it's not as if Adobe blackmails us into paying $600 per year EVERY YEAR to upgrade by changing the file compatibility between versions.

    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 ,
    Mar 21, 2011 Mar 21, 2011

    Copy link to clipboard

    Copied

    Did you have a question, or did you just want to complain about backward compatibility, which hasn't changed since it was introduced by exporting .inx back in CS2?

    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 ,
    Mar 21, 2011 Mar 21, 2011

    Copy link to clipboard

    Copied

    That is correct. You are not being blackmailed, coerced or threatened into upgrading.

    Was there anything else you needed verified?

    Bob

    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
    New Here ,
    Feb 24, 2018 Feb 24, 2018

    Copy link to clipboard

    Copied

    You are being blackmailed now. You did have to do it at the time, but now you do.

    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 22, 2021 Jun 22, 2021

    Copy link to clipboard

    Copied

    Haha - I was looking for this answer today, 12 years!! after the orignal post was written 😄 

     

    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
    New Here ,
    May 12, 2022 May 12, 2022

    Copy link to clipboard

    Copied

    Evergreen solution 🙂

    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 ,
    Mar 21, 2011 Mar 21, 2011

    Copy link to clipboard

    Copied

    Guess that was a bit snarky.

    Frustrated that we now have a few hours of work ahead of us to work around the fact that InDesign can't import paragraph marks.

    Compounded by the fact that we need to upgrade for no reason other than backward compatibility.  Our customers provide us with files that we need to work with, and they get annoyed at the "save it down" request.  So, we have little choice but to upgrade 'cause we are not comfortable annoying the customers.

    Today was the day that we needed to authorize the purchase of four seperate upgrades to CS5.. a large and needless expense... and I figured while I was at it, I'd see if this particular issue had been addressed.  When I could see it had not, I reacted poorly.

    I should not have made that post... this forum is here for help, not for venting.

    Sorry.

    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 ,
    Mar 21, 2011 Mar 21, 2011

    Copy link to clipboard

    Copied

    Sorry, I don't know of any program that can read a line break in a .csv or tab-delimited text file in a merge operation as anything other than a new record. If you want to place the file as text, ID is happy to read real breaks, and running find/change is not all that onerous compared, for example, with having to restucture your spread sheet the way it probably should have been set up from the beginning. Ever try to import the notes from an outlook express address book exported to .csv into an apple address book? Makes your problem seem like a walk in the park.

    So your customers find CS5 worthwhile, and you feel it's an impostion that you should have to upgrade to support them? That's something like telling a customer in a restaurant that you only serve hot coffee in the summer when what they really want is iced tea.

    How did you expect to work with all the new features they want to use that have no support in CS3, like multiple page sizes or span/split columns even if there was some magic way to open the file in CS3? For someone in business who has paying customers, the cost of an upgrade to keep pace every 18 to 24 months is a reasonable business expense and easy to budget. And guess what? I've found enough productivity enhancements in each new version to increase my billable output more than enough to cover the cost in short order, and I doubt you bill less than I do.

    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
    New Here ,
    Oct 14, 2014 Oct 14, 2014

    Copy link to clipboard

    Copied

    > Sorry, I don't know of any program that can read a line break in a .csv or tab-delimited text file in a merge operation as anything other than a new record.

    I'm sorry, but this is not true at all. In fact, i don't know any program except Indesign, that can not NOT cope with line breaks inside CSV-fields, since this is perfectly valid according to CSV-RFC, if the field is enclosed in double-quotes.

    In my opinion, Adobe should stick to commonly valid standards. If Indesign supports CSV, it should support it fully, and not only half of it.

    Heck, even Photoshop can cope with line breaks when importing variables from CSV!!!

    It is SO frustrating, if every Tool can do just a bit of what one wants to achieve, but none can do it fully. Photoshop can import CSV correctly, but cannot do GREP-Styles, and Indesign vice-versa.

    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 ,
    Oct 15, 2014 Oct 15, 2014

    Copy link to clipboard

    Copied

    It's true. This is a SAD OVERSIGHT by InDesign.

    You can't have leading print design software that can't import data. Exporting content from the web, heard of it?

    By this point they should have API connectors, an InDesign Content Server, and tons of data import options... and a Wizard, why not.

    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
    New Here ,
    Mar 05, 2023 Mar 05, 2023

    Copy link to clipboard

    Copied

    LATEST

    Numbers is an example of a product that correctly interprets a newline as part of the field contents, if it occurs within quote in the CSV.  InDesign could do the same thing.

    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 ,
    Mar 21, 2011 Mar 21, 2011

    Copy link to clipboard

    Copied

    Needless? Sorry, but all evidence is to the contrary. You have clients to serve so it was anything but needless.

    Bob

    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