Having InDesign Data Merge issues

[prepressRob]

Not my usual forte, but I've done data merges using Word quite a bit over the years and a few times using Indd. Currently have a large business card order, Indd files, and an xlsx spreadsheet. Am I correct that out of the box Indd will only accept csv or tabbed text files for data merge? I can find no find no info on this searching the help files.

Specific issue I'm having is that in zip codes that begin with zero, the zero falls off. It's in the original xlsx file (the field is formatted as text, not numeric), but when exporting to csv or txt it disappears. Perhaps further complicating the issue, the data file has to be worked on PC (old version on the Mac won't open it), and Indd file is Mac. Currently using InDD CS5, have and tried CS5.5 but that had even more issues.

Any thought on how to sort this are greatly appreciated. Short of that, I'm thinking I'll have to go ahead and export the PDF, then add the missing zeros record by record in Acrobat/PitStop. I've sorted them and there are 414 total - really not looking forward to that a whole lot . . .

 

[Stephen Marsh]

Yes, comma or tab separated values. I personally would use Libre Office on the Mac (a free port of OpenOffice). I have had issues with MS Excel .csv exports and generally gave up and started to use OpenOffice/NeoOffice/LibreOffice.

I would then save as to .csv using the edit filter settings of Unicode or Unicode UTF-8 (sorry, I can't remember which encoding works best, I think just plain unicode).

Double check that the .csv does not alter any of the numbers.

Another thought, a "intelligent" GREP search/replace in a text editor like TextWrangler or in inDesign may be able to add in the missing characters...however I can't help with the code.


Good luck,

Stephen Marsh

 

[rich apollo]

An xlsx file is XML. INDD has the ability to work with XML data, but it's a different vehicle than using csv for data merge.

 

[rich apollo]

You can manipulate the XML data in a trial version of Oxygen or with SernaFree. Both are XML editors.

The issues I've seen with the importing of text files is the character encoding. Excel, by default, uses ISO-8859-1 while I'm pretty sure that INDD is expecting UTF-8.

 

[Alith7]

Just ran into this actually.

first, to combat the dropped zeros, reset the column as "Text" in the excel file before saving as .csv. if that still fails, try setting as "special-zip code", or as "custom" and the text box type 5-Zeros to designate as 5 numeric positions. sometimes, when you open the Excel file it will "lose" column formatting and reapplying, even if everything looks fine, fixes the problem. gotta love Microsoft!

***Make sure that you save both a csv AND an excel file!! if you have to edit the file after you generate the CSV, it will drop all the leading zeros and special formatting, so, pretty much save the csv file and then DON'T TOUCH IT other than to link in indesign.


There is one other issue that you will probably run into and that is the coding format from PC to Mac. watch your special characters and punctuation, they may default to gibberish.

as a last resort, if you want to send me the file I'll send you back a CSV file that will work.

 

[DCurry]

I echo Alith7's advice, and I'll add this:

After you fix the column in Alith7's advice and save the Excel as .csv, to check to see if it works force the .csv to open with Word to see if it is fixed. For some reason, sometimes you try to open the .csv with Excel to check it but it will still look wrong, even if it is correct. Forcing it open in Word shows you reality.

 

[Alith7]

thanks DCurry.
can I add to yours?

you can also open the CSV in notepad or some such simple text edit program also.

 

[DCurry]

thanks DCurry.
you can also open the CSV in notepad or some such simple text edit program also.

Excellent point. Any text editor will do.