Data Merge not working - not with .csv, .txt; not in CS5 or CC2018; not with Mac or PC. Help!

[zacke43977993]

Hi Adobe community,

I'm dying here. My team is at at its absolute all-hands-on-deck busiest time of the year and another team needs me to jump in and help them create 80 certificates ASAP. Problem is, the certificate templates I'm inheriting and the spreadsheets I was provided aren't working with indd's Data Merge feature. In my previous job I'd performed dozens of data merges - and I've never run into a problem like this.

First of all, according to every help forum and Adobe InDesign guide I've found on the Internet, and three other CC users, I'm doing everything right.

Here's what's happening:

• The spreadsheet simply won't connect with InDesign's Data Merge. (i.e., "Select Data Source..." doesn't recognize the spreadsheet).
• I had an indd file with certificate template. This was created using InDesign CS5.
• This template had a "missing plugin" I was unable to find locally or anywhere on Google: 37xmltw.indesignplugin
• When I go to Data Merge and select the file, one of two things will happen:
  • I select the file and nothing happens. The Data Merge screen maintains the 1-2-3 step instructions like I didn't do anything.
  • I receive an error saying "the data source can not be opened. Confirm the file exists and that you have the rights to open it and select open data source again"
• The data files I'm using are .csv. I even saved as .txt and the same issues are arising.

Here's what I've attempted:

• I have re-created and re-packaged the certificate templates with a simple copy/paste of the Master & page content in CC2018 to avoid the missing plugin issue. These updated files don't support the Data Merge either, which makes me think this is a spreadsheet issue.
• I have saved & re-saved the spreadsheets in .csv (comma delimited), .csv (unicode), .txt (tab-delimited). Nothing worked.
• I have copied JUST the content in these spreadsheets (not the whole spreadsheet, not the blank rows or columns beyond the content), and pasted JUST the values into a blank spreadsheet, and saved that as .csv and .txt. Nothing is working.
• I use a PC normally, but brought my MacBook in today to see if I could get it to work. Not working - same exact issue!

I'm at a loss with this one. The only other option is to manually enter the spreadsheet data certificate by certificate, but I really don't have time for it this month! Please help!!

Zack

[Jeffrey_Smith]

Can you share the source file?

1. CSV needs to be saved with a comma delimiter. You can open .csv file in a text editor to verify what character was used for the delimiter.

2. You stated .csv (unicode), is it UTF-8 or UTF-16 ?  Try UTF-8, and this applies to either .csv or .txt

3. If the source has no special characters, and UTF-8 doesn't work, try export/save with no encoding.

[zacke43977993]

Hi Jeffrey,

Thank you for your response! I tried all three steps but it's still not working. I wish I could share the source file but it contains sensitive information. .txt didn't work, UTF-8 didn't work; migrating the folder from my Dropbox folder to my local desktop didn't work. I'm worried I'll need to do these manually now. If that's the case, do you think exporting to PDF and having a colleague help update the merge fields is better than doing it all in InDesign by myself?

[Colin Flashman]

Is this an issue data merging ANY file, or one particular file? That is, if you set up a brand new document and prepare a data merge using a very basic database, does this work or are there issues?

If data merge DOES work, what happens if - in a new document - you try the data merge with the data file that is being stubborn? What happens? Do any of the screenshots from this page: https://indesignsecrets.com/troubleshooting-data-merge-errors.php  appear?

If the data DOES come into a new file but not the trouble file, can you make a PDF of the indesign file using only the base elements in the original file, put the PDF into a new file as a picture, and then try the merge again?

[zacke43977993]

Thank you Colin & Jeffrey! In looking into your solutions, I finally noticed ONE of the fields in the top row had a capital letter wedged in it. This is why IN was rejecting the spreadsheet in every format! I updated it and triple-checked all the fields in the top row had lowercase letters - and then the merge took 5 seconds.

[lindsayb24430200]

I have been struggling with the same issue for a while now too and I tried EVERYTHING.

My issue seemed to be in unused columns and rows which I don't know how to get rid of in my Excel spreadsheet.

What worked for me was opening the .csv file in TextWrangler and manually deleting all the extra commas from the end of each row (there were like 8 of them I think). Once I deleted those and resaved my .csv file and then pointed to it in InDesign, my data loaded no problem.

I'm going to try to figure out how to automatically get rid of everything outside my selected rows and columns in Excel for next time.

[Jeffrey_Smith]

I'm going to try to figure out how to automatically get rid of everything outside my selected rows and columns in Excel for next time.

The method I use (for this issue) is to select the rows and columns of data, copy and paste into new excel document.

[Jdules]

InDesign reads excels like code so you're trying to figure out what is wrong with the "code". Save a copy of your excel and do the following.

• Double-check links
  • Image link columns should have an apostrophe before the folder link. Example: '@Image
• Remove special characters
  • Remove unnecessary characters like colons, quotes, anything that might bug the code.
• If none of these work start trouble shooting rows and columns by putting x's as a placeholder for cells and deleting all the information. Put numbers where the variables are. 
  • Doing this will narrow down if its something wrong with the information or if the excel doc/InDesign doc is broken. 

This should help figure out where the problem is! Most times for myself, it's something with the variable row having a blank or the image link didn't have an apostrophe. Hope this helps!

[t_conteh]

I have just learned to use this, and was having issues importing data from CSV files overall. What worked for me was saving the excel file as a "Macintosh Comma Separated (.csv)" file. Try that.

To give some background, I've tried rich text, TXT (which worked except for emojis) two other .csv formats, and directly from ecxel.

[kwanbluegem]

this works ! thanks!

[twiddle]

Confirmed - it works.

[twiddle]

confirmed - it works! Thx

[Galileo-n-OKC]

I ran in to this same problem.  I could not get my data file's Headers to show up in the data merge window in InDesign for a file generated out of PatronManager (which is sits on the SalesForce platform). Merges would work on archived data .csv files that I had used in the past.  But, not this newest one. After much trial and error (and reading the comments here), I deduced that it had to have something to do with the Header row in the orginal Excel file.  So, I first copy-n-pasted all the data below the header row to a fresh, new Excel file leaving the header row blank.  Then, I manually typed in new headers for each column in the spreadsheet.  All caps, no special characters.  Then, saved a new .csv file.  And, bingo!  The import to InDesign worked!  So, there must have been some wonky coding in the Header row that was created in the original SalesForce file.  Which, btw/, had been touched by a couple of staff members as they did their part to massage the data before it hit my Mac (oh, yes, I'm on a MacBook Pro).  So, the file could have picked up some wonky coding in the open-n-saves prior to me.  Anyway, my solution was to create a fresh header, save a new Mac .csv file and the merge worked fine.  Hope this sheds some light for others.

[Sam77Jackson]

For me, it was colons in the field names. Took out, worked fine. Found it because of the input in this forum. Thanks!