Geek Answers Handbook

Separate using a delimiter, unless the delimiter is shielded

I am reading clipboard data from Excel using

var stream = (System.IO.Stream) ( Forms.Clipboard.GetDataObject() ).GetData( Forms.DataFormats.CommaSeparatedValue );,

but unfortunately excel passes the cell text instead of the cell values. When cells use special formatting (for example, the thousandth seperator), clipboard data for a number of cells in columns that looks like this:

 1,234,123.00    2,345.00    342.00      12,345.00

stored as follows:

\" 1,234,123.00 \",\" 2,345.00 \", 342.00 ,\" 12,345.00 \"

when i really want this:

 1234123.00, 2345.00, 342.00, 12345.00

Earlier, I used the function clipData.Split(new string[] { "," }, StringSllitOptions.None))to turn the data from the CSV clipboard into a series of cells, but this fails if there is formatted text with commas.

, - , , , \", Excel , .

, , :

\" 1,234,123.00 \",\" 2,345.00 \", 342.00 ,\" 12,345.00 \"

, :

{ "1,234,123.00", "2,345.00", "342.00", "12,345.00" }

-.

***** ***

( DFA) : , , ?

+5
5

, Excel , , , , , , ( ). , . , , "". , ,

public static IEnumerable<string> SplitExcelRow(this string value)
{
    value = value.Replace("\"\"", "&quot;");
    bool quoted = false;
    int currStartIndex = 0;
    for (int i = 0; i < value.Length; i++)
    {
        char currChar = value[i];
        if (currChar == '"')
        {
            quoted = !quoted;       
        }
        else if (currChar == ',')
        {
            if (!quoted)
            {
                yield return value.Substring(currStartIndex, i - currStartIndex)
                    .Trim()
                    .Replace("\"","")
                    .Replace("&quot;","\"");
                currStartIndex = i + 1;
            }
        }
    }
    yield return value.Substring(currStartIndex, value.Length - currStartIndex)
        .Trim()
        .Replace("\"", "")
        .Replace("&quot;", "\"");
}

, , , , - "fo,o"b,ar","bar""foo", . , &quot;, " .

+3

. :

  • \ ", \" - (, ) ,
  • \"
  • (, )

,

+1

, , .

string[] vals = Regex.Split(value, @"\s*\"",\s*");
+1

, "" :

\"
\",
,\"

, Split:

string[] result = clipData.Split(new[] { @",\""", @"\"",", @"\""" }, 
    StringSplitOptions.None);

, . , StringSplitOptions.RemoveEmptyEntries StringSplitOptions.None:

string[] result = clipData.Split(new[] { @",\""", @"\"",", @"\""" }, 
    StringSplitOptions.RemoveEmptyEntries);
0

LINQ:

string excelData = "\\\" 1,234,123.00 \\\",\\\" 2,345.00 \\\", 342.00 ,\\\" 12,345.00 \\\"";

IEnumerable<string> cells = from x in excelData.Split(new string[] { "\\\"" }, StringSplitOptions.RemoveEmptyEntries)
                            let y = x.Trim(',').Trim()
                            where !string.IsNullOrWhiteSpace(y)
                            select y;

, , RegEx.

0

More articles:


All Articles