How to paste CSV data to Windows Clipboard with C#

Question

What I'm trying to accomplish

• My app generates some tabular data
• I want the user to be able to launch Excel and click "paste" to place the data as cells in Excel
• Windows accepts a format called "CommaSeparatedValue" that is used with it's APIs so this seems possible
• Putting raw text on the clipboard works, but trying to use this format does not
• NOTE: I can correctly retrieve CSV data from the clipboard, my problem is about pasting CSV data to the clipboard.

What I have tried that isn't working

Clipboard.SetText()

System.Windows.Forms.Clipboard.SetText(  
  "1,2,3,4\n5,6,7,8", 
  System.Windows.Forms.TextDataFormat.CommaSeparatedValue
  );

Clipboard.SetData()

System.Windows.Forms.Clipboard.SetData(
  System.Windows.Forms.DataFormats.CommaSeparatedValue,
  "1,2,3,4\n5,6,7,8", 
  );

In both cases something is placed on the clipboard, but when pasted into Excel it shows up as one cell of garbarge text: "–§žý;pC¦yVk²ˆû"

Update 1: Workaround using SetText()

As BFree's answer shows SetText with TextDataFormat serves as a workaround

System.Windows.Forms.Clipboard.SetText(  
  "1\t2\t3\t4\n5\t6\t7\t8", 
  System.Windows.Forms.TextDataFormat.Text
  );

I have tried this and confirm that now pasting into Excel and Word works correctly. In each case it pastes as a table with cells instead of plaintext.

Still curious why CommaSeparatedValue is not working.

Answer 1

The .NET Framework places DataFormats.CommaSeparatedValue on the clipboard as Unicode text. But as mentioned at http://www.syncfusion.com/faq/windowsforms/faq_c98c.aspx#q899q, Excel expects CSV data to be a UTF-8 memory stream (it is difficult to say whether .NET or Excel is at fault for the incompatibility).

The solution I've come up with in my own application is to place two versions of the tabular data on the clipboard simultaneously as tab-delimited text and as a CSV memory stream. This allows the destination application to acquire the data in its preferred format. Notepad and Excel prefer the tab-delimited text, but you can force Excel to grab the CSV data via the Paste Special... command for testing purposes.

Here is some example code (note that WinForms-equivalents from the WPF namespaces are used here):

// Generate both tab-delimited and CSV strings.
string tabbedText = //...
string csvText = //...

// Create the container object that will hold both versions of the data.
var dataObject = new System.Windows.DataObject();

// Add tab-delimited text to the container object as is.
dataObject.SetText(tabbedText);

// Convert the CSV text to a UTF-8 byte stream before adding it to the container object.
var bytes = System.Text.Encoding.UTF8.GetBytes(csvText);
var stream = new System.IO.MemoryStream(bytes);
dataObject.SetData(System.Windows.DataFormats.CommaSeparatedValue, stream);

// Copy the container object to the clipboard.
System.Windows.Clipboard.SetDataObject(dataObject, true);

Answer 2

Use tabs instead of commas. ie:

Clipboard.SetText("1\t2\t3\t4\t3\t2\t3\t4", TextDataFormat.Text);

Just tested this myself, and it worked for me.

Answer 3

I have had success pasting into Excel using \t (see BFree's answer) as column separators and \n as row separators.

Answer 4

I got the most success defeating formatting issues by using a CSV library (KBCsv) to write the data into a CSV file in the temp folder then open it in Excel with Process.Start(). Once it is in Excel the formatting bit is easy(er), copy-paste from there.

string filePath = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".csv";

using (var streamWriter = new StreamWriter(filePath))
using (CsvWriter csvWriter = new CsvWriter(streamWriter))
{
    // optional header
    csvWriter.WriteRecord(new List<string>(){"Heading1", "Heading2", "YouGetTheIdea" });

    csvWriter.ValueSeparator = ',';
    foreach (var thing in YourListOfThings ?? new List<OfThings>())
    {
        if (thing != null)
        {
            List<string> csvLine = new List<string>
                                         {
                                             thing.Property1, thing.Property2, thing.YouGetTheIdea
                                         };

            csvWriter.WriteRecord(csvLine);
        }
    }
}

Process.Start(filePath);

BYO Error handing & logging.