What you need is a CSV parser in which you can set the field quote character. Unfortunately the TexFieldParser which comes with VB.NET doesn't have that facility. Fortunately, other ones do - here I have used the LumenWorksCsvReader, which is available as a NuGet package *.
Option Strict On
Option Infer On
Imports System.IO
Imports LumenWorks.Framework.IO.Csv
Module Module1
Sub Main()
Dim s = "613,710,200,127,127,'{\""js\"":{\""\"":\""16\"",\""43451\"":\""16\"",\""65815\"":\""16\"",\""43452\"":\""16\"",\""41147\"":\""16\"",\""43449\"":\""16\"",\""43467\"":\""16\"",\""1249\"":\""16\"",\""43462\"":\""16\"",\""43468\"":\""48\"",\""43438\"":\""64\"",\""43439\"":\""80\""}}','rca',95,2048000,3,1,'AABBCCDDEEFFGGHHIIJJKKLL=','xx.xx.xx.xx',NULL"
Using sr As New StringReader(s)
Using csvReader = New CsvReader(sr, delimiter:=","c, quote:="'"c, escape:="\"c, hasHeaders:=False)
Dim nFields = csvReader.FieldCount
While csvReader.ReadNextRecord()
For i = 0 To nFields - 1
Console.WriteLine(csvReader(i))
Next
End While
End Using
End Using
Console.ReadLine()
End Sub
End Module
which outputs
613
710
200
127
127
{"js":{"":"16","43451":"16","65815":"16","43452":"16","41147":"16","43449":"16","43467":"16","1249":"16","43462":"16","43468":"48","43438":"64","43439":"80"}}
rca
95
2048000
3
1
AABBCCDDEEFFGGHHIIJJKKLL=
xx.xx.xx.xx
NULL
Note that the double-quotes are doubled up in the literal string as that is the way to enter a single double-quote in VB.
If you really want the backslashes to remain, remove the escape:="\"c parameter.
If you are reading from a file then use the appropriate StreamReader instead of the StringReader.
Using the above, perhaps you have a Windows Forms program where you wanted to populate a RichTextBox with the data from, say, a text file named "C:\temp\CsvFile.txt" with the content
613,710,200,127,127,'{\""js\"":{\""\"":\""16\"",\""43451\"":\""16\"",\""65815\"":\""16\"",\""43452\"":\""16\"",\""41147\"":\""16\"",\""43449\"":\""16\"",\""43467\"":\""16\"",\""1249\"":\""16\"",\""43462\"":\""16\"",\""43468\"":\""48\"",\""43438\"":\""64\"",\""43439\"":\""80\""}}','rca',95,2048000,3,1,'AABBCCDDEEFFGGHHIIJJKKLL=','xx.xx.xx.xx',NULL
614,710,200,127,127,'{\""js\"":{\""\"":\""16\"",\""43451\"":\""16\"",\""65815\"":\""16\"",\""43452\"":\""16\"",\""41147\"":\""16\"",\""43449\"":\""16\"",\""43467\"":\""16\"",\""1249\"":\""16\"",\""43462\"":\""16\"",\""43468\"":\""48\"",\""43438\"":\""64\"",\""43439\"":\""80\""}}','din',95,2048000,3,1,'AABBCCDDEEFFGGHHIIJJKKLL=','yy.yy.yy.yy',NULL
615,710,200,127,127,'{\""js\"":{\""\"":\""16\"",\""43451\"":\""16\"",\""65815\"":\""16\"",\""43452\"":\""16\"",\""41147\"":\""16\"",\""43449\"":\""16\"",\""43467\"":\""16\"",\""1249\"":\""16\"",\""43462\"":\""16\"",\""43468\"":\""48\"",\""43438\"":\""64\"",\""43439\"":\""80\""}}','jst',95,2048000,3,1,'AABBCCDDEEFFGGHHIIJJKKLL=','zz.zz.zz.zz',NULL
you could use the above to come up with
Imports System.IO
Imports LumenWorks.Framework.IO.Csv
Public Class Form1
Public Class Datum
Property A As Integer
Property B As Integer
Property C As Integer
Property D As Integer
Property E As Integer
Property JsonData As String
Property SocketType As String
Property F As Integer
Property G As Integer
Property H As Integer
Property I As Integer
Property Base64Data As String
Property IpAddy As String
Property J As String
Public Overrides Function ToString() As String
Return $"{A}, {SocketType}, {IpAddy}, {B} ,{C}, {D}, {E}, {F}, {G}, {H}, {I}, {JsonData}, {Base64Data}, {J}"
End Function
End Class
Public Function GetData(filename As String) As List(Of Datum)
Dim data As New List(Of Datum)
Using sr As New StreamReader(filename)
Using csvReader = New CsvReader(sr, hasHeaders:=False, delimiter:=","c, quote:="'"c, escape:="\"c, comment:=Nothing, trimmingOptions:=ValueTrimmingOptions.UnquotedOnly)
Dim nFields = csvReader.FieldCount
If nFields <> 14 Then
Throw New MalformedCsvException("Did not find 14 fields in the file " & filename)
End If
While csvReader.ReadNextRecord()
Dim d As New Datum()
d.A = Integer.Parse(csvReader(0))
d.B = Integer.Parse(csvReader(1))
d.C = Integer.Parse(csvReader(2))
d.D = Integer.Parse(csvReader(3))
d.E = Integer.Parse(csvReader(4))
d.JsonData = csvReader(5)
d.SocketType = csvReader(6)
d.F = Integer.Parse(csvReader(7))
d.G = Integer.Parse(csvReader(8))
d.H = Integer.Parse(csvReader(9))
d.I = Integer.Parse(csvReader(10))
d.Base64Data = csvReader(11)
d.IpAddy = csvReader(12)
d.J = csvReader(13)
data.Add(d)
End While
End Using
End Using
Return data
End Function
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim srcFile = "C:\temp\CsvData.txt"
Dim dat = GetData(srcFile)
For Each d In dat
RichTextBox1.AppendText(d.ToString() & vbCrLf)
Next
End Sub
End Class
It might be necessary to perform more checks on the data when trying to parse it. Note that I made a function for the .ToString() method of the Datum class and put the properties in a different order just to demonstrate its use.
* Tools -> NuGet Package Manager -> Manage NuGet Packages for Solution... Choose the "Browse" tab -> type in LumenWorksCsvReader -> select the one by Sébastien Lorion et al., -> tick your project name in the pane to the right -> click Install.
