Data Merge CSV file format problem

[Beado1]

Hi,

I'm using InDesign CC on a Windows 10 PC, and using Excel 2016 to generate data for a Data Merge operation in InDesign.

In amongst the myriad options for saving the data, Excel provides me with these options for saving files into a CSV format:

• CSV UTF-8 (Comma delimited) (*.csv)
• CSV (Comma delimited) (*.csv)
• CSV (Macintosh) (*.csv)
• CSV (MS-DOS) (*.csv)

Depending on which of those options I use I am finding either the data becomes altered by the CSV format, or InDesign rejects the format.

I have some cells which have 'special' characters such as the degrees sign '°', or a plus/minus sign '±' (e.g. the cell might have 36° or ±15° or ¼). There are many such symbols I use, and whilst sometimes I add these via the Character Map function in System Accessories, but most usually via the keyboard (for instance, a key combination such as 'Alt+0188' creates a ¼ symbol on a PC).

The CSV UTF-8 file retains this 'special' character data, but InDesign rejects the file and tells me "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 type". If I use the CSV or CSV (MS-DOS) format the special characters get changed (e.g. '°' becomes 'Ø'). I haven't tried the Macintosh version yet, however I'm not that keen to try it just in case I'm introducing weird issues with a Mac type file on a PC environment.

Any suggestions?

[Alexandre Becquet]

Hi, for my part the best solution that I find and that works very well is the TXT file export with a separation by commas.

I have always had problems exporting with Excel and the software versions do not respond in the same way.

Maybe there are other solutions but for me it works perfectly.

[SJRiegel · Accepted solution]

I've gotten the best results using a tab-delimited text file.

[John Mensinger]

  wrote

I've gotten the best results using a tab-delimited text file.

That.^

[Beado1]

Hi.

Yes, I thought about it from a different angle and figured that the issue was really in the export of the data where the info was being interpreted in a way that I didn't want it to.

I searched Excel forums and found that if you save the spreadsheet of data as an *.xls file, not an *.xlsx file, and then export to a *.csv (Comma Delimited) file then there was no alteration of the special characters and of course InDesign happily accepted the file.

I have, as you suggested, tried a tab delimited text file export from the *.xlsx file, and that keeps the special characters, so your answer is correct.

It works for me here; I'm not used to outputting tab delimited files as I have a WordPress site that I have to export out spreadsheet data in CSV format, so I lean that way, but I have the same issue on that side of things. Now at least I have found a way to export out to a CSV file that doesn't corrupt the information! Win - Win!! Thanks to all who responded!

[easygoing_Charm5FC8 · Accepted solution]

Thank you Beado1!!!! Saving as .xls first finally did the trick for me after spending 2 hours troubleshooting.

[intuitive_Dream9127]

Just jumping in here with a really silly solution I just found with this same issue.

I tried all the fixes above, nothing worked. I then closed the CSV document I was working on, tried it again and PRESTO! It worked.