How to ignore a character in split method

My file has the data in this format:

Name,Sal,Address,Location
Mike,"£10,732",xxxxxx,GBR
Bob,"£6,450",xxxxxxx,Fra
Arthur,"£8,320",xxxxx,Spa
James,"£7,423",xxxxxxxxxxxx,IRE

I need to read this data into a string array. In my new file I need to write Name,Sal and Location columns only. Here is my code:

    Dim ioReader As New System.IO.Streamreader("C:\old.csv")
    ioLines="Name,Sal,Location"
    Dim ioWriter As New System.IO.StreamWriter("C:\new.csv")
    While Not ioLine = ""
        ioLine = ioFile.ReadLine
        Dim values As String()
        If ioLine <> "" Then
            values = ioLine.Split(",")
            Dim outPut as string=values(0) & values(1) & values(3)
            ioLines += System.Environment.NewLine & outPut
        EndIf

When I am splitting the above data, the sal column values which already contains "," is splitting into 2 cells. 开发者_运维技巧I want to keep the sal column value as a single cell by ignoring "," in between numbers. Any suggestions please?

Looks like you are parsing CSV, if yes you can use

Microsoft.VisualBasic.FileIO.TextFieldParser

e.g.

Using MyReader As New Microsoft.VisualBasic.FileIO.
    TextFieldParser("c:\logs\bigfile")
MyReader.TextFieldType = 
    Microsoft.VisualBasic.FileIO.FieldType.Delimited
MyReader.Delimiters = New String() {","c}
        MyReader.HasFieldsEnclosedInQuotes = True
Dim currentRow As String()
'Loop through all of the fields in the file. 
'If any lines are corrupt, report an error and continue parsing. 
While Not MyReader.EndOfData
    Try
        currentRow = MyReader.ReadFields()
        ' Include code here to handle the row.
    Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
        MsgBox("Line " & ex.Message & 
        " is invalid.  Skipping")
    End Try
End While
End Using

Quick and dirty solution:

Dim outPut as string=values(0) & values(1) & "," & values(2) & values(5)

The input file is not proper CSV - the fields with , should be escaped (surrounded with "):

Name,Sal,Address,Location
Mike,"£10,732",xxxxxx,GBR
Bob,"£6,450",xxxxxxx,Fra
Arthur,"£8,320",xxxxx,Spa
James,"£7,423",xxxxxxxxxxxx,IRE

And I wouldn't use string.Split but a CSV parser - you could use the TextFieldParser class.

Another quick and dirty...

using System.Text.RegularExpressions;

private void Test(){
    Regex rex = new Regex(@"(?<!£\d*),");
    string[] s = rex.Split(@"Name,Sal,Address,Location Mike,£10,732,xxxxxx,GBR Bob,£6,450,xxxxxxx,Fra Arthur,£8,320,xxxxx,Spa James,£7,423,xxxxxxxxxxxx,IRE");
}

The regex will not split on comma preceeded by a pound-sign and digits. However this will only allow for one comma in the currency and so £10,342,234 will break it...