A

Anonymous

Question

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

Forum|Forum|16 years ago
April 7, 2009
9 replies
74756 views

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?

How to

T

Theodore25903517xi3l

New Participant

Hello - I'm just bumping this thread on the off-chance that it gets seen by a nice friendly community manager, and that they can make sure folks at Adobe know that this csv import bug still needs fixing. If management or a dev try and tell you this isn't a bug, they haven't read the standard itself which is only 6 short pages long (and only 3 that they would have to actually read)! I am a software dev myself, I can tell you that it shouldn't be very difficult; as mentioned, the standard is about as simple as it gets, and there are plenty of existing implementations for csv importing that are freely available as well 😊 Data Merge is a tool that would benefit from so much from just a little time and love! But, the issues (and lack of interest in fixing them) do make other products look quite appealing...

Blanchard143

New Participant

In case it ends up helping anyone– I recently discovered a workaround allowing you to break lines directly from the csv with a little tweaking:

https://indesignsecrets.com/controlling-line-breaks-with-data-merge.php

Of course it’s got some limitations, but you don’t have to run a find / replace or break the link to the source.

Phusckidus

New Participant

2016, Indesign does not seem to support this yet, still sad.

S

synstefana

New Participant

Actually there is solution for this:

"...select Content Placement Options from the Data Merge panel menu and turn on the Remove Blank Lines for Empty Fields checkbox."

http://indesignsecrets.com/rid-empty-lines-data-merge.php

K

Kopy-Rite

Participating Frequently

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.

Peter Spier

Community Expert

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.

A

alanomaly

Inspiring

If you need line breaks in order to avoid empty lines taking up space - for example, if entries take up a line each and are sometimes blank, and these lines shouldn't show, one simpler workaround is to have the paragraph style set to have leading of 0, then add a GREP style matching \w that adds a character style that has leading.

This means that entries with no letters or numbers in them take up no space in your layout - useful for vertically aligned or auto sized text boxes.

This is no help for numbered paragraphs, unfortunately, but it's easier than find-change hackery for other uses.

-----------

(off-topic: it's not just Kopy-Rite who is unhappy that much-discussed limitations like this exist for years and years and years without any attempt to improve or fix them. On other threads on this topic, you'll find many suggestions of other data merge tools that support line breaks and are genuinely professional quality, not just priced as if they were. Also, having regulars flame any criticism of Adobe just looks ugly, undignified and defensive: especially when they seem to deliberately miss the point ("don’t ask me to be happy to pay $600 for a file conversion utility"). The dismissive put-down about "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" looks pale and naive now that Adobe's entire business model for CS/CC applications has switched to rolling forced paid upgrades with monthly billing that is compulsory regardless of whether there are any new few features with need or value. Turns out it wasn't such an unreasonable concern after all)

K

Kopy-Rite

Participating Frequently

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.

Peter Spier

Community Expert

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.

K

Kira28702590o70s

New Participant

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

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.

K

Kopy-Rite

Participating Frequently

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.

Peter Spier

Community Expert

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?

A

Anonymous

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.

d0ublej0sh

Participating Frequently

I did the same thing...

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}"
Do your data merge into InDesign.
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.

maccoutinho

Participating Frequently

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?

Peter Spier

Community Expert

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.

Bob_Bringhurst_-_Adobe

Community Manager

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.

A

Anonymous

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded