Issues with Data Merge and non-latin alphabets

[arnoenzo]

I have a document containing of one paragraph of text in 26 languages, among which Russian, and Greek. I placed the unformatted text in a clean Excel file and saved it from there as a tab delimited txt file (I need it to be tab delimited, because of specific layout settings in InDesign.

The texts in Russian and Greek show without issues in Excel, and the font set I'm using in InDesign has the full range of characters for 26 languages. When I straight copy-paste the text in InDesign there are no issues.

When I import the tab delimited txt file in InDesign the error message tells me that the document contains characters that cannot be encoded, and in the preview all the text, except for the ones in non-latin alphabet (they appear in all underscores). The same happens with a number of (but not all) characters with accents on or under them.

I'd appreciate any tips or pointers to solve the issue, if anyone knows.
Thanks!

[SychevKA]

hi,
if txt file is imported by .place method, its first line should be <UNICODE-WIN>, at least for Russian

[arnoenzo]

The text file is placed by importing the data source (data merge). Each paragraph is called with tne <<reference>> code in the proper places in the document.

[SychevKA]

sorry, i missed excel conversation ))
try to save excel to unicode text, you will not lose tab delimiter,
also you can share your Excel file, maybe someone will test it

[Jens Trost]

I don't know how Excel saves the text files, but my guess is you need UTF-16 encoded files to have the full range of characters.
In LibreOffice there's a setting in the Save as dialog..

[Laubender]

Hi Arno,

UTF-16 or UCS-2 encoding of the data source is necessary, yes! Jens is definitely right on this.

But even with UTF-16 something could go wrong. Not because you are doing something wrong, but InDesign's data merge feature has a bug. Lately we had the strange case with a possible bug merging QR Codes that contain Czech characters like:

Ř

LATIN CAPITAL LETTER R WITH CARON

See this thread:

DataMerge with special characters and QR code
badbernburg, Sep 25, 2020
https://community.adobe.com/t5/indesign/datamerge-with-special-characters-and-qr-code/td-p/11460803

FWIW: Did not here anything new by the OP badbernburg.

Don't think this issue is resolved.

Regards,
Uwe Laubender

( ACP )

[arnoenzo]

Thank you everyone for your response.
Regarding the UTF-16or UCS-2 encoding... I don't have that option when I save the file as tab delimited text, so I'm assuming it defaults to UTF-8.
Would any of you know an alternative way to save the file with which I have the full range of characters, but which also honours the column division in Excel, so that I can data merge each column in a place of my choosing?

[Laubender]

Hi Arno,

what's your operating system?

When on Mac OS X Apple's TextEdit app can save a text file to UTF-16.

When on Windows 10, Notepad++ can convert and save a text file to UCS-2 LE BOM.

Hm, even InDesign itself can export selected text to Unicode UTF-16.

From my German InDesign on Windows 10:

Regards,
Uwe Laubender

( ACP )

[arnoenzo]

The problem is not in InDesign, I fear, nor in the text editor. I can set the text editor to UTF-16, but when I import the text into Excel, it converts back to UTF-8. It seems there is no option in the Save As panel, nor anywhere in the preferences that I can find, where I can set a default for UTF-16.
I need to save the text out of Excel as a tab delimited txt file (where the tabs represent each colum I made in Excel for the different paragraphs of information.

I'm on a Mac running Sierra, the latest CC and Excel 16.

[Laubender]

Why is this a problem?

You could export your text from Excel to e.g. InDesign and from there to UTF16.

Regards,
Uwe Laubender

( ACP )

[arnoenzo]

This is a problem, because the text file that I save as the tab delimited text doesn't have the non-latin languages in it anymore. And what's not there, you cannot bring back when you import it and change the setting after the fact.

I need the tab delimited text file to be in UTF-16, but it seems there's no option to do that in Excel. At least not in this version I have...

[Jens Trost]

This seems now more than an Excel problem than an InDesign problem... maybe in an Excel community you find better advice on how to create a UTF-16 file out of Excel (if even possible).

If Excel doesn't support UTF-16 you could try LibreOffice (it's free and open source) since as mentioned before they have an option with which encoding to save:

(! Screenshot of LibreOffice NOT Excel !)

I would just use a different programm for creating the csv than coping things around and taking unecessary additional steps.

[Laubender]

Try copy/paste from Excel to InDesign.

Regards,
Uwe Laubender

( ACP )

[arnoenzo]

Straight copying the text from Excel to InDesign isn't a problem.

And this is EXACTLY what I don't want to do. I need the data merge to automatically place the contents of a file in different referenced places in my InDesign document.

[Laubender]

I suggested copy/paste from Excel only as an intermediate step to create the data source for the data merge process with UTF16. Nothing more, nothing less…

Regards,
Uwe Laubender

( ACP )

[arnoenzo]

And how would I go about doing that?
I can only save as an InDesign related document, or Export as Rich Text Format or Text Only. That won't work as a data source for data merge when I have 26 different places in the document I have to put the text.

And it kind of defeats the purpose if I have to save 26 small text files.

[Laubender]

Hi Arno,

maybe I do not understand your workflow enough, but couldn't you copy/paste the contents of your Excel file to an empty InDesign text frame so that this gives you text where the data columns are separated with a tab and the rows are separated with a paragraph return? If that is done successfully, simply select the text in InDesign and export your selection to a text file with Unicode UTF16. And that file is the data source file you use for your data merge process.

Regards,
Uwe Laubender

( ACP )

[Test Screen Name · Accepted solution]

There is a lot of discussion here, but I think the point is a very simple one. SInce you need tab delimited UTF-16/UCS-2 from Excel, I suggest you save as "Unicode text (*.TXT)". This works for me.

[daanir2168211]

You can open csv file in any text editor and change encoding there. I use Notepad ++ on Windows. Second option is copy your table in Google spreadsheets and save csv from there, usually works for me.  

[arnoenzo]

I haven't gotten around to testing some of the answers, but I will check shortly if it works when I "just" save the file as a UTF-16 txt file.
If in that file format the division of the columns (tab delimited) doesn't get lost, then I might be good to go.

Will report back.
Thanks to everyone who took the time to respond.

[arnoenzo]

Got around testing the "just" saving as UTF-16 Unicode text, and that seems to be working. It maintains the colomn/row division set in Excel.
I still get an error that some characters couldn't be encoded properly, but I've checked the "problematic" texts as good as I could, and could find no errors.

Once more thank you to everone who took the time to respond.

[Ika33247517zo1r]

The way I use Cyrillic data to merge in InDesign (Windows 10)

Excel: Copy table with data

MSWord:

1. Paste table from Excel *Use destination styles
2. Convert to Cyrillic if not already
3. Convert Table to Text (use Other, comma without space, pic. 1)
4. Save as Plan Text (File conversion Unicode UTF-8, check Insert line breaks CR/LF, pic. 2)

Notepad:

1. Open TXT file saved from Word (special characters should be displayed correctly) and Save as UTF 16 BE or UTF 16 LE (not UTF-8). Usethis text file as data source in InDesign.