How to programmatically Paste CSV text into Excel and not just go into one cell? (C++)

Question

I have a list/grid in an MFC (c++) application. I would like the user to be able to copy and paste the data into a spreadsheet.

I've placed the data in the clipboard and the text makes it to the clipboard ok and I can paste it to notepad or Word or Excel, but Excel does not interpret the comma separated value-ness of my clipboard content - so it just goes into one cell/one column - rather than doing what I had hoped.

I hope there is something simple I am missing.

Any suggestions to get this to work?

I am not quite ready to make this a drag/drop source (which is in the task list)

EDIT:

I have it working - commas can't be used - must be tab separators

But now there is a new problem:

The issue now is that if I paste to excel using '\n' as line separator it looks good in excel but not in notepad.

If I make it "\r\n" then notepad looks correct but excel then has blank lines.

Any suggestions on how to make both consistent?

Answer 1

I used this question and tabs seem to work - but csv still does not

How to paste CSV data to Windows Clipboard with C#

Answer 2

Well, here's my two cents.. It seems a tab character in an NSString, such as @"123\t456" would tell Numbers and OpenOffice spreadsheets, that 456 goes into the next cell of the same row.

Likewise, a newline character \n, or a carriage return \r, would put follow-up data on a new row, for example @"123\n456" or @"123\r456" would put 456 in the next row (starting with the first column from the left).

Indeed, TextEdit does not interpret the newline (\n) or carriage return (\r). (OpenOffice text documents do.) However, providing the pasteboard with an array of strings (be it NSStringPBoardType, or NSRTFPBoardType, or whatever), puts the different strings on seperate rows, both in a spreadsheet and in TextEdit.

Also, a tab (\t) shows up as a tab in TextEdit, which you can then manipulate in the toolbar, to get a nice layout of your data.