Zap Gremlins in Problem Data Merge Files
I 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.
I had the same problem. In my case it was easy to solve the problem. I deleted 3/4 “empty” columns next to those with info. I started from a new document so it shouldn´t contain anything! I work in office 2008 and CS3. I have noticed Office 2008 contains a lot of mistakes for MAC like language mistakes and automatic repeating of series.
I use Data merge for numbering muliple files on the same sheet.
I’ve got the same problem, and I’m working in Windows XP. Any text editors that can zap gremlins in XP? What does zap gremlins actually do?
@David: My guess is that “strip gremlins” simply strips out the high ascii characters and suspicious control characters. I don’t know of any specific Windows text editors that do this, but I’m sure there are some out there.
Thanks Ive been banging my head also with this one! I knew it was dodgey characters, but it must be hidden/binary characters that are causing the blues as Ive tried deleting all non-web safe characters and that usually covers most character issues.
Thanks for your help with this one. Is there a windows alternative to TextWraggler? Has anyone pointed on out so far? Its a useful function, I will google down the solution. Thanks again for your help. I can now post a tutorial on csv data merge with confidence. Thanks
Hi,
I recall reading something about “zap gremlins” being a function or utility that strips away control characters. I believe this phrase originated on the UNIX platform. One alternative is to use a GREP expression that will keep certain characters. For example:
[^/n/f/t/r -~]
remove all characters that are NOT:
line feed, page break, tab, return, space to tilde inclusive.
If you find something is missing, add it to the above list. I suspect with the introduction of GREP in CS3, this should now work in InDesign.
-Greg
Note: In the expression, I was unable to figure out how to escape the back slash. Therefore, please replace forward slash with back slash.
YOU ROCK!!!!!!!
Thanks so much! I’ve had this problem happen in the past and I’ve had to do a lot of weird dancing to get it to work. Seems like with the Leopard update this started happening in CS3 but it never happened before. Your trick worked! So much easier than before!
I was having trouble with a “The prefix ‘^1′ has not been mapped to any URI” error. I finally found the following solution:
InDesign CS3 does not accept colons in the header line, even if that field is wrapped in quotes. It does accept colons in the data fields. I would avoid/strip special characters in the header fields altogether.
Works: header 1, header 2, header 3
Doesn’t Work: header 1, “header: 2″, header 3
Works: data 1, data 2, data 3
Works: “data: 1″, data 2, data 3