regex to split line (csv file)

Question

I am not good in regex. Can some one help me out to write regex for me?

I may have values like this while reading csv file.

"Artist,Name",Album,12-SCS
"val""u,e1",value2,value3

Output:

Artist,Name  
Album
12-SCS
Val"u,e1 
Value2 
Value3

Update: I like idea using Oledb provider. We do have file upload control on the web page, that I read the content of the file using stream reader without actual saving file on the file system. Is there any way I can user Oledb provider because we need to specify the file name in connection string and in my case i don't have file saved on file system.

Answer 1

Regex might get overly complex here. Split the line on commas, and then iterate over the resultant bits and concatenate them where "the number of double quotes in the concatenated string" is not even.

"hello,this",is,"a ""test"""

...split...

"hello | this" | is | "a ""test"""

...iterate and merge 'til you've an even number of double quotes...

"hello,this" - even number of quotes (note comma removed by split inserted between bits)

is - even number of quotes

"a ""test""" - even number of quotes

...then strip of leading and trailing quote if present and replace "" with ".

Answer 2

Regex is not the suitable tool for this. Use a CSV parser. Either the builtin one or a 3rd party one.

Answer 3

Give the TextFieldParser class a look. It's in the Microsoft.VisualBasic assembly and does delimited and fixed width parsing.