How to retrieve Unicode CSV Clipboard data MS Windows XP?

Question

I am writing a test app for a larger project and cant seem to retrieve Unicode CSV data from the Windows Clipboard, I am successful at retrieving CF_UNICODETEXT, using the built in GetClipboardData api call, however when I place Unicode CSV on the clipboard in MSExcel and try to retrieve with CSV format, I get bad data. Here is some code;

procedure TForm1.Button7Click(Sender: TObject);
var
   hMem     : THandle;
   dwLen    : DWord;
   ps1, ps2 : pChar;
begin
   OpenClipboard( form1.Handle );
   RichEdit1.Lines.Clear;
   try
      if Clipboard.HasFormat( CF_UNICODETEXT ) then
      begin
         hMem := GetClipboardData( CF_UNICODETEXT );
         ps1 := GlobalLock( hMem );
         dwLen := GlobalSize( hMem );
         ps2 := StrAlloc( 1 + dwLen );
         StrLCopy( ps2, ps1, dwLen );
         GlobalUnlock( hMem );
         RichEdit1.Lines.Add( ps2 );
      end
      else
         ShowMessage( 'No CF_UNICODETEXT on Clipboard!' );
   finally
      CloseClipboard;
   end;
end;

Now this code should work for CSV as well, but when I change my Clipboard format to what I'm desiring, the app will not get proper data. It might be important to know that I can get tabbed Unicode just fine, just not he CSV I desire.

Answer 1

The CSV clipboard format Excel uses is ANSI encoded, not Unicode.

From dumping the Excel 2007 clipboard, the ones that are Unicode enabled are:

• CF_UNICODETEXT
• "HTML Format"
• "Rich Text Format"
• "XML Spreadsheet"

"XML Spreadsheet" and "HTML Format" both have well defined tables/rows, so they shouldn't be too hard to pull data from.

Answer 2

You need to request the CF_CSV format. AFTER you get the data as CF_CSV, then you can treat it as an AnsiString, and then convert to a UnicodeString, if you desire.

Here's a screenshot showing 6 cells copied from Excel2007. I captured into ClipMate as CF_CSV, then displayed with ClipMate's hex viewer. You'll see that the fields are separated by commas (hex 2C), terminated by CRLF (x0Dx0A). What you see below is an annotated composite, showing Excel, the region copied, and ClipMate's rendering of the CF_CSV as hex bytes.

Also, interesting reading in this related thread: http://stackoverflow.com/questions/967878/get-csv-data-from-clipboard-pasted-from-excel-that-contains-accented-characters