时间戳

首页 > 解决方案 > Fixing badly formatted string with number and thousands seperator

c# - Fixing badly formatted string with number and thousands seperator

问题描述

I am receiving a string with numbers, nulls, and delimiters that are the same as characters in the numbers. Also there are quotes around numbers that contain a comma(s). With C#, I want to parse out the string, such that I have a nice, pipe delimited series of numbers, no commas, 2 decimal places.

I tried the standard replace, removing certain string patterns to clean it up but I can't hit every case. I've removed the quotes first, but then I get extra numbers as the thousands separator turns into a delimiter. I attempted to use Regex.Replace with wildcards but can't get anything out of it due to the multiple numbers with quotes and commas inside the quotes.

edit for Silvermind: temp = Regex.Replace(temp, "(?:\",.*\")","($1 = .\n)");

I don't have control over the file I receive. I can get most of the data cleaned up. It's when the string looks like the following, that there is a problem:

703.36,751.36,"1,788.36",887.37,891.37,"1,850.37",843.37,"1,549,797.36",818.36,749.36,705.36,0.00,"18,979.70",934.37

Should I look for the quote character, find the next quote character, remove commas from everything between those 2 chars, and move on? This is where I'm headed but there has to be something more elegant out there (yes - I don't program in C# that often - I'm a DBA).

I would like to see the thousands separator removed, and no quotes.

标签： c#regexreplace

解决方案

This regex pattern will match all of the individual numbers in your string:

(".*?")|(\d+(.\d+)?)

  • (".*?") matches things like "123.45"
  • (\d+(.\d+)?) matches things like 123.45 or 123

From there, you can do a simple search and replace on each match to get a "clean" number.

Full code:

  var s = "703.36,751.36,\"1,788.36\",887.37,891.37,\"1,850.37\",843.37,\"1,549,797.36\",818.36,749.36,705.36,0.00,\"18,979.70\",934.37";

  Regex r = new Regex("(\".*?\")|(\\d+(.\\d+)?)");

  List<double> results = new List<double>();
  foreach (Match m in r.Matches(s))
  {
    string cleanNumber = m.Value.Replace("\"", "");
    results.Add(double.Parse(cleanNumber));
  }

  Console.WriteLine(string.Join(", ", results));

Output:

703.36, 751.36, 1788.36, 887.37, 891.37, 1850.37, 843.37, 1549797.36, 818.36, 749.36, 705.36, 0, 18979.7, 934.37

推荐阅读