Split string using delimiter but with exception

Question

I have a text file that looks like the following.

AIR NEW ZEALAND LIMITED,AIZ,Transportation

"AIRXPANDERS, INC.",AXP,Health Care Equipment & Services

ALARA RESOURCES LIMITED,AUQ,Materials

ALCHEMIA LIMITED,ACL,"Pharmaceuticals, Biotechnology & Life Sciences"

ALCHEMY RESOURCES LIMITED,ALY,Materials

It has three columns that are always separated by commas. But there are some exceptions that content itself contains comma. The document has put these into a pair of quotation marks.

How do conveniently read out the columns using String.Split method and handle those exceptions?

Cheers Ji

@JohnWu, Thanks for pointing out. Although I have no idea how the Regex formula works, but it works! — uqji, Oct 19 '17 at 06:41
Try this link: [Split is not always the best way to split a string](http://www.regexguru.com/2009/04/split-is-not-always-the-best-way-to-split-a-string/) — John Wu, Oct 19 '17 at 16:54

score 2 · Answer 1 · answered Oct 19 '17 at 06:17

Instead of splitting the string, extract matches using a regular expression and the Matches function.

For example:

var input = @"Column1,Column2,""Column,4"",Column4";
var expression = @"""[^""]*""|[^,]+";
var results = Regex.Matches(input, expression);
foreach (var s in results)
{
    Console.WriteLine(s);
}

Output:

Column1
Column2
"Column,4"
Column4

score 0 · Answer 2 · answered Oct 19 '17 at 04:03

This looks like CSV format. Import-Csv should do the trick:

$data = Import-Csv -Path your-data.txt -Delimiter ","

Import-Csv assumes that the first line contains column headers. If it isn't, you can define header names using -Header switch (i.e. -Header:"col1","col2","col3").

Split string using delimiter but with exception

2 Answers2