Zap Gremlins in Problem Data Merge Files
May 6th, 2008I was having terrible troubles with Data Merge last week because I was getting this message about “the data source file you selected either has no records or is not a supported file format” every time I tried to choose the tab-delimited file that contained my data:
This was just a simple file exported from Excel! I opened the file back up in Excel and resaved as a csv (comma delimited). Same problem. I copied and pasted the data. Same problem. I tried resaving with Unicode vs. Mac vs. PC encoding. Same problem. I banged my head against the table a few times. Same problem.
Then I tried copying just a couple of lines from to a new file. It worked! After that, I quickly realized that some of the 200+ records in the file had weird, mysterious characters in them. I still don’t know what they are. But they’re in there, and InDesign is not happy about it.
So I opened the txt file in my favorite free text editor on the Mac, TextWrangler, selected all the text, and chose Text > Zap Gremlins. I love that command. All the bad stuff disappeared (I guess), because when I resaved, InDesign could suddenly import my file. (Can anyone suggest a great text editor with this kind of feature on Windows?)
It’s too bad when alerts such as the one I was getting are misleading. I mean, the file obviously had records, and the file format was supported. The alert should say, “I don’t know what the heck is in that file, but I can’t deal with it.” I hope my head-banging will mean that others that encounter this in the future won’t have to take as much Ibuprofen.
Hmm, interesting. But I can’t recall I’ve ever encountered any plain-text import problems in InDesign on Windows — so maybe these zap-needing gremlins are Mac-only critters? If you still have your original gremlin-file, how about importing it into ID on Windows? (Or you could email me the file, if it’s not classified info, and I’ll give it a quick try?)
The gremlins hate bright light.
Don’t get them wet.
And never, under any circumstances, ever feed them after midnight.
could they have been perhaps non-latin characters? I’d be interested in looking at the encoding of such a file. I know with my database workings that one or two weird characters can spoil a lot of work if you’re not aware of them.
Is it possible the Excel file had binary (gremlins) in the file before it was exported. I use VEDIT to edit my text files and I can see the offnding characters evenif they are binary (non-printing) characters. I am will to review the source files if it would help.
Yes, I’m sure there must have been odd characters in the Excel file before it was exported. I didn’t make the Excel doc; it was just sent to me. I don’t really need help in tracking it down, now that I know how to strip the file of problems. I just wanted to share my trials and tribulations (and the solution I found), as — if there is one thing I’ve learned along the way — if one person has a weird problem like this, then there must be others who will run into it sooner or later.
David, great info. I’ve never experienced this problem, but…when I do, this advice will help me save some hair.
Fritz, except for midnight, its alway after midnight!
I too have had this problem. The file was for simple numbering (51,000 to 52,000). I had originally made the ID file in CS2 with no problems. When I reused the file in CS3 I then received the errors. If I reopened in CS2 problems went away. Only when I created a new records file in Excel was I able to get things to work correctly with CS3. (Consequently, I deleted all old Excel files, so I can’t try the TextWrangler approach.) Yes, I am on a Mac as well.