CSV & Carriage returns issues

Any datamerge user has faced this issue : one “cell” has a carriage return (Ex: a two lines adress) but while datamerging with InDesign, everything goes wrong ! Informations are misused or even disappearing. But all your data is right within your table software. A single carriage return has corrupted the the data processing of the CSV/TXT File by InDesign. Small issue but dramatic consequences.

What’s wrong ?

Let’s take this simple table. For our “adress” field, we typed in three lines of text. Let’s drop some tags for the demo :

As we require a preview, we can see that something is definitively wrong.

The “society” field doesn’t show up within our layout. Even worse, on the next input, it’s gone mad :

For most of you, we are just stating the obvious. Once that said, we wanted to offer a deeper explanation of the issue. InDesign does wrong because the carriage returns hosted within the cell were considered as a jump towards the next record. The string next to the carriage return is now considered as the first cell of the next row.

A fake good idea

We thought we found a solution to this issue thanks to a nice algorithm. A really simple axiom : get the number of columns and check that every rows enumerate the right amount of cells by merging lines and temporarly replace problematic carriage returns with a unique string to be replaced later within InDesign.

In the above figure, the first row only hosts two “cells” when there should be three. We then merge the rows 1 & 2 to to get a three cells row. That’s the main concept. While the first trials were very enjoying, we had to face the awful truth. Our cooking won’t work everytime. Indeed, given how data would be found, it was almost impossible to reconstruct all the lines accurately.

Considering the red row cell, can we tell if it’s related to the orange cell or a separate cell within the row ? No we can’t !!

Conclusion

So it seems impossible to deal with carriage returns within a CSV/TXT file because sooner or later, there will be some scenario where only the human brain can tell. That’s why InDesign can’t take them into account and so many editors working with CSV. And we won’t do better.

What else then ? If you want to stick to datamerge, you will have to replace carriage returns within your source tables ( Excel files… ). Just check that a carriage return within your file is nothing but a jump towards a next row. You can use an unique string like ##RC## or any other pattern you won’t find within your data.

Another solution would be to prefer the XML format which can deal with carriage returns. But if you do use another technique, let us know.

Loic