split line by comma that might contain comma's in quotes

I have script that parse's a line by commas.  It was working great until one of the fields had a comma in it within quotes.  How could I parse this to get the values I want with visual basic.net?

Here's an example of the line:
{"longitude":"-40,8067","latitude":28.7831,"asn":"AS40384","offset":"-5","ip":"","area_code":"0","continent_code":"NA","dma_code":"0","city":"Somewhere, Close","timezone":"America\/Chicago","region":"Texas","country_code":"US","isp":"Charter, INC.","postal_code":"75270","country":"United States","country_code3":"USA","region_code":"TX"}

So in this case the city "Somewhere, Close" has a comma and breaks my csv split.  How can I handle this or avoid commas in a quote?  I looked at using parser.HasFieldsEnclosedInQuotes = true, but could not get it to work.
Who is Participating?
Shaun KlineLead Software EngineerCommented:
Will all field names (latitude, longitude, etc.) be enclosed in quotes? If so, instead of replacing "," with the "~",  would replacing just ," with ~" work?

Another possibility: Do you control the source where the text is coming from? (Guessing not, but worth asking.) If so, the suggestion is to either change the delimiter, or force all field names and values to be enclosed with quotes.

Beyond that, you may be stuck with going through the string and manually pulling out the pieces you need by searching for the specific field names.
Shaun KlineLead Software EngineerCommented:
If all of the values are enclosed in quotes, your delimiter will have a quote on either side. You could use the String.Replace function to change "," to something like "~" and then split on the ~ .
chadmanvbAuthor Commented:
well 2 fields are not in quotes.  Also if I do the replace I would be splitting in the wrong spot for the field that contains the comma.
chadmanvbAuthor Commented:
Here is what my old code looked like and worked great until I got a result back that contains a comma within the field.

     Dim strIPInfo As String = Nothing
            strIPInfo = getExternalIP(automationPageIP)

            'split isp string by ,
            Dim strSplitLine() As String = strIPInfo.Split(",")
            Dim intLineNumber As Integer = 1    'line counter

            'read each line and if it contains our field, split line by :
            'then remove the " from each line.  Then remove the , from any line that might contain one
            For Each line In strSplitLine
                If line.ToLower.Contains("""ip""") Then
                    Dim strLine() = line.Split(":")
                    strIP = strLine(1).Replace("""", "").Trim
                    strIP = strLine(1).Replace(",", "-")
                End If

                If line.ToLower.Contains("""isp""") Then
                    Dim strLine() = line.Split(":")
                    strISP = strLine(1).Replace("""", "").Trim
                    strISP = strLine(1).Replace(",", "-")
                End If
chadmanvbAuthor Commented:
If so, instead of replacing "," with the "~",  would replacing just ," with ~" work?

Good catch, that worked great!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.