Unable to paste CSV format into Excel

Hi,

I am trying to put data onto the clipboard which can be pasted into Excel. I have successfully implemented HTML, Text and XML Spreadsheet but cannot get the CSV format to work.

Even the example provided by MSDN won't work:

string comma = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator;
String csv = "1" + comma + "2" + comma + "3" + Environment.NewLine + "6" + comma + "8" + comma + "3";
byte[] blob = System.Text.Encoding.UTF8.GetBytes(csv);
MemoryStream s = new MemoryStream(blob);
DataObject data = new DataObject();
data.SetData("Csv", s);
Clipboard.SetDataObject(data, true);

When I 'Paste Special' in Excel, the CSV type is listed but pasting gives the following error:

"Microsoft Office Excel cannot paste the data"

It may or may not be important but I have both .NET 1.1 and 2.0 installed on my PC, and am developing in .NET 1.1

Any help would be very much appreciated.

Thanks,

Philip

Answer this question

Unable to paste CSV format into Excel

Wizard of Ogz
Hi Guys,

I figured this out, and the answer is quite commical.

When putting the data onto the clipboard, Microsoft provides a class DataFormats with the static field CommaSeparatedValue. This string equates to "Csv" - which Excel will detect and display in its 'Paste Special' box.

However, using either this field or the string "Csv", I was getting the above errors.

The solution Change to either "CSV" or CommaSeparatedValue.ToUpper() and everything works like a charm!

Spendid! Not sure if this is a bug in either Visual Studio or Excel, but I finally got my code to work so I am happy.

Philip.
Terry Muench
I'm having the exact same problem (with the exact same code) and was thrilled to see a solution. However, I tried this solution and still see the same symptoms.

Any other ideas out there
Ahmed Aloub
Well, just when you thought it was figured out you discover that Excel versions make a difference.

On version 11.8033.6568 (SP2) you cannot use upper case string ("CSV") for the data object format. If you do, you get the "cannot paste" message.

However, on version 11.8033.8036 (SP2), you cannot use the camel-cased string ("Csv") or you get the paste error.

So my final code looks like the following:

Byte[] blob = System.Text.Encoding.Default.GetBytes( "Test,1" );
MemoryStream s = new MemoryStream(blob);
DataObject data = new DataObject();
data.SetData("CSV", s);
data.SetData("Csv", s);
Clipboard.SetDataObject(data, true);

This seems to work with both versions of Excel.
irin
I've had the same problem with installations of Excel with the version "Microsoft Excel 2002 (10.6789.6746) SP3". Some installations need "CSV". Some need "Csv". I haven't figured out the difference between them yet, but the solution above makes my code work for all installations.
Rogerio Lima
Thanks ! Great help!
SamTechie
OK. I figured it out. I had to modify the code slightly:

Byte[] blob = System.Text.Encoding.UTF8.GetBytes( str )

Became:

Byte[] blob = System.Text.Encoding.Default.GetBytes( str )

and all was well in the world again.
Aghaster
Have you tried pasting into notepad to see what is actually on the clipboard
Mikey0727
I don't think you can paste data from the clipboard that is on there as Csv.

This has me puzzled as I even tried copying some cells in Excel, extracting the Csv data in my application and putting that exact same data back onto the clipboard. I got the same error message. It is very odd in deed - I have two suspicions:

1. I am putting text, html and Csv on the Clipboard. The two that work go on as strings, and the one I am having problems with is a MemoryStream ... could there be a problem there Maybe a security/access problem with the memory

2. I have .NET 1.1 and 2.0. I am developing in .NET 1.1, but maybe 2.0 is causing some bad behaviour ... security on the memory maybe

Any ideas
Unable to paste CSV format into Excel

Answer this question