CSV file not recognized in InDesign Data merge

[Caryn5CC3]

Good morning,

I've spent time going through the forum here with issues pertaining to the Indesign Data Merge feature not working properly. And, I have tried just about all the suggestions I could find. I normally save the file as Comma Separated Values. So I tried saving the file as a MacInstosh Comma Separated and MS DOS Comma Separated. I opened the Comma Separated Values file in a text editor too. No results with this. Then I started over from scratch with a new Excel report used to create direct mail imprints. Got rid of any blank columns and saved it as Comma Separated Values file. Same results. Then I created a new InDesign File with new data fields. And this didn't work either.

In all these attempts, the CSV file is there, but when I go to select it and click okay, nothing happens. Please note I perform this task regularly and it's fortunately the first time I've run into this issue. I'm actually working on another project with no issues at all. < scratching my head, wondering... >

OS System is Big Sur 11.7.4, InDesign 18.2.1 and Excel 16.77.1

Attached are a few screenshots.

Thank you in advance for any input to help get the data merge to work properly.

Cheers to Successful Outcomes!
Caryn

[Joel Cherney]

I've been answering this question a lot, over the last few weeks. Perhaps a recent update to Excel changed the default CSV output? Or the 18.5 InDesign update changed the default input format? In either case, you should be able to get your CSV to import succesfully with two minor tweaks to your workflow:

1) Look at the exact kind of file you're saving out of Excel. There are lots of options even in kinds of CSV you can save:

2) Match your CSV export type with the CSV import settings in InDesign:

[MEONHOMES]

I'm experiencing the same issues as the poster, have tried everything they tried as well as what you hav suggested (tho I'm on PC), any ideas?  🙂

[Brad @ Roaring Mouse]

Yes, this seems to be an Excel issue if you export using the first CSV export option in their list. Excel adds 3 characters to the beginning of what should just be purely a text file. In my screen grab it shows as hex code EFBBBF which adds 3 nonsensical characters ahead of the first field (in my sample: "MONTH"). These 3 characters are messing InDesign up as it's not seeing the first field where it should be.

 

As @Joel Cherney mentioned, using ANY of the other CSV export options further down,  does it properly.

Also, if you open your "bad" one in a text editor those 3 characters disappear... and if you simply resave the file, those 3 characters are gone.

I went back to another laptop with an older version of Mac Excel, as well as Excel on Windows, and it does the same thing, so this is not a new problem.

[Joel Cherney]

That three-character hex code is actually a UTF8 BOM ("Byte Order Mark"). It's declaring that it's a UTF8-encoded raw text file. So, I suppose that it could be used successfully as a Data Merge source if you were to pick Unicode instead of ASCII as the Encoding in the Import Options. 

Mind you, I can't be bothered to open up Excel to try. Instead, when I have to massage Excel files into being ready to import into InDesign as Data Merge sources, I'm going to continue to specify the exact kind of CSV I want Excel to make, instead of clicking on the first option and saying "Okay, Excel, gimme some comma-separated values, I don't care how you encode it... dealer's choice, Excel, you do you."

[Brad @ Roaring Mouse]

Thanks for that (re: BOM). I figured it might be some sort of encoding marker.

I read up on the issue a bit and found a discussion about BOM in Excel's CSV exports: (This was from a question whether it's necessary).

Mostly, it seems only some Microsoft products force the use of a BOM, mostly so Excel can read it's own CSV easier, but where most other software doesn't require or expect it in UTF-8.

As per the Unicode Standard: "The Unicode Standard permits the BOM in UTF-8, but does not require or recommend its use."

So, it's either select a different CSV export setting if you are using Excel, or wait until InDesign is designed to recognize BOMs if included.

Interestingly, for s&g, I exported CSV from Numbers at UTF-16 which did included a BOM, but InDesign handled it just fine.

Fun times.

[Laubender]

Hi @Caryn5CC3 ,

this does not apply directly to your case, but there is still another bug with CSV or text delimited source files with InDesign's Data Merge. If a column head reads "table" (without the quotes), InDesign will refuse to load the file. Without any error. As shown here:

Odd bug with Data Merge reading CSV
tommyb81622856, Sep 22, 2023
https://community.adobe.com/t5/indesign-discussions/odd-bug-with-data-merge-reading-csv/td-p/1410603...

Regards,
Uwe Laubender
( Adobe Community Expert )

[MEONHOMES]

Hi Did you ever get a solution?

[Laubender]

Hi @MEONHOMES ,

can you share the head of your CSV or tab delimited text file from the source?

FWIW: characters like : and / are also problematic so InDesign will not show the source without any error message. There may be more characters that enforce this behavior…

Regards,
Uwe Laubender
( Adobe Community Expert )

[MEONHOMES]

You nailed it!  My data had : in it, I removed those columns, tried re-importing and it worked!  Thanks! 

[Robert at ID-Tasker]

@MEONHOMES 

https://community.adobe.com/t5/indesign-discussions/no-fields-panel-for-data-merge/m-p/14752858#M581...

[Laubender]

Hm. I have to correct myself. Just tested a data source file with a column head with character / .

THAT worked without a flaw with InDesign 2024 version 19.5.0  on macOS.

However, the word table as column head did not work.

Also a : in the head.

Will do more tests tomorrow.

Regards,
Uwe Laubender
( Adobe Community Expert )

[Friend in the Print Biz]

Much appreciate your research on this. I did a big merge three weeks ago with no issues, but starting a new one today InDesign wouldn't link the .CSV. Lo and behold there was a colon in one of the column headers. THANK YOU