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
It looks like its the first 4 lines of code. I am using Excel 2007, Exporting from Filemaker the csv file. It is making these ? files in the csv files. You have to clear the first four lines of code of the question mark files maybe a few more lines to get it to see the whole document and not pop up with the error that it doesnt recognize the document.
Thanks for this — on CS6 it is still a problem. And whatever it is, once I rebuilt my template, using the data source would corrupt the file. Everything imported beautifully when I exported to PDF, but when I merged file the data gets doubled up. I will try using this trick to see if that avoids the problem, but first I have to rebuild the template again.
I finally solved my immediate deadline issue by testing dat linker. I think it is more stable for these complex templates I’m working on. Your write ups and your commenters have helped me immensely this week. Thank you.
Been enjoying zapping gremlins in this and other jobs ever said you mentioned this. I feel the power!
Errata: DataLinker by teacup.
Was trying to use Data Merge with cyrillic data using Excel for Windows and got the same error. The solution was to open resulting csv in Notepad++ and to convert internal file encoding to UTF-8 WITHOUT BOM.
Also data file name should be in Latin.
I am having this warning with arabic text, I tried your solution. I still have gibberish coming in but now its mostly symbols rather than letters and numbers. (so i guess im getting closer?) any suggestions?
Is there a bug in InDesign CC 2014? I have confirmed I’m doing multiple record layout correctly in three different tutorials but my preview continues to look different from my actual merged document!
YES!!!! ID secrets saves the day again. Thank you so much!
Seriously saved me. I was pulling my hair out. I do think this is a Mac/Excel data issue. Thank you David. You’re saving people on this same issue nearly 8 years later…: )
If you use Chinese keyboard to type English letter, it will create gremlins:) maybe other keyboards such as french or german has the same issue.
This problem had me loosing my mind. Thank you so much for posting the fix.