Participant

Answered

Excel csv not recognized in data merge

Forum|Forum|9 years ago
January 9, 2017
10 replies
24060 views

I recently updated my Excel and since then the .csv files won't merge into indesign. The indesign and Excel are both updated. When I go to merge the data none of the data fields show in the data merge window to click and insert into the document. When I close the file and reopen in indesign, it says that "the data source cannot be opened." I am only importing .csv files and have not had this problem before. In fact, I used an older .csv file created with the older version of Excel and it uploads just fine into indesign. Does anyone know what is going on and how to fix this?

Correct answer Pariah Burke

You can also open Excel-generated CSVs in Text Edit and save (no changes, just open and press CMD+S). That removes any extraneous Excel encoding and makes the CSV fully InDesign Data Merge-compatible.

A

Amy_Meyer

Participant

I had this problem as well and just solved by saving as CSV for Mac. (I'm working in Windows.) I just kept going down the list of different types of CSV saves until I found something that worked.

Joel Cherney

Community Expert

Oddly enough, the "import options" checkbox hasn't yet been mentioned in this thread. The main idea here is that InDesign's Data Merge can accept a few different encodings. When you are selecting your data source, there is an "Import Options" checkbox at the bottom of your file selection dialog that allows you to specify the platform and encoding, with a few options.

Similarly, when you are using Excel or Notepad or whatever your tool happens to be, you may or may not have choices in the platform or encoding you are saving to. If you don't know how to save a particular kind of data file, and you don't know how to tell InDesign what kind of data file you have, then yes, you basically have to guess. There are exceptions - if you are a Windows user, then I recall that Notepad++ will tell you the file encoding in the bottom bar. If you are a Mac or Linux user, you can use some command line tools to figure out the encoding of your file.

But it pains me to say a string of posts saying "the file type you posted doesn't work for me, I had to use This Other Encoding." The trick is not to pick the correct encoding from some years-old post, but to pick the right encoding from a drop-down menu, either in the app you are using to generate the file (as it seems you did here), or in the dropdown menu in InDesign's Data Source file selection dialog.

A

Amy_Meyer

Participant

Thanks, Joel. This will be a very important reminder for the next time I have to do this.

L

Luis32546682ullc

Participant

On PC, I found that saving out as the correct CSV type in Excel resolved the issue.

Joel Cherney

Community Expert

I have to point out that you've found a fairly old discussion, but also I have to point out that of all the answers posted here, yours is probably the most correct. It's all too easy to forget (or to never know in the first place!) that a raw text file saved on a Mac is encoded differently from a raw text file on a Windows machine, and that CSV is actually a raw text file inside. I suspect that the original post (way back in 2017) was about an update to Mac Excel that caused default CSV file encoding to default to Windows text file encoding.

Sometimes, however, that Unicode CSV that you've crossed out in your PNG actually is the correct encoding, if the values in your file need to be Unicode-encoded. That's typically whenever you are working with non-Latin characters, or extended Latin characters. So if I need to do a Chinese data merge (or Arabic, or Vietnamese, or anything that isn't plain-vanilla Western European) the probably the Unicode CSV choice is the correct choice.

A

andré_1959

Participant

I just want to add my cent here: Using CSV-UTF8 did not work for me (western europe). Using CSV worked.

elShimmer

Participant

HI found this helpful! My issue was the Single Record selection couldn't be changed to Multiple records. It was just blanked out. Turns out if you have more than one page in the document it only reads as single record. So start with just one page and you will get the option to import multiple records.

If you can't even import the csv tab delimited document to begin with, try opening the document.csv in Notepad and re-save. This should remove all of excels garbage it tags on.

The link below goes into step by step detail. Important rule 1. was my issue. It's too bad indesign can't recognized when you're having issues and bring up important rules, or helpful tips automatically. No, you just try a hudnred different things until something works. Or your forced to copy and paste. Aghh

https://gcostudios.com/designing-and-data-merging-with-multiple-records-in-indesign-cs6/

Pariah Burke

Community Expert

My pleasure. I'm glad it helped.

Pariah Burke

Correct answer

Community Expert

You can also open Excel-generated CSVs in Text Edit and save (no changes, just open and press CMD+S). That removes any extraneous Excel encoding and makes the CSV fully InDesign Data Merge-compatible.

washingtontrust

Participating Frequently

Thank you for the tip - this worked for me!!

Kevin Stohlmeyer

Community Expert

I had the same issue this morning and found that if you first go to "Content Placement Options" in the Data Merge Panel, you can check "Remove Blank Lines for Empty Fields" and it works. I will be filing this as a bug.

macpawel

Participating Frequently

Be sure that you close Excel before import data

You can also try to export data to Tabbed text file from Excel and import as data to inDesign Data Merge

Be Sure text file is closed

Pawel

E

Emily307727554rm3

Participant

I didn't realize you had to make sure excel was closed. I closed it and then it worked right away. Thanks!

macpawel

Participating Frequently

Maybe you done some mistakes with data?

To many variables names, wron variables (header) name, empty column, to many empty rows, even alone Enter character in the END of your document? Please try tu use UTF-16 encoding data

Pawel