1

Working on a program that takes a CSV file and splits on each ",". The issue I have is there are thousand separators in some of the numbers. In the CSV file, the numbers render correctly. When viewed as a text document, they are shown like below:

Dog,Cat,100,100,Fish

In a CSV file, there are four cells, with the values "Dog", "Cat", "100,000", "Fish". When I split on the "," to an array of strings, it contains 5 elements, when what I want is 4. Anyone know a way to work around this?

Thanks

Teknos
  • 411
  • 1
  • 7
  • 20
  • Who generates that CSV file? Do you have control over that? – HABJAN Apr 04 '11 at 16:36
  • It comes from a third party, I just used dummy names for confidentiality. – Teknos Apr 04 '11 at 16:44
  • Be careful with lists of comma separated integers like "34,35,36,37". While an attempt to parse that into an integer will fail, an attempt to parse it into a floating point value will succeed, and you'll end up with something like 3.435363E+07. The float parser in .NET allows thousands separators and doesn't seem to care whether they're correctly positioned or not. – Triynko Mar 21 '12 at 20:35

8 Answers8

6

There are two common mistakes made when reading csv code: using a split() function and using regular expressions. Both approaches are wrong, in that they are prone to corner cases such as yours and slower than they could be.

Instead, use a dedicated parser such as Microsoft.VisualBasic.TextFieldParser, CodeProject's FastCSV or Linq2csv, or my own implemention here on Stack Overflow.

Community
  • 1
  • 1
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • The backtracking that occurs for a Regex when a condition fails guarantees that it will require additional logic checks in the alternative conditions that would not be necessary in a more straightforward implementation, which could fail immediately when an unexpected character is encountered. A very simple Regex *can* be crafted to quickly parse a valid CSV file correctly; however, problems arise because it will also match invalid CSV data without extra checks, particularly because the backtracking to check alternatives behaves more like an "or" when it needs to behave more like an "if/else". – Triynko Mar 22 '12 at 18:39
3

Typically, CSV files would wrap these elements in quotes, causing your line to be displayed as:

Dog,Cat,"100,100",Fish

This would parse correctly (if using a reasonable method, ie: the TextFieldParser class or a 3rd party library), and avoid this issue.

I would consider your file as an error case - and would try to correct the issue on the generation side.

That being said, if that is not possible, you will need to have more information about the data structure in the file to correct this. For example, in this case, you know you should have 4 elements - if you find five, you may need to merge back together the 3rd and 4th, since those two represent the only number within the line.

This is not possible in a general case, however - for example, take the following:

100,100,100

If that is 2 numbers, should it be 100100, 100, or should it be 100, 100100? There is no way to determine this without more information.

Reed Copsey
  • 554,122
  • 78
  • 1,158
  • 1,373
  • +1 See [The Comma Separated Value (CSV) File Format](http://creativyst.com/Doc/Articles/CSV/CSV01.htm#FileFormat): "Fields with embedded commas must be delimited with double-quote characters." – Randy Levy Apr 04 '11 at 16:41
  • Say in a cell I had the number 100,000. When I split on the commas, it stores 100 in one element in the array, and 000 in the next element. While I can merge these, they may be a case where the number is 1,000,000 or 1,000,000,000. – Teknos Apr 04 '11 at 16:46
  • @obzezzed350: Yes - and that's why I said this isn't possible in a general case - you need the quotes to be correct. – Reed Copsey Apr 04 '11 at 16:47
1

you might want to have a look at the free opensource project FileHelpers. If you MUST use your own code, here is a primer on the CSV "standard" format

Muad'Dib
  • 28,542
  • 5
  • 55
  • 68
0

well you could always split on ("\",\"") and then trim the first and last element.

But I would look into regular expressions that match elements with in "".

Sergei Golos
  • 4,342
  • 1
  • 18
  • 21
0

Don't just split on the , split on ", ".
Better still, use a CSV library from google or codeplex etc
Reading a CSV file in .NET?

Community
  • 1
  • 1
Michael Blake
  • 2,068
  • 2
  • 18
  • 31
0

You may be able to use Regex.Replace to get rid of specifically the third comma as per below before parsing?

Replaces up to a specified number of occurrences of a pattern specified in the Regex constructor with a replacement string, starting at a specified character position in the input string. A MatchEvaluator delegate is called at each match to evaluate the replacement.

[C#] public string Replace(string, MatchEvaluator, int, int);
Chris Hodgkinson
  • 1
0

I ran into a similar issue with fields with line feeds in. Im not convinced this is elegant, but... For mine I basically chopped mine into lines, then if the line didnt start with a text delimeter, I appended it to the line above.

You could try something like this : Step through each field, if the field has an end text delimeter, move to the next, if not, grab the next field, appaend it, rince and repeat till you do have an end delimeter (allows for 1,000,000,000 etc) ..

(Im caffeine deprived, and hungry, I did write some code but it was so ugly, I didnt even post it)

BugFinder
  • 17,474
  • 4
  • 36
  • 51
0

Do you know that it will always contain exactly four columns? If so, this quick-and-dirty LINQ code would work:

string[] elements = line.Split(',');

string element1 = elements.ElementAt(0);
string element2 = elements.ElementAt(1);

// Exclude the first two elements and the last element.
var element3parts = elements.Skip(2).Take(elements.Count() - 3);
int element3 = Convert.ToInt32(string.Join("",element3parts));

string element4 = elements.Last();

Not elegant, but it works.

Matt
  • 3,676
  • 3
  • 34
  • 38