Data merge not working with CSV

[Plantor]

Hey, 

When I try to link a spreadsheet with an inDesign document using the Data merge -> Select Data Source function and selecting a CSV file exported from an excel spreadsheet, I get the following error message: 

"The data source file you selected either has no records or is not a supported file format. Please fix the file, select a file that contains records, or select a supported file format."

I have looked at the other troubleshooting threads, and none of them solved the issue. 

[Mike Bro]

Hello Plantor,

Re: I have looked at the other troubleshooting threads, and none of them solved the issue.

What have to tried to fix the issue....is it possible for you to post the .csv file for troubleshooting purposes?

Regards,

Mike

[Plantor]

I tried using a tab-delimited text file, but I got the same error message. Also tried opening the csv file in a text editing program and resaving as csv, did not work either, got the same error message. Why is this feature being advertised if it's not working properly? 

I uploaded both the excel file and the csv file here:

https://easyupload.io/m/9xnapx

[Mike Bro]

Hello,

I opened the Cards-Data.xlsx file and saved a .csv file that works using the below option,  I'm on a Mac running InDesign 2020...... How are you saving the .csv file?

I uploaded the .csv file that works here: https://easyupload.io/diuwc6

Regards,

Mike

[Dave Creamer of IDEAS · Accepted solution]

In Excel, make sure you save it as CSV (Comma delimited) and not CSV UTF-8 (Comma delimited).

If that doesn't work, try the CSV (Macintosh) or CSV (MS-DOS) formats.

[Plantor]

Oh okay, I got it. The problem was the CSV file used colons instead of commas. I simply made a txt file on the Notepad, and retyped the text using commas. This way I could use DataMerge in InDesign. I still have to figure out how to export to comma-based CSV, but at least now I have a workaround. Thank you all for the help. 

[Dave Creamer of IDEAS]

It's probably due to your location or language settings in Excel. 

On Windows:

1. Click on the Start menu.
2. Type control Region.
3. Select Additional settings in the pop-up window.
4. Change the List separator to comma.
5. Click Apply.
6. Hit OK.

[Dave Creamer of IDEAS]

Without knowing what you tried, here is something I've done...

Open the .csv file in a text editing program* and do a save-as, keeping the .csv extension. 

*Some programs can save as text or RTF--make sure the format stays as text.

[Serena5EAC]

Make sure your Excel file is closed. I went through all the below suggestions and it was as simple as closing the document.

[Dave Creamer of IDEAS]

Good point, but that is a different issue. You will get a different dialog box than the one the OP posted about.

[Real Deal Branding]

@Adobe This is 5 years ago. You still can't merge a CSV file with colons in it. This is lazy. Please update your software.

[Robert at ID-Tasker]

@Adobe This is 5 years ago. You still can't merge a CSV file with colons in it. This is lazy. Please update your software.

By @Real Deal Branding

What exactly is the problem? How your CSV file looks like? 

[Real Deal Branding]

Below is an example CSV file. If, within a cell, the text incldues a colon (e.g. Fr: Dinner/Sa: Breakfast & Lunch), and you try selecting it as the source for your Data Merge, it won't load. It doesn't even show an error. It just does nothing. But if you remove the colons, it works.

First Name,Last Name,Church,Church City,Workshop 1,Workshop 2,Optional Craft,Workshop 3,Workshop 4,Retreat Package,F: Breakfast,Friday Lunch
Jane,Tanner,Calvary Bible,Mosinee,Opting out,Opting out,Opting out,Fearfully and Wonderfully Made,Opting out,Fr: Dinner/Sa: Breakfast & Lunch,,Fr: Lunch
Amber,Hartson,Grace Baptist,Chippewa Falls,Marriage,When the Pot Shatters,Opting out,Helping Your Children,Fearfully and Wonderfully Made,Fr: Dinner/Sa: Breakfast & Lunch,,Fr: Lunch
Charlotte,Mitchell,Calvary Baptist,Watertown,Personal Testimony,When the Pot Shatters,Opting out,Prayer 101,Opting out,Fr: Dinner/Sa: Breakfast & Lunch,,Fr: Lunch

[Robert at ID-Tasker]

@Real Deal Branding 

Yeah, ":" isn't allowed in the HEADER of the CSV file - but you can have it in the DATA:

[Real Deal Branding]

Thank you! Very helpful! So, modifying my original comment, they ought to have an error message when this doesn't work rather than it just not doing anything, and leaivng their users to hunt around the internet. 

[Robert at ID-Tasker]

Thank you! Very helpful! So, modifying my original comment, they ought to have an error message when this doesn't work rather than it just not doing anything, and leaivng their users to hunt around the internet. 

By @Real Deal Branding

You're welcome. 

Yes, there SHOULD be a warning with an explanation. 

[Colin Flashman]

I see that this thread has split into two issues over time - one about semicolon delimiters; and one about colons in the header row.

I've written articles about both phenomena before.

The first being comma/semicolon delimiters swapping from Excel and why I prefer UTF-16: https://colecandoo.com/2022/05/16/to-be-or-csv-that-is-the-delimiter/

The second (written for creativepro) features a troubleshooting guide that - for a long time - didn't appear in Adobe's data merge help... at least until I'd written the article: https://creativepro.com/troubleshooting-data-merge-errors/