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":"72.5.180.50","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.
chadmanvbAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 ~ .
0
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.
0
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
0
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chadmanvbAuthor Commented:
If so, instead of replacing "," with the "~",  would replacing just ," with ~" work?

Good catch, that worked great!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.