An Excel add-in that will help you with your InDesign data merge job.
To use “Data Merge” functionality in InDesign it is necessary to import data formatted as a CSV or TXT file. Excel offers these two methods: CSV (Comma delimited) (*.csv) or Text (Tab delimited) (*.txt).
A third useful option is Unicode Text (*.txt).This will also export a tab-delimited text, but this time in Unicode (UTF-16) format.
Unicode format covers all types of foreign characters used in different languages.
Working with Excel to save .txt/.csv tables could lead to three common problems:
- The messed up encoding
Saving the wrong type of encoding because the .csv format only supports ASCII characters; so if you have some diacritical marks such as ç, à, é, or è, you could end up losing them. - The ghost editing
The second problem is a little more subtle. It is normal that your data has rounds of revisions, and each round you “save as” the data to .txt format and import/update the file to InDesign. However, when you save the .txt and it is still open in Excel, a small distraction is enough to trick you into editing the .txt file instead of the original .xlsx, where maybe formulas or filters are used. The result of this process glitch could be the loss of the last part of your data editing. - The “file in use” warning
If you save your data as .txt but do not close Excel, and you try to parse the file with InDesign, you will receive a warning that the file is in use by another application and you will be unable to complete the action.
To avoid these problems, we add a simple Add-In for Excel (both for Mac and Windows), which allows you to export this .txt file while keeping your Excel source file open on the screen. It will export your file in the same folder as the Excel file with the same name as the spreadsheet label. A macro add-in has a new button that lets you always operate in the main .xlsx file and always saves both the .xlsx file and the UTF8/Unicode .txt version. This helps with the problems outlined above, i.e., it ensures consistent “character codes”; reduces “ghost editing”; and cancels the “file is in use by another application” warning.
You can download the add-in from the button above.
Following there are the instructions to install it in five very simple steps.
Import Custom Add-In
To import this Add-in we need to go to the“Developer” Excel tab. If it is not enabled, we go to menu “File > Options”. Now under “Customize Ribbon”, check “Developer” in the right column.
Click OK and now we go to the “Developer” tab. Click on “Add-Ins” and then click on “Browse”.
Choose Export Unicode Text.xlam file downloaded before clicking OK.
Check it and click OK again.
A new tab will appear near the “Developer” tab with a new “Export Unicode txt” button.
On Mac will appear slightly different: the bar is positioned above the Excel screen, but we can move it where we want.
If the Add-Ins tab doesn’t appears, the add-in could have been blocked by the system. To unblock it, close Excel and go to the add-in file you previously downloaded. Right click on its icon and select Properties. From the dialog box check the Unblock option. Open Excel and, if necessary, repeat the addition of the add-in with Developer > Add-Ins, check the Export Unicode txt box and OK.
Final results
Now every time we need to export one of our spreadsheets, we can use this functionality instead of going through the menu “File > Save As”, closing Excel, and reopening the original one process.
