Parseing data in CSV files

  • Thread starter Thread starter Brian Henry
  • Start date Start date
B

Brian Henry

How would you parse this type of file into an array?

"Test","Help, data","hello there, this text has commas","commas seperate
data, and in quotes they dont"

where the double quotes are the string markers, which can contain comma's,
but when you are not in a quote block the commas seperate the data... you're
basic Comma seperated value file... thanks

(i know split doesnt work for this... thats why im asking)
 
Brian,

Why does the split on "," (all three characters I write) not work,

When you than eliminiate the first position from the first arrayItem and the
last postition from the last Array item with substring, you have what you
want in my idea?

Cor
 
the reason it wont work is because not every field is in quotes... so data
can look like this

"hello",,,,,,,,"field again","another, field here","bad huh?",,,

and at each comma that isnt in quotes is a new field... kind of makes
splitting alone really hard... i was going to split on the string quote
comma quote "","" like that, but because some fields have no quotes its a
lot harder then a simple split and removeing the starting and ending quotes
 
Brian Henry said:
How would you parse this type of file into an array?

"Test","Help, data","hello there, this text has commas","commas seperate
data, and in quotes they dont"

where the double quotes are the string markers, which can contain comma's,
but when you are not in a quote block the commas seperate the data... you're
basic Comma seperated value file... thanks

(i know split doesnt work for this... thats why im asking)
Click to expand...
I think I solved this some time ago but I cant remember how & dont now what
I did with the source code. I seem to remember changing single to double
quotes but the reason why escapes me now.
It occurs to me now however that splitting on commas then looping through
the bits extracted joining together any which start with a " or even a '
perhaps until you have a trailing " or ' to match. Then you just have the
problem of testing for 'escaped' or doubled quotes such as 123,"text
"",here""",246
 
Brian,

It seems as an original CSV so you can try that with OleDb

Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Dim file As String = "Test2.txt"
Dim path As String = "C:\Test1\"
Dim ds As New DataSet
Try
Dim f As System.IO.File
If f.Exists(path & file) Then
Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""
Dim conn As New OleDb.OleDbConnection(ConStr)
Dim da As New OleDb.OleDbDataAdapter("Select * from " & _
file, conn)
da.Fill(ds, "TextFile")
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
DataGrid1.DataSource = ds.Tables(0)
End Sub

I hope this helps a little bit?

Cor
 
Back
Top