I'm reading clipboard data coming from excel using
var stream = (System.IO.Stream) ( Forms.Clipboard.GetDataObject() ).GetData( Forms.DataFormats.CommaSeparatedValue );
,
but unfortunately, excel is passing cell text instead of cell values. When the cells are using special formatting (such as the thousands seperator), the clipboard data for a series of cells in columns that looks like this:
1,234,123.00 2,345.00 342.00 12,345.00
is stored as this:
" 1,234,123.00 "," 2,345.00 ", 342.00 ," 12,345.00 "
when what I really want is this:
1234123.00, 2345.00, 342.00, 12345.00
I had been previously using the clipData.Split(new string[] { "," }, StringSllitOptions.None))
function to turn my CSV clipboard data into a series of cells, but this fails when there is escaped formatted text containing commas.
I'm asking if anyone can think of a way to split this string into a set of cells, ignoring the commas escaped within the "
bits, since this is how Excel is choosing to escape cells containing commas.
In short, how can I turn a single string containing this:
" 1,234,123.00 "," 2,345.00 ", 342.00 ," 12,345.00 "
into an array of strings containing this:
{ "1,234,123.00", "2,345.00", "342.00", "12,345.00" }
Without ruining my ability to parse a simple comma delimited string.
*****edit***
Follow up question (formulated as a DFA) here: Split a string based on each time a Deterministic Finite Automata reaches a final state?
See Question&Answers more detail:
os