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

New Here ,
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

56.4K

Likes

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.

Likes

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 ,
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.

Likes

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 Community Professional ,
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.

Likes

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 ,
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.

Likes

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.

    Likes

    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?

    Likes

    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 Community Professional ,
    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.

    Likes

    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.

    Likes

    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.

    Likes

    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.

    Likes

    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!

    Likes

    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

    Likes

    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.

    Likes

    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 Community Professional ,
    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?

    Likes

    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 Community Professional ,
    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

    Likes

    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.

    Likes

    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

    LATEST

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

     

    Likes

    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.

    Likes

    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 Community Professional ,
    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.

    Likes

    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.

    Likes

    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.

    Likes

    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 Community Professional ,
    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

    Likes

    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

    Whoa… not sure why this turned personal.

    I know this isn’t the place to have this discussion, but there are things I’m not comfortable leaving out there to hang.

    I don’t want to support my customers?  Where on earth did that come from?

    That coffee example was lame; we serve our customers whatever they ask for.  Following that same theme, it’s more like the coffee supplier demanding that we buy a new coffee maker every year by changing the size of the coffee filters.

    We have had CS5 for months, and it was obtained ENTIRELY for the convenience of our customers.

    We are a service bureau/printer, and we need to print what our customers send.  As I said, asking them to save it down is an annoyance for them, and only having a single system with CS5 makes it a production bottleneck for us as more people move to CS5.

    From a production perspective, there are absolutely no productivity increases for us that come with an upgrade, other than we save time by not needing to save down to earlier versions in order to move CS5 files into our perfectly functioning CS4 production environment.

    I’m sure that CS5 has made some designers more productive, but it is really nothing more than an expensive file conversion utility for service bureaus.

    The few files we have received in CS5 can be saved down to CS4 – or even CS3 – where they can be opened and printed on our production systems.  So, quite clearly, there is no technical reason to need CS5 from a production standpoint, excepting the new effects, which as I’ve said, we have not yet encountered.

    In fact, for that very reason, I stand by my assertion that it is a needless expense, given that every file we have received can be saved down to earlier versions and work.  I could see if there was some advanced effect that would not work in earlier versions, but out of the hundreds of customers we service, we have yet to run into that.

    At some point we will receive a file with some new CS5 effect, and THEN the upgrade will truly be necessary for our customers.  But for now, it is entirely for the benefit of Adobe.

    These upgrades have nothing to do with whether the customers need us to have it… so far they don’t, at least not for any technical reason.  It’s all about maximizing Adobe profits; they cleverly tweaks their file formats so that that we are pressured to buy upgrades.

    Look guys… I didn’t start this to pick a fight.  And, I apologized for my snarky comment.  But you couldn’t leave it at that, and instead needed to toss out insults about our commitment to our customers.

    Don’t you see how it’s downright arrogant to suggest that Adobe is somehow entitled to upgrade dollars every 18 to 24 months, and we should be THANKFUL?

    All of that aside, there are certainly cool new features in CS5.  Photoshop is indeed a leap forward, and would probably be worth every penny if we were graphic designers working with photographs.  And I’m happy to pay for one of our upgrades.  The CS5 version of Premier, with it’s integration with Nvidia GPU cards will be a tremendous boost for our video encoding work.

    But don’t ask me to be happy to pay $600 for a file conversion utility.

    Likes

    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 Community Professional ,
    Mar 22, 2011 Mar 22, 2011

    Copy link to clipboard

    Copied

    I'm sorry you think this is personal. It's not.

    When you reopen an old thread to rant about upgrades you have to expect some reaction. Some of us are a little tired of hearing the same nonsense about Adobe having some major conspiracy to extort money from users through forced upgrades. You upgrade because you either need to or find value in doing so, neither of which is under Adobe's control. Sounds like your clients find value and you have need. Adobe issues new versions of the programs because they are trying to offer new features or improvements (and not all new features or improvements are seen as valuable or better by all users -- there's plenty in CS5, for example, that I find totally useless because I don't do interactive publication, but I know that's the direction the industry is moving) and to make some money to pay their employees and stockholders. The business model is that you do that through paid upgrades. If they don't make some money from time to time, nothing will get any better and their customers will start to look elsewhere for what they want.

    As a service bureau it's even more important for you to have the correct version of ID to print your customer's files than it would be if you were sharing for editing. If you've been backsaving through idml/inx and printing, I'd say you've been INCREDIBLY lucky. There are differences in the text engines from version to version that cause all text to recompose when an idml or inx file is opened in a previous version, and it's not at all unusual for that to result in different line endings and overset text -- not something you'd want to have going to press. I suspect it's cheaper to upgrade regularly, even if you don't like it, than it is to eat a job that came out wrong.

    It's not just our industry that needs new tools on a regular basis. I think mechanics have the same complaint about needing to upgrade both equipment and repair databases every year, and I'm sure there are others. You obviously understand that failure to keep up in a service business is a quick road to downsizing the business. If you don't need to do any editing at all on these files, then maybe you wouldn't find any productivity enhancements, but then, if the files don't require any editing, why not ask the clients to provide PDF? With PDFs you wouldn't need ID at all.

    Likes

    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
      • 1
      • 2